Losing bets are no longer in RED in Column AA.

[WWWW]

HELP please!

I've been depressed all day, as it looks like I inadvertently messed up one of the functions of your wonderful Tracker spreadsheet.

Looks like it's been about a year since I last updated my Tracker and posted in this subforum.

A few issues:



1) When I put a "N" in Column N ("WIN") for a losing bet, it alway has appeared in red in Column AA (Profit/Loss).

I must have accidentally done something wrong, as now beginning with Row 4579 a losing bet still appears in Column AA in parentheses, but it's in black.

The rows above 4579 are still appearing in red. I've tried changing a "N" to a "Y", then back to an "N" in Rows above 4579, but the "N"'s will reappear correctly in red.

Also it appears that the losing bets beginning with Row 4579 work correctly - they register as far as I've briefly tested so far - if I highlight and scroll down Column AA they deduct from the highlighted total as they should.

But how can I correct this and get the losing amounts to appear in red again in Column AA?


2) I'm using V2_1.

I see that you've updated the Tracker a few times.

Can I simply cut and paste everything into the latest Tracker, or will this mess up the formulas?

(Incidentally, I began the current Tracker I'm using on Nov. 22, 2012, so my Fiscal Year is almost over).


One issue that I've meant to post about for a long time:

3) In Column G ("Tipster/Capper"), I put my code for the Tipster, but for people who release tips with specific lines I also add the line, as I want to be able to analyze results including knowing if the lines are attainable, if the results of the Tipster are affected by my not getting the release lines, etc.

The problem that creates is:

a) If I want to filter Column G for Tipster "ABC", instead of simply clicking on "ABC", I now have dozens and eventually will have hundreds of different "ABC" 's listed - such as "ABC -3 -112", "ABC +4 +100", etc.

b) Doing this renders the "Performance by Tipster/Capper" on the "Performance Summary" page useless, as I listed on the "Settings" page under "Tipsters/Cappers" simply "ABC" for that person, while the entries as mentioned are "ABC -3", etc.

Any ideas?


4) I recently noticed on the "Performance Summary" page that "Back vs Lay Betting", that everything is listed under "Back Bets".
I'm using USA odds - does this summary only work with Decimal Odds?


Thank you for your help with the above, and for the great work you've done here with this sheet.

[WWWW]

PS - Good news for me.

I closed the Tracker without saving the bets I'd added to it today (took a screen shot first so I could recopy them), and when I reopened it the losing bets now are listed in red as they should be.

I have had that error happen a few times in the past, but so far I've managed to get it working again.
But I would be interested in how to fix that, in case the next time it happens I can't cure it.

[WWWW]

One other issue I've been meaning to ask about forever:

Due to betting limits, often I need to make multiple bets for the same play.

E.g., Dallas +6.

So at Book "A" I bet $10 at +6 -110.
They move the odds to +6 -112, and I bet another $10.
Now at Book B I bet $12 at +6.5 -110.
etc.

The way I've been dealing with this is:

For the 1st of these bets, I've been filling in all the Columns - Date/Book/Sport/Selection/Bet Type/Tipster - Capper/Event (in the Event column, I've been listing the opponent), plus of course the Required fields.

For each of the following bets (which is really one bet or rather one play divided between multiple bets, sometimes at different books) I fill out the same columns EXCEPT I omit Tipster - Capper (because his play was Dallas +6 -112, and I'm following that play with all the bets).

When the results are known, I fill in the Score for the 1st bet only.

It's been almost a year since I started using this version - not sure I remember my reasoning for why I did it this way.

Of course this screws up the stats as far as how many different plays I've actually made, as one play may have 3-5 bets.
So the stats are unrealistic if I were to check how many DIFFERENT plays I've bet, how many bets I've made favoring Team A and how many bets with opponent B, how many bets in each sport, etc.

Any way around this or a better way to resolve this?

I seem to recall you stating that if one enters the info on one row and then adds no other info except the required fields in the next row immediately below, that the sheet reads it as the same bet? Do I have that correct?
Even if that is correct, in many cases it wouldn't help because:

To complicate things further, sometimes for various reasons I add on to the same bet at a later time - e.g., the +6 -110 is now available closer to gametime - hours or even days later - at +6 -102, so I buy some more at the improved price.
But in the interim I've made multiple bets on other plays, so I can't have the latest bet immediately beneath the other bets in the same play.

Also, sometimes my successive bets on the same play are at different books so I need to note the book info in each row.

[admin]

Hi WWWW, I have sent you a PM.

[admin]

Hi WWWW,

To answer your questions:

1)

