MTSW 2018 Bets
The version of the browser you are using is no longer supported. Please upgrade to a supported browser.Dismiss

ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
Introduction
2
3
4
Version
5
6
This is version 2.21 of the Basic Excel betting tracker. Versions 2.0 and onward use the .xlsx filename extension and require Microsoft Office 2007 or later due to the lengthy formulas and use of countifs() and sumifs() functions.
7
8
9
10
This spreadsheet is free for personal use. Feel free to make amendments to the sheet to suit your needs. The password to unlock the sheet is sport.
11
12
http://www.aussportsbetting.com/tools/betting-tracker-excel-worksheet/
13
14
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 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 bookmakers along with the sports you plan to bet on. We recommend you also input bet types. You can also change the name of the custom filter. Change the odds format and currency rounding if necessary. If you like to measure your performance in units rather than a currency amount, enter the currency amount that one unit equals.
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 bets and bonus credit
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
BB (optional) - this is a simple Y or N field for whether your wager was placed using Bonus Bet money. You can leave this field blank for regular wagers.
51
Win (required) - this field defaults to "P" for pending bets. 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
Cashing Out Fields
54
The following fields are to be used for cashed out bets. Note that regardless of whether you cash out or not, complete the bet result column (Y, N, R, etc.) anyway. The Performance Summary will compare the net profit for your actual betting to the hypothetical profit had you never cashed out.
55
Amount Received (optional) - enter the currency amount that you received for cashing out.
56
% of Bet (optional) - some bookmakers enable you to cash out a percentage of the bet rather than the entire stake. If you have cashed out of this bet enter the percentage of the bet that you cashed out. If you have cashed out the entire bet enter 100%.
57
Please note that the spreadsheet does not currently support multiple cashing out on the same bet (e.g. cashing out 50% at \$12 and then cashing out another 25% at \$7). In these circumstances it is recommended that you split the bet into two or more bets, where necessary. In previous example you would then record cashing out 100% of the first bet at \$12 and 50% of the second bet at \$7.
58
59
Betting Exchange fields:
60
The following fields only apply if you placed your wager with a betting exchange. If you have entered one or more betting exchanges in the Settings sheet, be sure to select the appropriate commission structure for each agency in the Bookmakers table. The Commission Structure field is only really necessary for Matchbook, which uses a unique commission structure, rather than the profit-based approach used by Betfair, Smarkets and BETDAQ.
61
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". If no commission applies to your wager you can leave this field blank.
62
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.
63
64
Data entry colour coding
65
66
To clarify which cells are editable and which aren't, the following colour coding is used for table headers:
67
68
Required input cells
69
Optional input cells
70
Automatically populated cells (do not edit)
71
72
How to input a standard multi bet
73
74
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.
75
76
http://www.aussportsbetting.com/tools/betting-tracker-excel-worksheet/
77
78
Worksheet protection
79
Worksheet protection
80
81
As it stands, only limited sections of this spreadsheet can be edited. This is to prevent key formulas from being accidentally overwritten.
82
To unlock a worksheet, go to that worksheet and select the Review header ribbon, then click on Unprotect Sheet.
83
The password to unlock each worksheet is sport
84
85
Betting exchange wagers
86
Betting exchange wagers
87
88
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.
89
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.
90
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.
91
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.
92
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.
93
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.
94
95
Exchange input examples
96
Example 1: you place a Back 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
- 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.
98
99
Example 2: you place a Lay bet on Clijsters to win the Australian Open. The wager is for \$10.00 at 4.50 odds.
100
- 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.