[LVBOUND]

Guys,

I got an excel chart where I have the line I bought the game for in Cell (X)

I got the Pinny closing line in cell (Y)

so for example I got

Boston at -115
Pinny closed at -120

so I got my line 5 cents better than the closer.

In CELL (Z) I want to put some type of formula so I can track my advantage or disadvantage all year?

Does anybody know a good formula so I can get a good +/- read?

Matt

[Optional]

in Z1 put =Y1-X1

in Z2 put =Z1+(Y2-X2)

and copy the contents of cell Z2 down the column.

That will give you a running tally of how you are doing against the line. I think that's what you want?

[durito]

You want to compare to the no-vig line.

http://www.sportsbookreview.com/forum/handicappe...rcentages.html

Then you can use Ganch's excel function (ProbUS2Edge(Probability, USOdds) or ProbDec2Edge(Probability, DecimalOdds): Calculates edge based on win probability and US or decimal odds. Example: ProbUS2Edge(55%,-110) = 5%) to compute your edge v the closing line:

http://www.sportsbookreview.com/forum/handicappe...ate-excel.html

[Pokerjoe]

If the -115 is in A1 and the -120 is in B1, in C1 put

=IF(OR(A1="",B1=""),"",IF(AND(A1<0,B1<0),(1/(1/(B1/-1)*100+1)*100)-(1/(1/(A1/-1)*100+1)*100),IF(AND(A1<0,B1>0),((1/(1/(B1/1)*100+1)*100-100)/-1)-(1/(1/(A1/-1)*100+1)*100),IF(AND(A1>0,B1>0),((1/(1/(B1/1)*100+1)*100-100)/-1)-(1/(1/(A1/1)*100+1)*100-100)/-1,(1/(1/(B1/-1)*100+1)*100)-(1/(1/(A1/1)*100+1)*100-100)/-1))))

So, the line you got in A1, the pinny close in B1, C1 then tells you how you did against the close. Using "cents" won't work; you have to convert it to percentages.

Then, just average column C to find your average performance against the pinny close.

Obviously, adjust this formula for which ever cells you actually have the data in.

Obviously if I flubbed this someone will flame me (this is TT).

Nice meeting you in Vegas, btw. Hope the rest of your weekend was fun. Mine consisted of a drive back home because I had broken a tooth on the drive up that day and wanted to see my regular dentist. I'm guessing you had more fun than that, LOL.

[Pokerjoe]

BTW, I wrote this so fast there's maybe a simpler formula for it, I don't know. I have the bad habit of brute forcing a lot of excel work. And, obv, this is comparing your price to the pinny close, not the pinny no-vig, which you didn't ask for.

[Flight]

PokerJoe, that can be simplified by using the VBA functions Ganchrow provided.

US2Fair - convert closing moneyline to no-vig line (requires both sides of the moneyline)
US2Prob - convert the no vig-line and the wagered moneyline to probabilities.

Subtract the 2 probabilities, you get an edge.

You could just do it all with raw moneylines (Y-X like Optional stated) for a quick and dirty look at how you do in terms of moneyline cents. The real problems with this approach is you don't account for vigorish, and US moneylines are non-linear (i.e. 5 cents edge at -300 isn't that much in terms of p). If you bet everything around -110, it will give you a decent gut feel.

[justzend]

I'm sorry for sounding like such a newb moron, but I was wondering if someone could tell me a simple excel formula for calculating the price, risk, to win, like on the SBR Spreadsheet; Where after I input the price and then place a dollar amount in either the risk or to win, it automatically calculates the opposite for me.

Thanks to anyone who can help me :-)

[Pokerjoe]

PokerJoe, that can be simplified by using the VBA functions Ganchrow provided.

US2Fair - convert closing moneyline to no-vig line (requires both sides of the moneyline)
US2Prob - convert the no vig-line and the wagered moneyline to probabilities.

Subtract the 2 probabilities, you get an edge.

You could just do it all with raw moneylines (Y-X like Optional stated) for a quick and dirty look at how you do in terms of moneyline cents. The real problems with this approach is you don't account for vigorish, and US moneylines are non-linear (i.e. 5 cents edge at -300 isn't that much in terms of p). If you bet everything around -110, it will give you a decent gut feel.