I think this is a quirk with Excel. Whenever a cell doesn't appear in the correct colour or shading, try saving then closing it. Often the colours will reappear when the sheet is reopened. This may be due to the quantity and complication involved with the conditional formatting. It may slow down your computer too much if Excel constantly checked.


2)

I highly recommend using Paste -> Paste Values. This means that all data will conform to the number and cell formatting of the sheet. To do this, first copy the data and instead of using a keyboard shortcut like Ctrl+P, select the down arrow below the Paste button (in the home panel) and select the left icon (123) under "Paste Values".


3)

I wouldn't use the Tipster column in this fashion. I would input ABC for the tipster and the line into the Wager Line column (Column U, depending on your version). This way you have the information: ABC -3 -112 recorded within the Tipster, Odds and Wager Line columns. I would then tweak the performance summary page to display stats accordingly. Alternatively, if you are primarily making line wagers you could input the lines in the Bet Type column. Go to the Settings sheet and input a range of lines in the Bet Type Column (e.g. -25, -24, -23...). This way you can view performance data for a capper in the "Performance by Bet Type" table of the Performance Summary sheet. If you mainly make line wagers but also place total score or other wagers, you could use the bet type names: "Line -25", "Line -24", etc., along with other bet type names.


4)

Back versus Lay betting is specific to betting exchanges such as Betfair. The betting terminology differs between the USA and elsewhere, so "Lay" can have different meanings. If you are using a bookmaker then you are only making Back wagers. Lay wagers refer to when you lay a bet on a betting exchange so that you play the role of the bookmaker. They are bets that the selection DOESN'T win. For example, if you lay $10 on Roger Federer to win the ATP Masters at 4.00 decimal odds, then you win $10 if he doesn't win the tournament (minus commission), but you lose $30 if he does win the tournament. With lay wagers on exchanges your potential profit equals your stake, while the potential loss depends on the odds.


5)

For multiple bets on the same play, I started writing a way to account for this, but it became so convoluted that I actually recommend moving away from Excel to a database program like Access. You could have a table for events (fixture id, fixture time & date, league, home team, away team, final score), a table for selections (fixture id, selection id, bet type, selection) and a table for bookmaker wagers (selection id, bet id, bet date, bookmaker, tipster/capper, stake, odds). You could have multiple selections per event (i.e. bet on the home team at the line and take the unders). Critically, you could have multiple bookmaker wagers per selection. Using Access would provide far more versatility than Excel. Note that I choose to handicap what our Betting Tracker spreadsheet can do by opting not to use Macros (to avoid scaring people off). While Macros would help, I think Access would be best suited to your needs.

[Pixxer]

Nice spreadsheet. For sure I will use it.

Thank you!

[WWWW]

I've been having computer issues recently (my saved System Restore points periodically disappear), and have been planning to reformat windows for about 2 months, but in the last week having daily Excel problems now - crashes, sheets locking up, workbooks linking together and locking, etc. - so I'll probably do it tomorrow.

