Athlete monitoring tools are all the rage these days..DATA DATA DATA! Data is only as good as you use it though! But for those who are working with athletes and teams on a budget here is a cool way you can do it exactly the way you want to, with the only cost being your time and energy.
For an updated version of this post click here
This post gives you a way of collecting data from your athletes the exact same way you would through an app. Your athletes will have a link to a questionnaire that they can use on their phones or laptops and send the data to an online google docs spreadsheet. From here you can format the raw data to your liking using google docs, or transferring it over to excel. I have yet to find an athlete monitoring system that caters to my needs and wants, which is why I use excel extensively.
Step 1. Getting the right tools.
Make sure you have google docs and excel.
Step 2. Creating your questionnaire.
You will need to create a questionnaire for your athletes to fill out. One of the easiest ways to do this is by using google forms. Once you know how many pieces of information you need (don’t go bananas here – keep it simple!), you can set up your questions as you like. The question type will determine how it all looks to the athlete. For example, a lot of the question types I use are ‘choose from a list’. This gives a drop down menu and keeps things nice and clean for the athlete, especially for those logging on using their phones.
The picture above shows how it looks when setting it up the form questionnaire. This is taken from a wellness questionnaire that looks for information such as fatigue and muscle soreness with a scale of 1-7. You simply select the ‘choose from a list option’, and then fill in the type of wording and scale you wish to use. Below is how it will look to the athlete. This screenshot is of the workload questionnaire but it looks the same in wellness!
The drop down box is much cleaner and user friendly. The two pictures above are samples from a workload questionnaire that looks at session duration and session rpe. As you can see the box ‘activity’ is a mandatory question – this option helps with clumsy athletes who may forget certain info.
You can also add in images above or below your questions, such as rpe scales and hydration charts to help athletes input more accurate info.
Step 3. Sending the questionnaire to athletes.
The easy part is setting up the form with the questions you want. Once you have your questions finished for whichever type of questionnaire you want, simply click the view live form for the version that the athletes will see. 
Once you are in the live form version, copy the link address, as highlighted by the red circle (with my link missing!), then paste the link into an email or text to your athletes. Your athletes can then bookmark this page on their laptops, or simply save the webpage to the main menu on their phone for quick and easy access. (Helpful tip – get your athletes to set daily alarms to complete monitoring!).
Here’s how a sample questionnaire looks on a windows phone.
Step 4. Collecting and organising the data.
GREAT! NOW YOU HAVE YOUR METHOD OF COLLECTION – NOW WHAT TO DO WITH THE DATA?
This is the tricky and time consuming part, depending on the type data you are looking for. The picture below illustrates how the wellness and workload data looks in the form responses page in google docs. To access this page simply click the ‘view responses’ button on the original form page (a separate file will automatically be set up google docs anyway).
The first picture shows exactly how google organizes the data. Very bland and not too easy to follow sometimes, especially when you have a lot of data! Unfortunately, this raw data cannot be formatted to your liking, if you try to change the data format it simply disappears!
Luckily, there are a few ways around this. You can copy the raw data into excel and format it as you like, or you can set up a new tab in the same spreadsheet, this is a quicker way to see the data in a new format as it updates automatically and saves the hassle of transferring it to an excel spreadsheet.
If you open up a new tab in the same spreadsheet, then simply input a formula (Importrange – shown below) that copies all the data automatically from the main tab and uploads it to the new 2nd tab. In this 2nd tab you can format the data to your liking. As you can see I’ve created a tab called ‘Wellness Monitor’.
Step 5. Importing the raw data to a custom tab.
In order to make sure the data syncs automatically with your new tab, you need to insert the same type of formula into cell A1 on your new tab. THIS IS JUST AN EXAMPLE OF THE FORMULA YOU NEED, NOT THE ACTUAL FORMULA! Your formula will be different because the web address underlined below will be unique to your document.
=IMPORTRANGE(“https://docs.google.com/spreadsheets/d/1AxfplZjO“, “Form Responses 1!A1:I2000“) – THIS LINK WILL NOT WORK FOR YOU, YOU MUST CREATE ONE YOURSELF!
This is a sample version that is in my address bar when I am in the form responses tab. The only change you will have to make is the blue part highlighted in the formula. The importrange formula will import every bit of info from A1 to I2000 in the tab that is named “Form Responses 1”, which is the main responses page. (Remember when you get over 2,000 responses you will need to widen the formula past I2000).
When you are in the main responses page, copy and paste the full link that is in the address bar and put it between two quotation marks, as I have underlined in the formula above. Also you must ensure you put an exclamation mark ! after the name of the form responses page.
Now your new tab should copy all the data automatically. To check if this is the case, simply change some data in the form responses page and see if the data changes in your new tab.
Step 6. Formatting the data to suit you.
Once you have the formula in correctly, you can format the columns as you wish. Below is my new monitor, I set up conditional formatting to change the colour of the tab if a certain word is entered. I also moved all the text to the centre of the cell and changed the timestamp column to show only the date of entry, as opposed to date and time.
You can see a ‘Yes’ in the injury column changes the tab red and I have a colour scale for each wellness marker that changes depending on which word pops up. This make things easier for coaches to spot any potential red flags.
To do this click the format button in the toolbar and select conditional formatting. Depending on what you want you can format a cell to change colour if a certain text appears in the column, or you can have a colour scale that changes depending on a number. As you can see the hydration status scale increases in darkness based on an increasing number.
If you wish – you can set up the questionnaire so the athlete only has to select a number, and put instructions above the question such as what each number represents. This makes it easier to use the data in excel for things like pivot tables, but you take the risk of athletes not reading he instructions and just picking a number without fully knowing what that number represents. The same can be said for the RPE columns, as I’ll go through next.
Step 7. Using data to automatically quantify workload. (RPE x Duration)
The wellness monitor was one of the easier ones to develop, the workload monitor is slightly trickier if you go down the route I did. If you scroll up to the google form where I have the rpe scale you can see that I chose to use numbers and words for the rpe scale as opposed to just looking for a number out of ten.
This was mainly due to my experience with a lot of athletes who would give a number out of ten, then they would look at the actual words beside the number on the rpe scale and change their mind! So having the words within the question makes it easier for more accurate information to be collected. Alternatively, you could just place a picture of an rpe scale above your question, and simply ask for a number, which would save you all the work that I’ll now go through, however, some athletes may still ignore or miss the scale even if it is right in front of them!
Using a question with words and numbers together causes some problems with the form responses, as it literally copies the answer as it is, i.e. numbers and words, as you can see in the RPE column. If you are looking to quantify training load by using session duration X rpe then this is where problems occur as you cannot multiply a number by a word!
Like the wellness monitor I previously mentioned, I’ve used another tab to automatically copy the data from the responses tab to a newer tab, where I can format the data to my liking. I’ve also added in some extra columns to automatically add up total daily workloads.
Some players will have double sessions in a day, so I have a Load 1 and Load 2 column and then a Total Load column. Load 1 and Load 2 columns are the sum of session duration x rpe.
Step 8. Formula for multiplying worded RPE with a numbered duration.
However, as mentioned before, multiplying words and numbers defies the laws of mathematics! That colourful formula in the picture above helps get around this (take a breath, this is a beast of a formula!) So in the Load 1 column I put the following formula into cell O2:
=IF(G2 = “0 – Rest”, E2*0, If(G2 = “1 – Really Easy”, E2*1, If(G2 = “2 – Easy”, E2*2, If(G2 = “3 – Moderate”, E2*3, If(G2 = “4 – Sort of Hard”, E2*4, If(G2 = “5 – Hard”, E2*5, If(G2 = “6 – Hard”, E2*6, If(G2 = “7 – Really Hard”, E2*7, If(G2 = “8 – Really Hard”, E2*8, If(G2 = “8 – Really Hard”, E2*8, If(G2 = “8 – Really Hard”, E2*8, If(G2 = “9 – Really Really Hard”, E2*9, If(G2 = “10 – Maximal”, E2*10))) ))))))))))
This simply states that if each column contains a certain text the you multiply E2 (which is session duration in minutes) by a certain number, depending on the text in the column.
So if G2 (the rpe column), says “0 – rest” then E2, the session duration column, is multiplied by 0. This continues all the way up to 10. This is how you get a session load using duration x rpe.
In my case I have two columns for double sessions so I used the same formula for the second load, but just changed the column letters:
=IF(M2 = “0 – Rest”, K2*0, If(M2 = “1 – Really Easy”, K2*1, If(M2 = “2 – Easy”, K2*2, If(M2 = “3 – Moderate”, K2*3, If(M2 = “4 – Sort of Hard”, K2*4, If(M2 = “5 – Hard”, K2*5, If(M2 = “6 – Hard”, K2*6, If(M2 = “7 – Really Hard”, K2*7, If(M2 = “8 – Really Hard”, K2*8, If(M2 = “8 – Really Hard”, K2*8, If(M2 = “8 – Really Hard”, K2*8, If(M2 = “9 – Really Really Hard”, K2*9, If(M2 = “10 – Maximal”, K2*10))) ))))))))))
Once you have this formula you can copy and paste it down as many cells as you want, so when the data automatically updates on a daily basis, then your new formula will too!
This gives me a nice number now which helps quantify daily training loads for each player. You can put this formula into excel if you wish so that when you copy and paste the raw data into excel it will add it up in that spreadsheet, as I have done below.
Step 9. Making automatic graphs from your data.
Simply open excel and copy the data from the modified workload monitor tab and paste it exactly as it into an excel worksheet. Then set up a pivot table on another tab and select the data you wish to use in your table. (You can easily google how to set up a pivot table, there are lots of good lessons out there). You then have unlimited ways to set up the data as you see fit using the raw data you have collected from google docs.
So there it is, go have a break and try digest all that it’s fairly thorough, but hopefully this post has made the work a little easier and you have a free tool to help monitor your athletes a little better. Microsoft excel have some great tutorial sites on using excel pivot tables but have a play around with them yourself and experiment a little!
REMEMBER this information isn’t gospel, it isn’t something to be used to pull athletes from training and matches because a red flag shows up. It is simply a conversation starter for you and your athletes. “How are you today, I seen you’re a bit stressed lately, how’s everything going?”. This is a much better way of getting quality information from your athletes. Also every athlete will not be where you want them to be all the time. For some athletes feeling worse than normal seven days a week is, in fact, normal! Some may be a 10/10 the morning after a tough pre-season session, over time, when you have collected a good lot of data, you will have a good idea of group and individual norms.
Dr. Adam Sullivan 