a) I not sure OP can use ganchrow's VBA functions; if he could, he probably wouldn't have needed someone to write such a relatively simple formula for him to begin with. People have varying levels of ability, which is fine; I wrote a one-cell formula he can cut-and-paste from here without further explanation. 1 cell>3 cells, imo, unless the other cells are independently useful (which they might be, but which OP didn't request).
b) You can't do it using Optional's equation at all, because you can't add and subtract US moneylines at all (for any useful purpose) unless they're both faves or dogs.
c) About not accounting for vigorish: OP didn't ask for that. It's easily added if he wants to do that. He can even just generically account for it if he wants to (he's a one sport bettor). I think, actually, for Pinny MLB, it's 1.5%, so, Matt, if your model averages 1.5% advantage over the CL, don't get excited, you're actually just a push-bot. (somebody fix that 1.5% if needed, please; it's top-of-my-coffee-less head this AM; isn't it 1.5%? I don't know, I'm not a BTCL guy)
d) About the non-linearity of US lines: my formula specifically DOES account for that; that's why I wrote it for him, so that he would get out of the habit of judging line-value by cents.

[Optional]

b) You can't do it using Optional's equation at all, because you can't add and subtract US moneylines at all (for any useful purpose) unless they're both faves or dogs.

Yeah sorry for the mis-information. I work in decimal odds and didn't think about it long enough before posting.

[Flight]

Thanks Joe. I was just trying to push the use and understanding of the VBA functions. They are immensely helpful, encourages programming to solve problems, and not enough gamblers understand these simple conversions.

Your formula is great in it's own way.

(somebody fix that 1.5% if needed, please; it's top-of-my-coffee-less head this AM; isn't it 1.5%? I don't know, I'm not a BTCL guy)

For Pinnacle -104 lines, 1.9% edge is the rate to achieve to become a push-bot.

BTCL = Beat The Closing Line???

Wish I could've met you guys at the bash.

[Pokerjoe]

Yeah sorry for the mis-information. I work in decimal odds and didn't think about it long enough before posting.

Yeah, me, too. US format kind of sucks.

[Pokerjoe]

Thanks Joe. I was just trying to push the use and understanding of the VBA functions. They are immensely helpful, encourages programming to solve problems, and not enough gamblers understand these simple conversions.

Your formula is great in it's own way.


For Pinnacle -104 lines, 1.9% edge is the rate to achieve to become a push-bot.

BTCL = Beat The Closing Line???

Wish I could've met you guys at the bash.

Yeah, that's BTCL.

And I actually shouldn't use the term "push-bot,", LOL. It's an online poker phrase meaning to just have two speeds, all-in or fold. I just adapted in on the fly here. I mean, if you have a program that just breaks even ...

And maybe next bash HTT should have it's own meetup.

[LVBOUND]

Pokerjoe,

I just did the formula and it doesnt seem correct? what am I doing wrong,

Here is the screen shot..

Matt

[Flight]

LVBOUND,

I just checked those four answers with SBR's arbitrage calculator and it seems correct to me (within 5 hundredths of a percent at least). Arbitrage calculator can be used in reverse to determine edge (instead of using it to determine a book's theoretical hold, you determine the player's theoretical hold. Just inverse the Pinny close).

They are in units of %, so 1.1%, .75%, .25%, etc. You're not getting close enough to the 1.9% push bot numbers.

[Flight]

Also, is that the pinnacle no-vig line or the vig line?

[LVBOUND]

Flight great answers, but I am a little confused. Can you 1st grade teacher me for just a second.

This is what I am doing, the number on the right is the number I have been getting (overnights) so they have been very good on average. After speaking to pokerjoe and iceman at the bash they said that I shoudl compare my numbers to the closing pinny line.

So I did and I am trying to come up with a number to track how I have been doing agaisnt Pinny's closing. So I have a few questions if you dont mind.

#1) What is the diff in a Vig and No Vig Line?
#2) What is a Push Bot?

Now that you have read this how would you go about doing what I am trying to do.

Thanks,
Matt

[Flight]

1) You can read more about understanding vigorish at http://www.pinnaclesports.com/bettin...ips/moneylines