Which is about a day too late, as it looks like the Tracker is messed up - the Performance Summary page/ Betting Profit-Loss/ Net Profit - is showing about 10 % less than the profit shown in the Performance Graph. [I figure out and explain what the problem is in "UPDATE" in #1 below].

I love the Performance Graph, so this is upsetting, but glad to learn from your very helpful answers to my questions that I can paste the current info into the new version of your Tracker.

A few more questions for you:

1) In the Tracker I'm currently using, I've never had the Performance Graph show a higher figure than the Performance Summary page Net Profit figure (I don't think so).
Is there anything I could do to try to get them back in sync?
Or have I just inadvertently messed up a formula somewhere?
Is there anything else that could cause it?

UPDATE - I've fooled around with this problem, and have found the specific row where the problem begins.
The Performance Summary page Net Profit figure remains accurate.

However, the Performance Graph stops recognizing losing bets. If I input a "Y", it moves upward correctly.
But if I input an "N" into the Results column, the Graph no longer reacts to it.

Any easy fix for this problem?
I won't bring over the same problem to the newest version Tracker when I paste my data over, will I?

I'm hoping that if I've somehow messed up a formula in the Tracker I'm using, that pasting the data into the new sheet isn't going to carry along the formulas I've unintentionally messed up.
Or could it mess up the new sheet as well when I paste the data into it?

2)

"I'm using V2_1.

I see that you've updated the Tracker a few times.

Can I simply cut and paste everything into the latest Tracker, or will this mess up the formulas?"




"I highly recommend using Paste -> Paste Values. This means that all data will conform to the number and cell formatting of the sheet. To do this, first copy the data and instead of using a keyboard shortcut like Ctrl+P, select the down arrow below the Paste button (in the home panel) and select the left icon (123) under "Paste Values"."


"To do this, first copy the data..."


To clarify - I was planning to simply cut and paste the data from my current sheet to your newest one.
I cut and paste by highlighting and Control/C.
Is that the way you would copy the data?


"instead of using a keyboard shortcut like Ctrl+P, select the down arrow below the Paste button (in the home panel) and select the left icon (123) under "Paste Values"."

Just curious - I normally, after saving data with Ctrl/C, click where I want to put it and then use Ctrl/V.
I've never used Ctrl/P. Same thing?
EDIT - I just tried Ctrl/P and it brought up my Printer popup window.
I assume you meant Ctrl/V?

But I understand that I should use the "123" icon under Paste Values.
Is the reason for using this that it will input the data without carrying over any of the underlying formulas from the other sheet, which would be carried over otherwise and possibly cause problems to the sheet?



Thanks for your help.
Your Tracker is great and it's important to me that I can keep it functioning correctly (or transfer to the new sheet and have that function correctly).

[WWWW]

"3)

I wouldn't use the Tipster column in this fashion. I would input ABC for the tipster and the line into the Wager Line column (Column U, depending on your version). This way you have the information: ABC -3 -112 recorded within the Tipster, Odds and Wager Line columns. I would then tweak the performance summary page to display stats accordingly. Alternatively, if you are primarily making line wagers you could input the lines in the Bet Type column. Go to the Settings sheet and input a range of lines in the Bet Type Column (e.g. -25, -24, -23...). This way you can view performance data for a capper in the "Performance by Bet Type" table of the Performance Summary sheet. If you mainly make line wagers but also place total score or other wagers, you could use the bet type names: "Line -25", "Line -24", etc., along with other bet type names."


"I would input ABC for the tipster and the line into the Wager Line column (Column U, depending on your version)."

