v2.1 now available - support for closing odds and closing lines

[admin]

Version 2.1 of the Excel Betting Tracker has been released. As a result of numerous requests, the workbook now supports closing odds and lines/spreads. This version is designed for those who strive to ‘beat the closing line.’ Punters who don’t require this feature should use version 2.02 instead.

Version 2.1 contains the following new features:

• Closing odds and line/spread columns added to the Bets worksheet
• New ‘Closing Odds Analysis’ and ‘Closing Line Analysis’ worksheets that provide statistics on beating the closing odds/line.
• Bookmaker turnover column added to the ‘Available Funds’ worksheet
• Additional FAQs added to the ‘Intro’ worksheet

Click here to learn more about the spreadsheet.

[iCap]

Version 2.1 of the Excel Betting Tracker has been released. As a result of numerous requests, the workbook now supports closing odds and lines/spreads. This version is designed for those who strive to ‘beat the closing line.’ Punters who don’t require this feature should use version 2.02 instead.

Version 2.1 contains the following new features:

• Closing odds and line/spread columns added to the Bets worksheet
• New ‘Closing Odds Analysis’ and ‘Closing Line Analysis’ worksheets that provide statistics on beating the closing odds/line.
• Bookmaker turnover column added to the ‘Available Funds’ worksheet
• Additional FAQs added to the ‘Intro’ worksheet

Click here to learn more about the spreadsheet.

Could you give us an example; as to where to input the following information base on this MLB match (Texas Rangers Over 5 -120* vs Oakland Athletics ) Opening Line -115 Closing Line -125 Paid Line -120

Closing Odd ? (Odds Analysis)
Wager Line ? (Line & Spread Analysis)
Closing ? (Line & Spread Analysis)

Whats the difference between the closing odds input and the closing line ( I would think they have same meaning). Shouldn't you have an open line column to compute what you beat the closer at?

[admin]

Hi iCap,

For that particular wager you would input the bet details as follows:



There can be confusion over "closing line" because betting terminology differs around the world. In American parlance you can think of the closing odds as the closing line and the closing line as the closing spread. Feel free to edit the spreadsheet to change the terminology if that suits.

The line/spread analysis is for spread bets. Suppose you backed the Cleveland Browns +12.5 against the Ravens at -110 and the market shifts to Cleveland +11.0 at -110. While the odds haven't shifted, the spread has. In this case you could input that bet as:



Let me know if that clears things up.

[iCap]

Hi iCap,

For that particular wager you would input the bet details as follows:



There can be confusion over "closing line" because betting terminology differs around the world. In American parlance you can think of the closing odds as the closing line and the closing line as the closing spread. Feel free to edit the spreadsheet to change the terminology if that suits.

The line/spread analysis is for spread bets. Suppose you backed the Cleveland Browns +12.5 against the Ravens at -110 and the market shifts to Cleveland +11.0 at -110. While the odds haven't shifted, the spread has. In this case you could input that bet as:



Let me know if that clears things up.

It's clear now, thanks for the image.

[Parker]

I love this tracker. Thanks for all the hard work.
The new feature on closing lines is great but being from america and working with silly american odds it can be difficult to use both sets of odds. It appears that all the closing line data is displayed as decimal odds. How can I display all my closing line data in American odds?
Thanks

[admin]

Hi Parker,

Thanks for the positive feedback.

I did consider providing support for all odds versions in the Closing Odds Analysis sheet, however the formulas get a bid wild when incorporating systems like American odds where odds can shift from, say -105 to +105. Perhaps support for other odds will be added in a later version.

In the meantime I suggest you focus on the 'Beat Close Percentage Analysis' rather than the 'Beat Close Analysis'. Due to the way that decimal odds work, the percentage analysis shows the profit improvement by beating the closing line. For example if you wager $100 at -110 and the closing line is -120, the beat close % (in decimal odds) is 9.1%. This means a winning wager at -110 provides a 9.1% greater profit than a corresponding wager at -120.

