I've developed a strokes gained spreadsheet and anyone interested can use it!

Recently, dornstar posted a thread involving the use of strokes gained to determine where one's game may be lacking and where their practice time could be best spent. While I'd heard of the concept, I was not aware of how it could be applied to the game of amateurs, where shotlink is not prevalent. However, upon discussing the concept with an aspiring tour pro friend of mine, and him taking an interest in the topic, I've decided to use my mediocre skills on Microsoft Excel to develop a spreadsheet to store, track, and analyze this data.

If you're interested in the concept of strokes gained and how you might use it in your own game, I highly suggest reading the thread started by dornstar, here: http://www.golfwrx.com/forums/topic/1089289-ive-gone-100-to-strokes-gained-for-stats-from-tee-to-green-heres-how/

I'm starting this thread as a way for users of my spreadsheet to communicate with me any changes they would like to see or any suggestions for updates. I'll be happy to try to accommodate as best I can.

There are several formulae used in this spreadsheet which were first introduced in Excel 2007. I believe the spreadsheet will function in Excel 2007 and I'm positive it will function in Excel 2010 and Excel 2013, as those were the two programs I used to develop it. I've left in my own data for people to play around with the functionality. Please don't make fun of my scores and shots, LOL! To clear the data and being using the spreadsheet for yourself, simply delete all of the data contained within the green cells on the My Bag worksheet and the Input worksheet and you're good to go.

I would encourage each of you interested in using the spreadsheet to read the accompanying instructions also linked here, as they can help you get the most out of the spreadsheet.

Without further ado:

Spreadsheet:

https://www.dropbox.com/s/8r7k5ji8an6dpf1/Golf%20Strokes%20Gained%20Analysis.xlsx?dl=0

Instructions for use:

https://www.dropbox.com/s/88wdrlnivlaz8jq/Instructions%20for%20use%20of%20Golf%20Strokes%20Gained%20Analysis%20spreadsheet.docx?dl=0

The most recent versions of the spreadsheet can be found in post #83 on page 3.

Definitely going to be following this

Sweet
!

Definitely interested. Thank you!

Thanks for this. I will read over it and learn how to use because this seems like a really good idea. It's nice how you have your shots in there so I can just keep looking at your example until I get a hand of it. I will probably need a bit more time filling it out as we use metres over here. Also for putts how do you calculate the distance? Do you just estimate or do you actually pace it out. I'm just a bit scared of steeping in someones line and taking too long. Also for putting I'm guessing it's feet?

Interesting stuff. Took me a little while to figure out how to use it. I can see it to be very useful for a low handicapper where the extra strokes gained during a round are more subtle and harder to pinpoint than those of a high handicapper who can simply just remember which clubs he screwed up with.

It would take a lot of patience and time to write down the distances and location of every shot throughout an 18 hole round in order to fill in the data sheet though. Truly designed for the serious, low handicapper who is willing to put in the effort necessary. Even though im an 8.5, I wouldn't use it because I realize that no part of my game is polished. Every aspect of my game needs improvement.

Well done, Sir!


Fully functional in Excel 2008 on Mac.

[quote name='Steve Ryan' timestamp='1414287552' post='10351201']
Interesting stuff. Took me a little while to figure out how to use it. I can see it to be very useful for a low handicapper where the extra strokes gained during a round are more subtle and harder to pinpoint than those of a high handicapper who can simply just remember which clubs he screwed up with.

It would take a lot of patience and time to write down the distances and location of every shot throughout an 18 hole round in order to fill in the data sheet though. Truly designed for the serious, low handicapper who is willing to put in the effort necessary. Even though im an 8.5, I wouldn't use it because I realize that no part of my game is polished. Every aspect of my game needs improvement.
[/quote]

I was thinking about that too. A higher handicap maybe a little too inconsistent, that when you look over your stats nothing with stand out on what you need to improve.I'll give this a try though, maybe 5 rounds and see what the data says. If it's clear I will keep using it, if not I know I've got a lot of work to do.

[quote name='btewks' timestamp='1414288154' post='10351257']
Well done, Sir!


Fully functional in Excel 2008 on Mac.
[/quote]

Glad to hear it!

[quote name='Steve Ryan' timestamp='1414287552' post='10351201']
Interesting stuff. Took me a little while to figure out how to use it. I can see it to be very useful for a low handicapper where the extra strokes gained during a round are more subtle and harder to pinpoint than those of a high handicapper who can simply just remember which clubs he screwed up with.