In your spreadsheet, if you are entering the numbers shown on sbrodds.com for the side you played, that is the Vig line. The no-vig line is somewhere in between the two. You can get a quick estimate of the no-vig line by just taking half the distance between the two moneylines. There are several threads on this topic if you want to know how to calculate it yourself (strongly recommended since you have a spreadsheet already). Note that you will need to record both sides of the Pinnacle closing moneyline to accurately get the no-vig line. (not just the side you wagered)

Calculation walk-thru
http://www.sportsbookreview.com/forum/handicappe...-vig-line.html

More info from Ganchrow
http://www.sportsbookreview.com/forum/handicappe...rcentages.html

2) Sorry I was trying to have some fun with coffee-less Pokerjoe. I didn't intend to confuse you. Push-bot was a way of saying what % frequency of your wagers do you need to win in order to break even in the long run. At -110 odds this number is 52.38%. From the 2nd link:

were a line offered at -110 then for every $110 risked, $210 would be returned. $110 is 52.38% of $210, meaning that the implied probability of a -110 line is 52.38%. As such, if your bets at -110 win with frequency greater than 52.38% you’ll be making money, and conversely if your bets at -110 win less 52.38% of the time you’ll be losing money.

BTW I was wrong this morning. If you are playing at Pinnacle (-104 juice), the break even point is 51%. (1.4% lower than a -110 line, significantly helping the avg player). To calculate the percent break even point for a given wager take ($ risked)/($ returned) for a winning wager, e.g. 104/204 = 51% for Pinnacle, 110/210 = 52.4% for Bookmaker.

The number I gave earlier today (1.9%) is the theoretical hold for Pinnacle, a different matter.

3) How would you go about doing this for your spreadsheet? Since I am assuming you are just quoting the juiced Pinnacle closing line, you would have to go back and get the other side of the moneylines and then calculate the no-vig line using the formulas referenced. If that is too much work to get back and get the historical closer, you could just subtract 1% from the pinnacle number (the assumption you make there is that the juice is 1% for all your wagers. It is not, but a decent assumption since you don't have the no-vig line available). This tells you the true line. It will hurt you across the board, but gives you a more accurate estimate of your edge.

For example, if you bet a game at +100, and Pinnacle closes at -104, your edge is 0%, since the Pinnacle no-vig closer in that case is also +100.

Make sense?

[LVBOUND]

"

[Flight]

Here is a spreadsheet that walks you through the calculation. I did it one step at a time so you can look at how things get converted. In a practical spreadsheet I would either hide the intermediate columns, or condense the calcs into one formula.

In the last two columns is the real edge for this scenario and the incorrect edge you think you got by not taking into account vig. It is about 1% off, you can't claim that 1% for yourself.

Does this all make sense? Please post any questions you have, given the time investment, I want to make sure you get this (and anyone else reading).

Note that I am using the VBA functions provided by Ganchrow in this attachment. If you want to look at what US2Prob does (or any other utility conversion), just hit ALT+F11 and look at Module1 for the VB code that is really easy to understand.

[Flight]

The no-vig line for this example is -154. Your edge is 5.1%.

[Optional]

I was just trying to push the use and understanding of the VBA functions.

When I load these up as default functions it really slows down my excel :\

[Pokerjoe]

Looks like Flight has well answered all your Q's, Matt. From your screenshot, I'd say you have it right.

Because your spreadsheet doesn't have the Pinny buy-back numbers (the other side of the pinny close), you'll just have to estimate the no-vig line, but that's safely done because you aren't mixing up your sports on this sheet. The main thing to remember, and the reason you need to consider the no-vig line, is because beating the vig line isn't beating the vig, and it's the vig you have to beat.

And if your results don't look as handsome as you'd hoped, it's because your edges might not have been as strong as you thought. You bet a lot of favorites, iirc; it's easy to see 10 cent edges at big prices and think they're big. When properly sized, properly expressed, not so much.

Then again, a 1% edge, if true, is a goldmine.

Good luck.

[Flight]

When I load these up as default functions it really slows down my excel :\

You could try removing them as an add-in (default functions).

I only copy them into a workbook when necessary, so that they're installed at the document-level, not the application-level.

[Optional]

Thanks, I'll try that.