Excel Spreadsheet, any help appreciated!

[louboutin1]

Hey guys!
So, I'm not sure if this is the appropriate board, but as its the only forum I'm using, and I'm using Geeks Toy..I thought why not give it a try!
So I'm a complete beginner, with less than 200 bets placed lifetime, but I like it and I feel like continuing doing it..so to the point>
I'm trying to make/use an excel spreadsheet to track my history etc. and for this I downloaded a spreadsheet from betandskill.com which seems very good. (http://www.betandskill.com/betting-t...worksheet.html) Basically, its perfect...EXCEPT I'm not sure how to input bets when I LAY, as it's really just made for normal bets. THey say that if I want to insert a laybet I will have to:

* If you place a lay bet with Betfair, we recommend you input it into the spreadsheet as follows. This example is for a $5.00 wager against an outcome at 4.00 odds. $5.00 is the lay stake amount and the lay odds are 4.00.

Input the stake amount as stake * (lay odds - 1), i.e "=5.00*3"
This reflects the fact that you have put $15.00 at risk by placing the wager.
Input the odds as 1 + 1/(lay odds - 1), i.e "=1 + 1/3"
This reflects the fact that you have put $15.00 at risk for the possibility of winning a $5.00 profit. The bet is equivalent to wagering $15 at 1.33 odds, because $15*1.33 = $20, and $20 - $15 = $5.

...But what if the Lay odds were less than 2.0.I will end up with multiplying the stake with a number less than 1 which makes everything messy...
If any1 has any idea how to do it, or a different spreadsheet or whatever I would greatly appreciate it.
If this is the wrong board to ask for stuff like this, I'm terribly sorry!
Regards!

[Wednesday 13]

...But what if the Lay odds were less than 2.0.I will end up with multiplying the stake with a number less than 1 which makes everything messy...

Yes, you do. And I don't understand why would make the spreadsheet messy - you'll still have the stake and the odds.

In you example, stake $5 and lay odds of 4.00, you risk 15 $ (which is 5*(4.00-1) ) to win gross of 5$, or net of 5*(1-C), where C is commission in % (i.e., if commission is 5%, your net profit is 5*(1-0.05) = 4.75$). So, if you're looking at it as back bet, then back stake is liability, and back odds are

Back Odds = 1 + Profit / Back Stake

Profit = Lay Stake * (1 - C)

Back Stake = Liability = Lay Stake * (Lay Odds - 1)

So,

Back Odds = 1 + Lay Stake * (1 - C) / (Lay Stake * (Lay Odds - 1) = 1 + (1 - C) / (Lay Odds - 1)

In your example:

Back Odds = 1 + (1 - 0.05) / (4.00 - 1) = 1.3166

Back Stake = 5.00 * (4.00 - 1) = 15

And those are numbers you need to enter in bthe spreadsheet; let's check: you're risking 15$, and your possible profit is 15*(1.3166-1) = 4.75 $, which is correct.

If you stake 5$ at lay odds of 2.50, you're risking 5$ * (2.50 - 1) = 7.50$ to win 4.75$ (after commission). So in the spreadsheet, you enter stake of 7.50 $, and odds of 1+(1-0.05)/(2.50-1) = 1.6333. So, if you lose, loss is 7.50$, and if you win, profit is 7.50*(1.6333-1) = 4.75$, which is again correct.

If you stake 5$ at odds of 1.80, you're risking 5$ * (1.80-1) = 4$ to win 4.75$ after commission. So you enter stake of 4$ and odds of 1+(1-0.05)/(1.80-1) = 2.1875. So, if you lose, you lose 4$, and if you win, profit is 4$ * (2.1875-1) = 4.75$. That's again correct, and I don't see why is it messy if lay odds are lower than evens?

BTW, thanks for the link, nice resources they have there!

[louboutin1]

Hi, and thank you for such an informative answer!!!
My problem is that unfortunately I'm not as good as most with math, so if you could help me out just a little more so I get this straight, I would be forever greatful!
Thing is, this spreadsheet actually takes commission into account by itself, let me give you a screenshot so maybe it makes more sense: http://puu.sh/cRCmZ/66cdad2705.png
In the last example is where I am stuck.. I layed kirilenko with 20eu at 1.47odds and then went on to back her for 26.25 at 1.12(just greened up). Here is the statement: http://puu.sh/cRCvy/b783be7045.png

How would you enter it into the spreadsheet?
Thank you _so_ much for taking your time to help me !
And you are most welcome for the link.

[Wednesday 13]

Hi, Louboutin, you welcome!

Now, your additional question is quite different than the orginal one, and to be honest, I do not know the answer.

If there was no commission, you could just enter them as two separate bets. But, as you know, commission is calculated on net profit, so it would not be correct. Maybe you could take your maximum exposure at any moment (liability for lay bets, stake for back bets) as stake for this spreadsheet, because that's what you actually risked (for example, if market gets suspended, which happens not so rarely, you could not trade out, so you risk entire exposure), and odds as ratio between that maximum exposure and profit (through equation from my previous post). That would work if you green up; if you red up, then stake would have to be the actual loss, and odds... errrrm, I don't know...

Though, I'm puzzled: you say that you greened up by laying at 1.47 and backing at 1.12... Did you make a typing mistake, or what? Even your spreadsheet says that you made loss of 6.25, so you didn't green up, you redded up?

[louboutin1]

Hi, Louboutin, you welcome!

Now, your additional question is quite different than the orginal one, and to be honest, I do not know the answer.

If there was no commission, you could just enter them as two separate bets. But, as you know, commission is calculated on net profit, so it would not be correct. Maybe you could take your maximum exposure at any moment (liability for lay bets, stake for back bets) as stake for this spreadsheet, because that's what you actually risked (for example, if market gets suspended, which happens not so rarely, you could not trade out, so you risk entire exposure), and odds as ratio between that maximum exposure and profit (through equation from my previous post). That would work if you green up; if you red up, then stake would have to be the actual loss, and odds... errrrm, I don't know...

Though, I'm puzzled: you say that you greened up by laying at 1.47 and backing at 1.12... Did you make a typing mistake, or what? Even your spreadsheet says that you made loss of 6.25, so you didn't green up, you redded up?

Hi, and thanks again for you answer! I guess keeping records like this is a bit too time consuming and abit too difficult when it comes to lay bets..specially LTDs become a complete nightmare...May I ask how you keep track of your history yourself..?
And yes, I guess I redded up sorry, didnt really pay attention as it was a super old bet, one of my first so probably a mistake (also note that i was betting in my own currency which is the euro divided by 10) so was just trying out
Anyways, thank you as always..!

[doubleback]

Check out this thread ...

http://www.geekstoy.com/forum/showth...analytics-tool

[louboutin1]

Check out this thread ...

http://www.geekstoy.com/forum/showth...analytics-tool

Wow, thank you for sharing, pretty much spot-on what I wanted!
Lets just hope the site wont go down any time soon...A stand-alone program type of thing would be amazing, but this works wonders while it works
THanks again!