It would take a lot of patience and time to write down the distances and location of every shot throughout an 18 hole round in order to fill in the data sheet though. Truly designed for the serious, low handicapper who is willing to put in the effort necessary. Even though im an 8.5, I wouldn't use it because I realize that no part of my game is polished. Every aspect of my game needs improvement.
[/quote]

I agree with you regarding the usefulness of this being more geared toward being used for a lower cap, but I think a higher cap can benefit in the following way. A higher cap can determine where they're giving away the most strokes compared to their current ability by using the first table in the Analysis by Date worksheet. That worksheet will tell what the average strokes per shot lost are for all categories. Column E in that table, Difference from Avg., will show which categories are worse than their average for all strokes and which shot categories are better.

I might work on a version of this spreadsheet which would adjust the stroke amounts on the data worksheet by the average number of strokes per shot from the table discussed above. That way, it wouldn't be compared to the PGA Tour Pro average, which none of us can really expect to compare with, but rather to your own average. If there is overwhelming desire for me to work on that, I'll attempt it.

Also, regarding the keeping of data, dornstar in his thread showed a table he uses to keep track of the distance and shot type during the round by hand. No need to do that math on the course, just record distance from the pin and shot type (really pretty easy if you have a laser). I'm at the in-laws this weekend but I have a table I developed based on dornstar's table on my other computer which I can put into my dropbox as well. It allows me to input club, distance, shot type, and notes in the width of my scorecard holder.

[quote name='360_CS' timestamp='1414284024' post='10350947']
Thanks for this. I will read over it and learn how to use because this seems like a really good idea. It's nice how you have your shots in there so I can just keep looking at your example until I get a hand of it. I will probably need a bit more time filling it out as we use metres over here. Also for putts how do you calculate the distance? Do you just estimate or do you actually pace it out. I'm just a bit scared of steeping in someones line and taking too long. Also for putting I'm guessing it's feet?
[/quote]

No problem. I have thought about creating a version that would be compatible with meters instead of yards and feet. The putting distances are in feet. If you look closely at my data, you'll notice that I have putters from the fairway (fringe). On these shots, the distance is measured in yards, even though I'm using a putter. So a putter from F6 is from 18' off the green.

The putting distances are estimated. As long as the estimation never changes, it should become accurate over time. The other way to help determine the putting distance would be to measure out on the practice green putts of certain distances and put down a ball to get a visual cue of exactly how far away 8 feet, 15 feet, 30 feet, really are.

If I were to work on a metric spreadsheet, how are putts measured in meters? Is 1 meter the shortest distance you would call a putt? 1/3 of a meter? 1/2? I'm just trying to determine what I would use for the distances and how the inputting of data would work.

Don't worry about metres, it's easy enough for me to just convert it myself, adding 10% is easy enough. And like you said measuring putts would be a problem since 1 metre is quite big. Plus yards and feet have their advantages as it is easy to measure distances yourself. I didn't notice you had putts off the green. Good thinking. I'm still looking at the sheet to work out how to analyze everything.

Another question. You know how you have shots in white and your first round you shot 70 odd if I were to shoot higher or lower, i can just start my next round straight after the my first round without stuffing anything up, right? Also I haven't used excel in years but for the date and the course how do I just copy it without writing it out 70-80 times. Sorry for the rookie questions.

LOL. I can't wait to play my next round now.

[quote name='360_CS' timestamp='1414301370' post='10351863']
Don't worry about metres, it's easy enough for me to just convert it myself, adding 10% is easy enough. And like you said measuring putts would be a problem since 1 metre is quite big. Plus yards and feet have their advantages as it is easy to measure distances yourself. I didn't notice you had putts off the green. Good thinking. I'm still looking at the sheet to work out how to analyze everything.

Another question. You know how you have shots in white and your first round you shot 70 odd if I were to shoot higher or lower, i can just start my next round straight after the my first round without stuffing anything up, right? Also I haven't used excel in years but for the date and the course how do I just copy it without writing it out 70-80 times. Sorry for the rookie questions.

LOL. I can't wait to play my next round now.
[/quote]

Good questions.

To answer your first question, the spreadsheet automatically calculates your cumulative number of strokes. The way it does that is to look at the date column and determines how many times that date has appeared prior and adds one each time the date appears again. Therefore, when you enter a new date, the cumulative strokes starts counting until you stop entering data or enter a different date. So if you take more or less strokes than me, no problem. The only disadvantage to this is that it does not work well with multiple rounds on a given date. One solution for that is to input your first round on a date prior to the actual date played and then the second round on the date it was played. It's not ideal, but it would work.

