MTSW - Bet Tracker (From March 2nd)
 Share
The version of the browser you are using is no longer supported. Please upgrade to a supported browser.Dismiss

 
View only
 
 
ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
Introduction
2
3
4
Version
5
6
This is version v2.20 Basic of the Google Sheets betting tracker. It is based on v2.20 Basic of the Excel betting tracker.
7
8
Terms of Use
9
10
This spreadsheet is free for personal use. Feel free to make amendments to the sheet to suit your needs.
11
Do not make this spreadsheet, or any variant of it, available elsewhere. If you wish to tell others about this betting tracker, please share the following URL:
12
http://www.aussportsbetting.com/tools/google-sheets-betting-tracker/
13
14
Setup - READ THIS BEFORE USING THE SPREADSHEET
15
16
STEP 1: Delete the sample data in the Settings, Deposits and Bets worksheets. IMPORTANT: do not use the Ctrl+X keyboard combination to delete data because this can cause background calculations to go awry. To remove data we recommend you highlight the cells and press the Delete button on your keyboard.
17
STEP 2: Go to the Settings worksheet and input the names of your betting agencies along with the sports you plan to bet on. We recommend you also input bet types. Change the odds format and currency rounding if necessary.
18
STEP 3: Go to the Deposits worksheet and input your bookmaker account balances into the Deposits & Withdrawals table
19
STEP 4: Go to the Bets worksheet and input your betting details
20
STEP 5: Once each bet has resulted, update the Bets worksheet accordingly
21
22
Worksheets
23
24
This workbook enables you to monitor your sports betting performance. It consists of seven worksheets that can be selected using the tabs at the bottom of the screen. The yellow sheets display information and statistics, while the blue sheets are for data entry. Each worksheet is described below.
25
26
INTRO (output) - provides an introduction to the spreadsheet along with usage instructions
27
SETTINGS (input) - manages settings for bookmakers, sports categories, tippers, bet types, odds formats and currency rounding
28
DEPOSITS (input) - lists your bookmaker deposits and withdrawals, including bonus credit and free bets
29
BETS (input) - the main data entry sheet for your wagers
30
AVAILABLE FUNDS (output) - tracks your net deposits and available credit with each bookmaker
31
PERFORMANCE SUMMARY (output) - an interactive sheet that summarises your betting performance
32
PERFORMANCE GRAPH (output) - plots your betting performance over time
33
34
Bets Worksheet - Field Explanations
35
36
Below are descriptions of the fields in the Bets worksheet. Variables with black headers are required while those with blue headers are optional. Sample data is provided in the Bets worksheet so you can see how the fields are intended to be used. Input instructions are automatically displayed for the first 20 rows of the Bets worksheet to assist with the data entry process.
37
38
Date (required) - this field is crucial for the Performance Graph and is handy for filtering your results in the Performance Summary. It is highly recommended that you input your wagers in chronological order to avoid erroneous looking graphs. For this reason we recommend entering the date that you placed the wager rather than the event date. This avoids having to re-sort the data to get a meaningful graph. The dates are displayed in the format d-mmm-yy (e.g. 3-Nov-14), although you can actually input dates in the format dd/mm/yyyy (e.g. 03/11/2014). Note that if you highlight a cell, the cell formula will have the dd/mm/yyyy format despite the fact that the cell is displayed on the page as d-mmm-yy.
39
Bookmaker (required) - this field contains the bookmaker or betting exchange that the wager was placed with. To ensure data integrity, you must enter a list of your bookmakers on the Settings sheet first.
40
Sport/League (required) - this field enables you to filter your results on the Performance Summary sheet by sport. To ensure data integrity, you must enter a list of sports/leagues into the Settings sheet first. Depending on how you use the Custom column (discussed below), you can either use this field for sport and league (e.g. "Football - EPL") or you can use this field for sport (e.g. "Football") and then use the Custom column for the league (e.g. "EPL").
41
Selection (optional) - this is your wager selection. This field is for your reference only and is not referenced anywhere else in the spreadsheet. To give some input examples, if you back Team A to beat Team B your selection is "Team A". If you back the over in the total score market, your selection is "Over".
42
Bet Type (optional) - this field is recommended because it can be used as an additional filter in the Performance Summary sheet. It records the type of wager you placed. To ensure data integrity you must input a list of bet types into the Settings sheet first. Examples include head-to-head (a.k.a. moneyline), line (a.k.a. handicap or spread), total score, correct score, etc.
43
Tipper (optional) - subscribers of tipping services can use this field to track the betting performance of each tipper/capper. This field is referenced in the Performance Summary sheet and is required if you want to use the Tipper Analysis sheet. To ensure data integrity you need to enter your tippers into the Settings sheet first.
44
My Variable (optional) - this field can be renamed in the Settings sheet. It provides an additional filter by which you can analyse your betting data in the Performance Summary sheet. To ensure data integrity you need to input a list of acceptable values into the Custom Filter table of the Settings sheet first. You can use this field any way you want. Some example uses are:
- League - if you use the Sport / League column to enter just the sport, you can use this field for the specific league.
- Pick attribute - you could filter your wager data based on the home favourite, home underdog, road favourite, etc.
- Tip rating - if you subscribe to tippers who publish tip ratings you could enter them here.
45
Fixture/event (optional) - this records the game that you wagered on. This field is for your reference only and is not referenced anywhere else in the spreadsheet. For futures markets you could enter something like "2014/15 NBA Championship" or "2014/15 NBA MVP".
46
Live Bet (optional) - this is a simple Y or N field for whether your wager was placed in-play (a.k.a. live) or pre-game. You can leave this field blank for pre-game wagers.
47
Score/result (optional) - this records the final score or result for your selection. This field is for your reference only and is not referenced anywhere else in the spreadsheet.
48
Stake (required) - this is the currency amount wagered. For lay bets on betting exchanges you should input the backer's stake rather than the amount of money you're risking. Note that the amount of money at risk is automatically shown in the At Risk column. The at risk amount will equal the stake amount for all back bets (i.e. all wagers with traditional bookmakers). For betting exchange lay bets the stake could be greater or less than the at risk amount, depending on the odds.
49
Odds (required) - the bookmaker odds for your selection go in this field. If you are using an odds system other then decimal/European, change the odds type in the Settings sheet first. The + sign is not required for American odds such as +200.
50
FB (optional) - this is a simple Y or N field for whether your wager was placed using Free Bet money. You can leave this field blank for regular wagers.
51
Win (required) - this field defaults to "P" for pending bets. It is the only field for which input instructions appear for all rows, not just the first 20. Once your bet has concluded, input the bet result using the instructions that automatically appear on the Bets worksheet. The standard accepted values are P (pending bet), Y (winning bet), N (losing bet), R (refund) and H (half payout). For Asian Handicap wagers there are also the values PU (push), HW (half win) and HL (half lose).
52
53
Betting Exchange fields:
54
The following fields only apply if you placed your wager with a betting exchange.
55
Commission (optional) - enter the betting exchange commission here. The values are displayed in % form, so if the commission is 5%, enter "5" and not "0.05". Note that the commission is applied to your wager profit, which is the practice with most, but not all betting exchanges. If no commission applies to your wager you can leave this field blank.
56
Lay Bet? (optional) - if you are placing a lay bet (a bet that the selection WON'T win), input Y. If the wager is a standard back bet you can input N or simply leave the field blank.
57
58
Tipper's Betting Data fields (ADVANCED VERSION ONLY):
59
These optional fields enable you to input your tipper's wager details if they differ from your actual wager. It's not uncommon for odds and lines to change after a tipper has published a tip. For this reason your actual wager may be at different odds to what was quoted in the tip. For example, your tipper may have recommend you back Team A at 1.90 odds but the odds had dropped to 1.85 by the time you placed a wager. Alternatively, your tipper may have recommended you back Team A -8.5 at the line but the line had shifted to -9.0 by the time you placed your wager. In these circumstances your tipper's bet result and profit may differ from yours. If the tipper's bet details are the same as your wager you can leave these fields blank. This is because the spreadsheet will assume your wager details are identical to those provided by the tipper. These fields are referenced by the Performance Summary worksheet.
60
Odds (optional) - if you placed a wager at different odds to what was published in the tip, enter the tip's odds here. Be sure to use the same odds convention as what you have selected in the Settings worksheet.
61
Line (optional) - if you placed a line/spread/handicap wager at a different line to what was published in the tip, enter the tip's line here.
62
Win (optional) - if the tipper's bet result differs from your own, enter the tipper's bet result here. An example of when this could happen is if the tipper recommended Team A -8.5 and you wagered Team A -9.0. If the winning margin is 9, your tipper's bet won while your bet result was a push/refund.
63
64
Closing Odds & Lines Analysis fields (STANDARD AND ADVANCED VERSIONS ONLY):
65
The following three fields are designed for those who want to track whether they've beaten the closing odds & lines. By "beat" the closing odds we mean you placed a wager at better odds than were available just before the event began. These fields are referenced by the Performance Summary worksheet.
66
Closing Odds (optional) - input the closing odds for your selection (the odds available just before the event began). Be sure to use the same odds convention as what you selected in the Settings worksheet. Note that if you leave this field blank the spreadsheet will NOT assume that the closing odds were the same as your wager odds. Instead it will exclude the wager from the Closing Odds Analysis section of the Performance Summary worksheet.
67
Wager Line (optional) - if your wager is a line/spread/handicap bet (e.g. Team A +9.5), input the line into this field. For positive lines like +9.5 you can leave out the + sign. Note that this field is only effective if you use it in combination with the next field described below. Note that if you leave this and the next field blank the spreadsheet will NOT assume that the closing line was the same as your wager line. Instead it will exclude the wager from the Closing Line Analysis section of the Performance Summary worksheet.
68
Closing Line (optional) - if your wager is a line/spread/handicap bet (e.g. Team A +9.5), input the closing line (the line available just before the event started) into this field. For positive lines like +9.5 you can leave out the + sign. Note that this field is only effective if you use it in combination with the previous field described above. If you leave this and the previous field blank the spreadsheet will NOT assume that the closing line was the same as your wager line. Instead it will exclude the wager from the Closing Line Analysis section of the Performance Summary worksheet.
69
70
Data entry colour coding
71
72
To clarify which cells are editable and which aren't, the following colour coding is used for table headers:
73
74
Required input cells
75
Optional input cells
76
Automatically populated cells (do not edit)
77
78
How to input a standard multi bet
79
80
If you want to record detailed leg information for a multi (parlay) bet, we recommend you input the main bet details as one line in the Bets worksheet. You can then optionally input the selection details in the rows below. First, input M for the Win column in each of the selection rows (not for the first bet row). The spreadsheet will then cross out any rows that don't apply to that line. You can then use the selection rows to record bet details. These will be ignored by the rest of the spreadsheet, but will enable you to keep track of which legs won and lost.
81
If you're unsure about this process, download a fresh copy of the spreadsheet from the link below and look at the sample data in the Bets worksheet.
82
http://www.aussportsbetting.com/tools/google-sheets-betting-tracker/
83
84
Betting exchange wagers
85
86
If you do not use a betting exchange you can ignore columns O and P in the Bets worksheet. Feel free to hide these columns if you wish.
87
This spreadsheet enables you to input whether a wager is a back bet or a lay bet. It also enables you to input betting exchange commissions on winning wagers.
88
Note that this sheet only supports betting exchanges that charge commissions on the profits of winning bets. If you are using Matchbook, which charges commissions on both winning and losing wagers, you will need to manually tweak the stake amounts of losing bets to give the correct value.
89
Important: if you place a lay bet, input Y for the Win column in the Bets worksheet if your wager won and N if your wager lost. Input all other outcomes (HW, HL, PU, etc.) as per the result of the bet from a backing perspective. See the input examples below for clarification.
90
If you place one or more back or lay bets on the same market, tweak the commission(s) for the winning wager(s) as required so that the payout corresponds to your exchange account. This involves revising the commission downward. For example if your commission is usually 5% and you win $10 on a back bet and at the same time lose $5 on a lay bet on the same market, input 2.5% as your commission on the winning back bet.
91
Note that the inclusion of back and lay bets on the same market will distort the Performance Graph because the betting tracker won't acknowledge that the opposing bets were on the same market. Also, the Available Funds calculation will be off until the bet has concluded, because it won't acknowledge the fact that opposing bets on the same market free up funds. Lay bets at high odds will also inflate the concluded bet amounts, which will pull your percentage profit closer to zero. While this isn't ideal, this betting tracker is designed to be beginner friendly and versatile, hence the lack of additional exchange-specific features. Feel free to modify the spreadsheet further to suit your needs. The instructions to unlock the workbook are shown above in the 'Worksheet protection' section.
92
Exchange input examples
93
Example 1: you place a Back bet on Clijsters to win the Australian Open. The wager is for $10.00 at 4.50 odds.
94
- Input 10.00 into the Wager field
- Input 4.50 into the Odds field
- Input your commission level (e.g. 5%) into the Commission field
- Leave the Lay Bet field blank, or input N into this field
At the conclusion of the event, input Y into the Win field if Clijsters wins the tournament. Input N into the Win field if she doesn't. A win will result in a profit of $35 (before commission), while a loss will result in a profit of negative $10.
95
96
Example 2: you place a Lay bet on Clijsters to win the Australian Open. The wager is for $10.00 at 4.50 odds.
97
- Input 10.00 into the Wager field
- Input 4.50 into the Odds field
- Input your commission level (e.g. 5%) into the Commission field
- Input Y into the Lay Bet field
At the conclusion of the event, input N into the Win field if Clijsters wins the tournament. Input Y into the Win field if she doesn't. A win will result in a profit of $10 (before commission), while a loss will result in a profit of negative $35.
98
99
If the result is anything under than Y or N, input the result (R, HW, HL, etc.) from the backer's perspective, regardless of whether it is a back or lay bet. Note that the spreadsheet will calculate your profit/loss accordingly, depending on whether it is a lay bet.
100
Loading...
 
 
 
Intro
Settings
Deposits
Bets
Available Funds
Performance Summary
Performance Graph