With the full time implementation of the new In-Play Dividends mechanic on Football INDEX, we thought it was time to upgrade our very popular free spreadsheet in order to help users keep track of their dividends.
Just in case you're new to the INDEX, 'In-Play Dividends' refers to Goals, Assists and Clean Sheet bonuses awarded to players for the first 30 days in which you purchase futures in them. Naturally, this can be quite tricky to keep track of and so we've aimed to help make this process more manageable.
For those who currently use a previous version of our spreadsheet, we'll have a method of upgrading your existing file with the new features without the need to manually enter too much data from scratch. But before we get to that, here's a rundown of the new features and how to use them.
Most of the main action is located on the new IPD Tracker sheet, where there are two main features: 30 Day Eligibility Tracker and Current Players Activity Tracker.
You'll also notice a handy reference guide to show the dividend payout ratios for each type of player.
How to use 30 Day Eligibility Tracker:
- Enter Player Name
- Enter Player Type - Use GK, DEF, MID and FWD to retain colour code formatting.
- Enter Purchase Price - this serves as a quick reference guide.
- Enter Number of Futures Purchased.
- Enter Date of Purchase.
The number of days left eligible for In Play dividends is automatically calculated for you and is colour coded in a green, orange and red format to quickly show which players will soon become ineligible. When a player hits zero or beyond, the entire row will turn red to further indicate those futures are no longer active.
When making further purchases, enter these details on a new row as this will ensure you only sell the futures that have become ineligible and keep those that are still within the 30 day period.
You can sort your players by A-Z by clicking the drop down arrow in the lower right cell corner. This will help to quickly calculate the total number of futures you currently have eligible in each player.
When futures become inactive and you wish to remove them, simply delete the data entered in columns B to F. Be careful not remove the formula in column G when doing this.
We think this is perhaps the most useful aspect of our new improved spreadsheet as keeping track of which players you have eligible in an ever moving portfolio can soon get out of hand.
How to use Current Players Activity Tracker:
- Enter Player name.
- Enter Number of Eligible Futures
- Enter Date of Event
- Enter GACS Type - Goal, Assist or Clean Sheet
- Enter Number of Goals, Assists or Clean Sheet.
- Enter Dividend Amount.
Total In-Play dividend amount is automatically calculated. Payment Due by is automatically calculated and will return the next Wednesday, which is the latest specified payment date by Football INDEX.
When dividends have been paid, you can type 'Paid' into the final column. This will turn the row green in order to easily view which dividends have been paid.
Users can choose to use this table in a variety of ways to suit. For example, you may wish to keep a complete history of all dividends. Alternatively, once a full set of dividends have been paid, you can delete individual rows. Or, you may decide to keep all records for a period of a month. It's very open to how you wish to retain control of your data.
Again, if/when you wish to delete data, simple delete the information from rows K to P along with S and be careful not to delete the formulas in rows Q & R.
Automatic rolling weekly, monthly and all current record totals are shown below the main table. The weekly totals will always display the current window for the current week, starting on each Tuesday.
We think this will really help what is quite a tricky task of keeping track of the dividends your players have earned for you.
Finally, let's move over to the Performance Buzz Returns sheet where there is now an extra column for In Play dividends to be entered. This can be done daily, weekly or if you prefer, simply enter the final monthly total in the indicated cell. These are added to your overall Performance Match Day dividends totals.
It should perhaps be pointed out here that there is no connection between the IPD Tracker sheet and any other sheets. This is because it allows the user to decided how much they want to utilise the new features. For example, some users may only want to use the 30 Days Eligibility Tracker and only enter the dividend amounts received as they drop into your account.
There's one final thing to cover, and that's how best to transfer your existing data in to the new version. In actual fact, it's best to transfer over the new sheets into your existing file as this will remove the need to copy most of the data you've already entered!
WARNING: WE HIGHLY RECOMMEND THAT YOU CREATE A COPY OF YOUR EXISTING FILE BEFORE STARTING TO TRANSFER ANY DATA JUST IN CASE SOMETHING GOES WRONG!
To Move the IPD Tracker Sheet to You Existing Spreadsheet:
- The first step is to transfer over the GACS Tracker Sheet from the new template file into your existing file.
- Open both your existing spreadsheet along with the new template file.
- On the new file, right click the GACS Tracker sheet and select 'Move or Copy'.
- In the box that pops up, select your existing spreadsheet from the drop down menu. Then highlight '(move to end)' and click Ok.
- This will move the GACS Tracker sheet directly into your existing spreadsheet, ready to use.
To Copy Existing Performance Buzz Data:
Copying your existing Performance Buzz data is a little more time consuming, but is fairly straight forward if you take your time and follow the instructions below.
- Open both your existing spreadsheet along with the new template file.
- On your existing spreadsheet right click the 'Performance Buzz Returns' sheet and select 'Move or Copy'.
- In the top drop down menu, choose '(new book)' and click ok. This will move your data to a new temporary file.
- On the new template file, right click the new 'Performance Buzz Returns' sheet and select 'Move or Copy'.
- In the box that pops up, select your existing spreadsheet from the drop down menu. Then highlight move before 'GACS Tracker' and click Ok.
- This will move the new Performance Buzz Returns sheet to your existing file. You can now close down the new template file.
- Next we copy over your data by going month by month. Highlight the data for each month in columns B to P on the temporary file containing your existing data (we recommend highlighting the entire month so as to ensure you don't miss anything or enter it under the wrong day). Hit Ctrl+C to copy, then move to the corresponding month on the new sheet and paste the data using Ctrl+V.
- Once completed, we have one final step to restore working formulas. In the top menu bar, select the Data tab. In the 'Connections' section, click on 'Edit Links'.
- Click 'Change Source' then navigate to and select your original file then click Ok.
- And you're done! If you wish to remove the green arrows from your portfolio table, highlight the top cell, hover the cursor over the lower right corner until it changes to a + sign and double click. Do this for each row.
After completing these steps you should now have a fully updated version of our new spreadsheet ready to use. All previous features are still intact and we think that the new additions make this an essential assistant to keeping track of your Football INDEX journey!
You can find the original user guide to use all the pre-existing features in our V2 User Guide article by clicking the button below or Download the new spreadsheet