How to chart a tennis match with an Excel Spreadsheet

The return of live sports has been a welcome development during the COVID lockdown. We’ve enjoyed watching the US Open and seeing the new batch of young players that are going deep into the tournament.

Today’s post is for those parents who are dreaming of developing their kids into the next Roger Federer, Rafael Nadal, or more realistically (insert name of solid high school player who is able to get recruited into a highly ranked college).

The inspiration for this post comes from http://thetennisparentsbible.com/, a very in-depth (and intimidating) book on junior tennis development. One of the sections of the book is on match charting, or effectively keeping stats for your child’s match. Some of the benefits that author Frank Giampaolo cite are systematically evaluating performance, identifying strengths and weaknesses, and looking busy so you don’t have to talk to somewhat neurotic tennis parents (okay, I added the last one).

Spreadsheet Inputs

This spreadsheet is a bit tricky because of the amount of complexity that one could try for. In theory, every stroke could be a data point, including where on the court it was hit from, what spin it was hit with, and where on the court it was hit to. I’ll keep it relatively simple for now…

Basically we’ll keep track of who won the point, how many shots were in the rally, and how the point was won (winner or error, forehand or backhand, volley or groundstroke or serve). This system was inspired by someone named “Knotwilg” commenting on the TennisTroll Channel.

Here’s how our spreadsheet looks so far:

Here’s how I’d fill it out. If Player 1 hits an ace on the first point, I’d put a “W” into cell B6. Then if in the next point Player 2 dumps a forehand volley into the net, I’d put an “E” into cell J7. There will be lots of borderline situations (half volleys? forced error or winner? is a tweener a forehand or a backhand?), but out of simplicity we’ll keep it to just these five categories and winner or error, and let the user decide how to categorize the borderline cases.

Spreadsheet Outputs

Now that we have some raw data of how all the points in a match were won or lost, we can create some summary statistics to give us some insights into how and why the match was won or lost.

I’ll insert some rows above the match chart to calculate some of these summary statistics. I’m sticking to the basics here – first we’ll count Player 1’s winners:

The other formulas follow similar logic and break it out by strokes.  We then add percentages to the winners versus errors.

Finally, we add some data on the rally length, including the overall average and the average rally length for when each player wins to give a sense of whether a player does better drawing out rallies or ending them faster. This includes adding an intermediate step of figuring out which player won the point:

And that’s pretty much it for this simple version of a tennis match tracker. In theory, you could customize for statistics you think are most important (like where on the court each player was to calculate things like net points won, etc). Also, one could build something that tracks matches over time. All great exercises for building your spreadsheet skills!

Download the spreadsheet here: Tennis Match Tracker

What makes a good business? Part 3 of the IRR trilogy

This post was inspired by reading a Quora response where the child of a top 1% family says that one of the advantages of his upbringing is that his parents taught him about how business works. “Business” is a somewhat broad term that some schools say takes two years and $120k in tuition to “master”. However, as we typically do in this blog, we can educate ourselves on some of the central concepts of business on our own through spreadsheets!

image

Our hope is that anyone with internet access can find this page and play around with spreadsheets to begin to think about what makes a good business. Now if only this site could also provide the exotic travel experiences, connections, and the ability to take risks with a safety net that growing up in the 1% also provides… Continue reading “What makes a good business? Part 3 of the IRR trilogy”

Fantasy Football Player Combos – an exercise in spreadsheet treasure hunting

In many parts of life, having a combination of two things can lead to better outcomes. Having stocks and bonds means sometimes the stocks do well while at other times the bonds do well, resulting in better overall portfolio performance. Also, the driver of this truck pictured below probably has at least one of his teams doing well.

frontrunner-exc
Source: http://www.sportsgrid.com/mlb/frontrunner-truck/

Continue reading “Fantasy Football Player Combos – an exercise in spreadsheet treasure hunting”