Excel Tricks for Sports

EXCEL TRICKS FOR SPORTS

Ongoing Project
Performance & HRV - Training and Recovery
Quest for personal optimisation

 

 


Info INTRO

My journey into the world of fitness continues. It is growing and developing in ways that I could not have imagined. As it progresses I am starting to understand that fitness is not fitness, but that is an discussion for another time!

I have been consistently training for three years now (started October 2012) and my progress has been amazing and not amazing at the same time. On the one hand I have improved many facets of what is deemed fitness and on the other hand I have not really progressed. This has partly been due to my desire to try as many different programs as possible within my chosen realm (i.e. Tacfit) and not actually being specific with personal attribute development. This is not a reflection of the 'systems' that I have chosen to follow but more to do with lack of what I would label as confident knowledge.

This changed a little back in March (2015) but for many reasons did not happen to the level I wanted. Six+ months later and I am seriously ready to move to the next level.

To do this involves two things (for me). The first is to develop a way to chart my 'fitness', and that also identifies areas that need improvement. The second is a way to track my HRV (heart rate variability), which includes methods over and above that of the HRV companies out there (and/or amalgamations of concepts from said companies). Both are highly individual to my personal requirements and I have the determination and stubbornness to see them through!

Now, I talk about individuality but am realistic enough to know that I can only achieve certain elements through imitation and innovation. Learn from the experts and then tailor to suit :) With this strategy in mind I set about researching. The aims were as follows:

1. Identify strengths and weaknesses in personal 'fitness' and track it.

2. Monitor HRV (heart rate variability) and include specific elements that were important to me.

Unbeknown to me at the time was that both elements would meet. This was a pleasant surprise. The meeting point was the need for something that was Excel-based. 

My inspiration for tracking and monitoring came from Joel Jamieson's team excel sheet/radar chart that accompanied his strength in motion dvd series.

Strength in Motion Radar ChartJoel Jamieson's Team Excel Spreadsheet

Initial ideas for HRV monitoring/analysis came from Complementary Training who wrote articles (now available through subscription only) on analysing time-series of individual data.

Complementary Time Series

Both the inspiration and ideas obtained were a huge step in the right direction. Alas I lacked the knowledge to turn them into a personal reality. That was until I discovered Excel Tricks for Sports. Thanks to a link on the Complementary Training blog, I was introduced to this amazing resource. Not only did they provide training on how to accomplish these types of projects but they also provided working examples of both elements that I was looking for. Bingo!


 

etfs

What is Excel Tricks for Sports?
(Taken from YouTube Channel with kind permission)

etfs

This channel is made by a sport scientist, sports analyst and strength and conditioning coach working with Olympic athletes. The videos will demonstrate techniques and systems used when preparing training programs, managing training and monitoring data and preparing reports. All videos have accompanying files that can be downloaded from www.athletefitness.co.nz You can use these demonstrations and files to help guide your practices, give you ideas or just develop some new excel skills.

Some of the files include Athlete Fitness Profiles and Dashboards, Training Program development, Fitness Test Reporting and i use both Excel 2010 for PC and Excel for Mac 2011. Occasionally i dabble in VBA too. Email me on This email address is being protected from spambots. You need JavaScript enabled to view it. or link me in (My name is John Lythe) if you want to make contact.

 

 What I achieved as a result of using Excel Tricks for Sports

etfsI am an individual that likes ownership of something from start to finish. I studied John's work and created my own projects. It was like the old childhood days of when I was disassembling and reassembling my bicycles! I took his Excel files apart, learned the hows and whys and then put them back together. John's videos made it easy to learn what I needed to know to conceptualise, create and own my projects.

Prior to my Excel adventure I knew basic SUM and IF formulas. Now I know and understand:

  • Complex IF
  • IFERROR
  • Match
  • VLOOKUP
  • HLOOKUP
  • INDEX
  • TRUNC
  • OFFSET
  • Dynamic chart creation
  • and more..................

Images of my projects can be found at the end of this article.

My take on Excel Tricks for Sports 

Excel Tricks for Sports is an amazing resource for those wanting to dive into the Excel ocean (both from a fitness and non-fitness perspective). The videos are concise and John has a very personable manner that makes him a joy to listen to and learn from. I cannot recommend John and his work enough. What is great is that you are given resources to develop your own projects or John is only a click away if you would rather someone else develop your project. After watching all of his YouTube videos I am confident that he knows his stuff and then some :)

Thank you John and thank you Excel Tricks for Sports.

 


 Data FURTHER RESOURCES

Official Web Site
http://fittersmarterstronger.com//

YouTube Channel
https://www.youtube.com/user/ExcelTricksforSports

 


 

InfoMY PROJECTS

 The following images are from the HRV Excel document that I built from scratch. It was a fascinating project and one which has given me great satisfaction. To overcome different formulaic challenges to achieve specific results was very satisfying. My greatest sense of achievement was creating speedos that could accomodate negative results.

ALL credit for the creation of the document goes to John and Excel Tricks for Sports. Thank you for pathing the way.

There are some ideas that I have taken from different sources and I acknowledge them as follows:

  • HRV / Activation chart from dashboard - ithlete pro (of which I am a paid subscriber).
  • HRV Training Load - Joel Jamieson and Bioforce HRV.
  • Coefficient of variation - Andrew Flatt and HRV Training.

 

HRV Doc

HRV Doc

 HRV Control Panel. This is where all the other sheets take data from

 


 

 HRV Dashboard

Personal dashboard with summary of key data that I have identified as being useful (baseline, z-score, CofV, training load, HRV and activation)
Will focus on aesthetics later ;)

 


 

HRV Timeline

 HRV over time.

 


The fitness assessment and radar chart is the same as John's version. I created from scratch using his model and adjusted some of the parameters to suit my goals. This sheet exceeded my initial vision and in my opinion is a work of art.

 

 Performance Training

This is just a snapshot of the Excel document. I am so excited to put this into practice and know it will aid in future attribute development and measurements.


 

Note 1: None of the external links on this page or throughout this web site are affiliate links. I provide links to give access to further information about the training I undertake. I have absolutely nothing against affiliate links but I don't personally run any.  

Note 2: Sometimes I will provide details about a training program but only details that have been put into the public domain by the program creators.

 


 

"Fitness is a simple pathway to something much bigger."

Me.
October 2015