This would work for me currently as - even though I'd like to - I haven't been using Columns R thru W.
But if I were to begin tracking my BTCL (Beat the Closing Line) I would want to know how my bets are beating the closing line, not the capper's release line. So that would not work.
[EDIT - Having thought about it more ...ideally I'd like both. If I had to choose which one - it would be tracking my own BTCL, because the tipster's BTCL would be meaningless if I can't bet at his release numbers.]

My suggestion is to add a Column between G and H where one can enter the Tipster's line/over-under total/etc.
As we know, many times a tipster releases plays at numbers which either don't exist currently/never existed, or followers get the release number before I can get it.
So if I see that a tipster's results are different than mine - which almost always means better than mine - I can easily compare his release numbers with my own bet numbers.

For now though, I guess I'll use Column U for that as you suggested since I'm currently not using it for anything else.

[WWWW]

"5)

For multiple bets on the same play, I started writing a way to account for this, but it became so convoluted that I actually recommend moving away from Excel to a database program like Access. You could have a table for events (fixture id, fixture time & date, league, home team, away team, final score), a table for selections (fixture id, selection id, bet type, selection) and a table for bookmaker wagers (selection id, bet id, bet date, bookmaker, tipster/capper, stake, odds). You could have multiple selections per event (i.e. bet on the home team at the line and take the unders). Critically, you could have multiple bookmaker wagers per selection. Using Access would provide far more versatility than Excel. Note that I choose to handicap what our Betting Tracker spreadsheet can do by opting not to use Macros (to avoid scaring people off). While Macros would help, I think Access would be best suited to your needs."

Unfortunately I have only minimal Excel knowledge and have never used Access, so I appreciate the suggestion but I'll make do with the Betting Tracker as is.

When I had multiple bets on the same play due to limits and/or the number improving and betting it again, I was entering the tipster's name in Column G only for the 1st bet of the group of bets.
This way I can count how many separate plays I've made by counting how many times the tipster's name appears.
So if tipster A says "Bet the Colts -3", I will enter "A" in Column G for the 1st bet I make on the Colts, even if I make 5 bets total of Colts -3.
This is working ok if I am only using one tipster for a sport.
But if I'm using more than one tipster for a sport, and I want to filter for how I'm doing in that sport on Tipster A's plays and also Tipster B's plays, I filter by "A" but the "Blanks" - the rows for which I haven't entered anything in Column G - are sometimes bets of A's plays and sometimes B's plays.
I'm finding I can't get an accurate accounting of how I'm doing with Tipster A, because some bets of Tipster B get included when I've left Column G blank (haven't specified a tipster, for the reason given above).

So I find that I need to enter the tipster's name in each row.
I've decided that my solution is I'm going to use "A" for the 1st bet of a group of bets on the same play.
For additional bets for the same play, I'm going to use "A1" for each of them.
So if I later filter Column G for tipster "A", each entry will be a unique play of his.

If I want to find out my results of all bets I've made on Tipster A's plays, I'll filter for "A" and "A1".

The other advantage to this method is if I want to find my average bet size per play of Tipster A, I can count how many unique plays of his
by filtering Column G for "A", and then to find out the total wagered on all his plays I filter Column G for "A" and "A1".

I hope that's not too confusing. I've been thinking about how to deal with the above, and that is what I've come up with.
But I'd be interested in any suggestions.

[admin]

Hi WWWW,

1)

The performance graph uses cells in the Bets sheet (somewhere around column AZ, depending on the version). You have to scroll right to see it. Either the graph issue stems from those cells or the graph itself has been compromised in terms of the cells it references.

Providing you import using Paste->Special->Values, the graph problems shouldn't occur on a fresh copy of the spreadsheet.

If in doubt, paste your data into a fresh copy because it doesn't take much time to do.

2)

Yes, you can copy by highlighting the cells and Ctrl+C.

Sorry, when I wrote Ctrl+P I mean't Ctrl+V.

Yes, the Paste values "123" option is designed to prevent superimposing unwanted formulas and formatting into the new sheet.


3)

If you wanted to track your tipster's performance using their line rather than the one you were able to get, one solution is to maintain two versions of the spreadsheet. One uses the line you were able to get, the other uses the line that the tipster quoted. Obviously the downside of this approach is it means double the data entry.

I probably won't release a version (at least at this stage) with your tipper's lines because I try to give the spreadsheet a broad appeal. I've received a huge number of specific requests and if I implemented them all the spreadsheet would become difficult to use. Also, to make best use of such data, the best way would be to create a version that caters heavily to line betting. This would involve columns for your line, the tipsters line and each team's score. Your win/loss result would be determined for you once you inputted the scores. Who knows, perhaps this would become an option in the future as a version specifically designed for USA sports wagering, as line betting isn't as popular elsewhere.

5)

I recommend entering the tipster in each row. While this will throw off the bet counts for wins and losses, crucially, it will be accurate for profit/loss, which is the most important metric.

The system you came up with regarding "A" and "A1" sounds like a good work around.

Perhaps one day I'll write a web-based betting tracker. Users would need Internet access to use it but it would be able to support much broader features than Excel. It also wouldn't require users having specific database software, such as Access. Also, updates and fixes could be made without requiring any work from users.