For the date issue, that's pretty easy. You just go to the first available row to input data and input the date you're playing. Input that same date in the row below that. Then select both cells and use the drag and fill on the bottom right of the second cell to fill down the column as far as you want.

My process for entering a round is usually as follows. I'll drag the date down 100+ rows using the method above. That will show me the cumulative strokes. I'll delete the dates back up until I get to the number of cumulative strokes I know I had. Then I go down the Hole # line and enter the number of strokes for each hole. Then I enter the data across the sheet, including club, distance, and type.

I'm happy to help and please let me know if you have any other questions.

Thanks!

I just finished reading Every Shot Counts and look forward to trying out your work. I really appreciate the effort you've put into this tool and your sharing it with us. Will provide feedback after use. Thanks again!

I'd love to have a "Strokes Gained by Club" feature. I know the overall yardage gaps are covered, but looking at individual club performance would be a cool way to see how a particular club is performing.

Now that I think about it, having strokes gained by club and showing the yardage gaps... That would be sweet! For example, pulling up my numbers for 100 yardage shots being able to compare my 54* full shots to 3/4 shots with my 50* would be very valuable. Could provide a very objective platform for on-course decision making.

[quote name='Steve Ryan' timestamp='1414287552' post='10351201']
Interesting stuff. Took me a little while to figure out how to use it. I can see it to be very useful for a low handicapper where the extra strokes gained during a round are more subtle and harder to pinpoint than those of a high handicapper who can simply just remember which clubs he screwed up with.