$100 at -110 odds => $90.91 profit.
$100 at -120 odds => $83.33 profit

($90.91 - $83.33) / $83.33 = 0.091 = 9.1%.

[WWWW]

If a bet is Pending ("P" in the "Win" column), the row is colored yellow. This is true in the download of your new version.
If the P is changed eventually to W or N, the yellow remains thru most of the row.

Is this intended? Why?

I can see the yellow as being an easy way to spot one's pending wagers. But since it remains afterwards, what's the point?

PS - Great spreadsheet and just downloaded this new version with new features I appreciate. Eager to begin using it.

[admin]

If you save the spreadsheet, close it, then re-open it, the yellow will be gone. It's just a quirk of Excel (to my knowledge, anyway) that the yellow is still displayed temporarily.

---

UPDATE:

If you wish to refresh all conditional formatting without having to close and re-open the file, there are two options:

1. Enter CTRL+ALT+SHIFT+F9
Just note that you may get a 'Not Responding' message while the workbook re-evaluates every cell.

2. Go to the Bets sheet. Select the Developer tab. Click on the Developer menu and go to the Visual basic view. Click on the menu View and select "properties Window". In that list you can find the "EnableFormatConditionsCalculation". Set this value to "True" and the auto refresh will work.

While option 2 has the advantage of being permanent, you will get Macro warning errors whenever you open the file.

[WWWW]

If you save the spreadsheet, close it, then re-open it, the yellow will be gone. It's just a quirk of Excel (to my knowledge, anyway) that the yellow is still displayed temporarily.

Thanks for the prompt response and explaining this.

I have a few more questions/suggestions about the sheet:

1) I'd like to see the Payout listed when I input the Stake and Odds.
Is there a way to do this?
Then when I grade the bet (Y or N), the Payout would disappear if it is a N.

Suggestion for the next version:
Add a column in the Automatically Calculated, make it the first column in that section, called To Win, which would show the amount potentially to be won on the wager.



2) I'd like the sheet to automatically assume a wager is Pending, so the sheet doesn't show all pending wagers as a loss, making it impossible to keep an accurate record of where I am at any given time in terms of W/L, Available Funds, etc.

I realize I can input P into that column, and then change it when the bet is resolved.
I used to cut and paste a P for 100's of rows in advance.
But with the later versions of the sheet it now turns the page Yellow, which I'd rather not do.

Is there another way I can make the sheet assign a default P to all wagers without grading them as N's, and without turning the sheet yellow?

I'd also like to suggest for the next version that the sheet automatically assumes a bet is P unless otherwise input.




3) Can I write notes for a row in the following area: AB thru and including AW, without messing anything up (without ruining anything - formulas, etc.)?




4) I'd like to add some more columns.
These are specifically the columns I'd like to add - I mention these as suggestion for future sheets:
a) To the right of Selection, add Opponent.
b) After Selection, Opponent, Bet Type, add a column called Handicap (spread or total).
c) Just before the present Column K, add a column called Notes.

d) Add 2 columns - Selected Score and Opponent Score, to input the actual scores, into your Analysis section (current columns R thru W).
This could calculate how close/far the Selection's score was from the Spread/Handicap or Total.
This would also make the current column J (Score) irrelevant.

These are specific Columns that I would like to add.
I'm mentioning these in case you or others reading this feel these would be worthwhile additions.
But if not, how can I add these columns into the Optional areas of the sheet?

[admin]

Glad I could help!

I try to keep the number of columns to a minimum, so as not to deter beginner users. I get numerous requests and if I catered to all of them the spreadsheet would quickly become a mess. If you want to edit the bets sheet yourself simply unlock it using the password sport. Click on the column letter (to highlight the entire column) to the right of where you want to add an additional column and click on the Insert icon.

Yes, you can write in AB through AW. These were simply put in to push the working cells to the right and out of sight.

Thanks, I will take your other comments into consideration when developing the next version.