[color=#0000cd]It would take a lot of patience and time to write down the distances and location of every shot throughout an 18 hole round in order to fill in the data sheet though.[/color] Truly designed for the serious, low handicapper who is willing to put in the effort necessary. Even though im an 8.5, I wouldn't use it because I realize that no part of my game is polished. Every aspect of my game needs improvement.
[/quote]
It's really not a lot of work. I've been doing this for 10 rounds now and after each shot, I write down 2 things.... Club and the distance from the pin I'm at.

On the tee of a 400 yard par 4, that would be 1W and T400. If I ended up in the fairway 120 yards away, the next recording would be GW and F120. Most of the time, I just write down what I had when I record my score for the hole. Takes all of 10 seconds.

[quote name='dornstar' timestamp='1414343073' post='10353211']
[quote name='Steve Ryan' timestamp='1414287552' post='10351201']
Interesting stuff. Took me a little while to figure out how to use it. I can see it to be very useful for a low handicapper where the extra strokes gained during a round are more subtle and harder to pinpoint than those of a high handicapper who can simply just remember which clubs he screwed up with.

[color=#0000cd]It would take a lot of patience and time to write down the distances and location of every shot throughout an 18 hole round in order to fill in the data sheet though.[/color] Truly designed for the serious, low handicapper who is willing to put in the effort necessary. Even though im an 8.5, I wouldn't use it because I realize that no part of my game is polished. Every aspect of my game needs improvement.
[/quote]
It's really not a lot of work. I've been doing this for 10 rounds now and after each shot, I write down 2 things.... Club and the distance from the pin I'm at.

On the tee of a 400 yard par 4, that would be 1W and T400. If I ended up in the fairway 120 yards away, the next recording would be GW and F120. Most of the time, I just write down what I had when I record my score for the hole. Takes all of 10 seconds.
[/quote]

I agree that it doesn't take long. I will write stuff down depending on when my turn to play is or just catch up after the hole. Tee shots I'll write down after the shot. Shots around the green you may have to remember multiple distances/lies but it isn't a big deal. I did some "walking off distances" practice so it is quick to measure off distances on the green and for shorter shots around the green. My normal stride is about 2 feet so I can walk off distances putt distances or do a quick conversion to yards in my head for short game shots. I haven't even been using a normal scorecard the last two rounds - I use a custom sheet I made to track this and focus more on per hole score than the overall score.

I do have a GPS watch that I use. I'll use an estimated distance based on front/middle/back numbers and pin location. It kind of makes me wish I had a laser rangefinder to be more exact but ultimately I don't think I'm getting bad info by estimating distances within 5 yards.

My playing partners have asked what I'm doing, but I haven't been slowing the group down at all.

Entering data after rounds will take a little time but I really look forward to it. It seems like so many golfers have this stigma towards getting better.... I don't understand why. This literally shows you where to put work in.

And I just realized you can filter by club on the "analysis by shot" sheet...

[quote name='btewks' timestamp='1414342548' post='10353177']
I'd love to have a "Strokes Gained by Club" feature. I know the overall yardage gaps are covered, but looking at individual club performance would be a cool way to see how a particular club is performing.

Now that I think about it, having strokes gained by club and showing the yardage gaps... That would be sweet! For example, pulling up my numbers for 100 yardage shots being able to compare my 54* full shots to 3/4 shots with my 50* would be very valuable. Could provide a very objective platform for on-course decision making.
[/quote]

I'll work on something for that, but the formula might be tough. I'll have to give it some thought as to how I would get that done. In the meantime, there is a way you could do that manually, but it would be slightly time consuming. If you go to the Analysis by Shot worksheet and select the distance ranges you want, for all lie options (the time consuming part), it will bring up all of the shots with all of the clubs. Then you could sort by smallest to largest and see which clubs performed better than others from that distance.

[quote name='btewks' timestamp='1414345473' post='10353321']
And I just realized you can filter by club on the "analysis by shot" sheet...
[/quote]

I was just responding that, LOL. I still think analysis by distance by club might be helpful. I'll see what I can come up with.

[quote name='Stiff' timestamp='1414336848' post='10352877']
I just finished reading Every Shot Counts and look forward to trying out your work. I really appreciate the effort you've put into this tool and your sharing it with us. Will provide feedback after use. Thanks again!
[/quote]

No problem. I look forward to hearing your feedback. Please let me know what changes could be made, especially as it relates to "Every Shot Counts" as I've not yet read that, but it's definitely on my list.

[quote name='bwrichmond' timestamp='1414345826' post='10353351']
[quote name='btewks' timestamp='1414342548' post='10353177']
I'd love to have a "Strokes Gained by Club" feature. I know the overall yardage gaps are covered, but looking at individual club performance would be a cool way to see how a particular club is performing.

Now that I think about it, having strokes gained by club and showing the yardage gaps... That would be sweet! For example, pulling up my numbers for 100 yardage shots being able to compare my 54* full shots to 3/4 shots with my 50* would be very valuable. Could provide a very objective platform for on-course decision making.
[/quote]

I'll work on something for that, but the formula might be tough. I'll have to give it some thought as to how I would get that done. In the meantime, there is a way you could do that manually, but it would be slightly time consuming. If you go to the Analysis by Shot worksheet and select the distance ranges you want, for all lie options (the time consuming part), it will bring up all of the shots with all of the clubs. Then you could sort by smallest to largest and see which clubs performed better than others from that distance.
[/quote]

I just made a club comparison sheet. I did it for two drivers first but I could easily sort data for yardages and copy/paste values as needed.

Thanks so much for sharing this, really useful. Is there a way of tracking GIR?

[quote name='JNZ' timestamp='1414365986' post='10354603']Thanks so much for sharing this, really useful. Is there a way of tracking GIR?[/quote]

There isn't a way to track GIR. With strokes gained, GIR isn't really that important.

thanks for sharing!

is there a std dev of strokes gained for each club on this worksheet?

I haven't taken a look at the sheets yet, but being a data junkie (and thus, Excel junkie) myself, I'm excited about the prospects of this. Thanks to both BRich and dornstar for getting these things going!

EDIT: BRich - by the way...I did just replace that Fubuki ZT in my 2Deep with a UST V2 75X. Launch came down like I expected. Only had one round with it so far, but it's marvelous.

What sort of sheets/cards/apps are people using to record the information on the course?

[quote name='hayzooos' timestamp='1414423099' post='10357117']
I haven't taken a look at the sheets yet, but being a data junkie (and thus, Excel junkie) myself, I'm excited about the prospects of this. Thanks to both BRich and dornstar for getting these things going!

EDIT: BRich - by the way...I did just replace that Fubuki ZT in my 2Deep with a UST V2 75X. Launch came down like I expected. Only had one round with it so far, but it's marvelous.
[/quote]

What I wouldn't give to hit that a few times now. Liked it with the Fubuki but too high launch. I would probably love it with that V2 in there! Glad to hear it's working out well for you!

The only round I've played since learning of this strokes gained system without keeping my strokes gained stats was the WRX outing, because the weather was not conducive to doing anything but swing, drive, swing, drive, putt, putt, drive. Let me know what you think if you have the opportunity to test it out yet this year.