Help needed with Excel.

[Interesting Ian]

At halftime, assuming there's no bias for scoring in one half or the other (maybe not a good assumption, but you're the one with the tables, so you can check),

Actually I think I remember reading somewhere that on average only 45% of goals are scored in the first half, and 55% in the 2nd half.

the current expectation is indeed a Poisson distribution with half the parameter of the initial one. But (important point) we can't get the numbers for this new distribution by rescaling the numbers from the old one to half. This is because there's more to the distribution that just the average. When you rescale to half, this has the effect of multiplying the average by half, just as you want. Unfortunately, it only has the effect of multiplying the standard deviation by (1/sqrt(2)). (Trust me on this one). So to get the correct result, you actually need to recalculate the Poisson distribution from scratch using the new parameter.

Sorry you've completely lost me.

 

[Interesting Ian]

The Goal Seek feature might be applicable, no pun intended.

I've just been reading up on it.

I need to automate it though i.e not have to go to tools and specify the set cell and changing cell, and then pressing ok when it's found the solution. I shouldn't have to do that since the cells are always the same.

Any ideas anyone?

 

[Art Vandelay]

I need to automate it though i.e not have to go to tools and specify the set cell and changing cell, and then pressing ok when it's found the solution. I shouldn't have to do that since the cells are always the same.

You could set a macro. Or figure out the inverse function.

A random number! I think not! It will be one of these pseudo-random numbers which mathematicians like to call "random". Quite the opposite to real random numbers!

"Real random numbers" are purely theoretical, anyway.

So I'm not competing against professionals generally. Indeed I'm getting the pre-match goal expectations from the bookies themselves (the spread firms).

You don't think there are any professionals betting on this system? The efficient market hypothesis surely applies.

Another question. Is it possible to keep changing a value in a cell so that after every 5 minute interval after a specific time that I can specify, the cell will display the value of a succession of differing cells?

You mean, the value of the cell depends on the time? As I said, there's a now() function, but it only updates if you do something with the spreadsheet. If you type =now() into a cell, you'll see the date and time. If you just walk off and do nothing to the spreadsheet, you'll still have the date and time of when you first entered it. If you edit the spreadsheet, however, the cell will change.

 

[Interesting Ian]

II
So I'm not competing against professionals generally. Indeed I'm getting the pre-match goal expectations from the bookies themselves (the spread firms).

Art Vandelay
You don't think there are any professionals betting on this system? The efficient market hypothesis surely applies.

No I don't think that. To a large measure the professionals determine the odds.

I'm not sure what you're saying. Are you suggesting that no one can win any money despite there only being a 1% commission rate? If so that's absurd. Of course you can.

I buy and sell bets. In order to get value it's unlikely I'll be buying from professionals or selling to professionals.

 

[Jarom]

You don't think there are any professionals betting on this system? The efficient market hypothesis surely applies.

If the market were efficient, no bets would be placed. Each bet placed is evidence against betfair being an efficient market.

For each bet placed, at least one side must be slightly mistaken in their determination of the probability. If Ian can consistently identify people who are mistaken by more than 1% (harder than it sounds), then he'll make money on average.

 

[Interesting Ian]

If the market were efficient, no bets would be placed. Each bet placed is evidence against betfair being an efficient market.

For each bet placed, at least one side must be slightly mistaken in their determination of the probability. If Ian can consistently identify people who are mistaken by more than 1% (harder than it sounds), then he'll make money on average.

I have made money on average, and that's when the commission is 5%. That's mainly in the "correct scores market". Not much though. £183.33 since I first made a bet on Betfair a year ago.

 

[Jeff Corey]

I have made money on average, and that's when the commission is 5%. That's mainly in the "correct scores market". Not much though. £183.33 since I first made a bet on Betfair a year ago.

it's trew

 

[Art Vandelay]

If the market were efficient, no bets would be placed. Each bet placed is evidence against betfair being an efficient market.

According to that logic, there should be no trading on a stock market. The efficient market hypothesis simply says that people cannot make money, on average, not that they can't lose money. The mere fact that

For each bet placed, at least one side must be slightly mistaken in their determination of the probability.

You are mistaken on two counts. First, there is no need for either side to be mistaken. Surely you don't think that everyone who bets on roulette actually thinks the probabilities are in their favor? They are willing to accept a negative expectation value as part of gambling. Secondly, the efficient market hypothesis says that the market as a whole has all available information, not that every every participant in the market does.

If Ian can consistently identify people who are mistaken by more than 1% (harder than it sounds), then he'll make money on average.

It is not enough merely to identify them. There must in addition be a lack of other people identifying them; if two people both identify someone who are mistaken, they will bid the profit away.

I'm not sure what you're saying. Are you suggesting that no one can win any money despite there only being a 1% commission rate? If so that's absurd. Of course you can.

Unless you're really good, you're not going to have a positive expectation value. If you're making money, it's most likely due to luck.

I buy and sell bets. In order to get value it's unlikely I'll be buying from professionals or selling to professionals.

It's irrelevant whether you are actually buying from or selling to professionals. As long as they're part of the market, they'll set the price.

 

[Interesting Ian]

Originally Posted by Jarom :

If Ian can consistently identify people who are mistaken by more than 1% (harder than it sounds), then he'll make money on average.

Art Vandelay
It is not enough merely to identify them. There must in addition be a lack of other people identifying them; if two people both identify someone who are mistaken, they will bid the profit away.


Originally Posted by Interesting Ian :
I'm not sure what you're saying. Are you suggesting that no one can win any money despite there only being a 1% commission rate? If so that's absurd. Of course you can.

Art Vandelay
Unless you're really good, you're not going to have a positive expectation value. If you're making money, it's most likely due to luck.

II
I buy and sell bets. In order to get value it's unlikely I'll be buying from professionals or selling to professionals.

Art Vandelay
It's irrelevant whether you are actually buying from or selling to professionals. As long as they're part of the market, they'll set the price..

I don't think you understand how this works. Let's consider 2 bets I made last night and which were both matched.


Fixtures 17 April / Chelsea v Everton / Total Goals 7 goals or more Back 17-Apr-06
01:07 63.89 2.00 63.89 17-Apr-06 01:15

7 goals or more Lay 17-Apr-06
03:35 41.8 2.00 41.8 17-Apr-06 09:56

For the Chelsea v Everton match today I tried to buy a bet: namely for 7 goals or more with a stake of £2 at odds of 63.89. According to my calculations the odds exceed the true odds hence it being worthwhile if someone should buy my bet. And someone did buy my bet!

But even if I am correct and I got good odds the match will be very unlikely to result in 7 goals or more.

So after somebody bought the bet I tried to sell it. But of course I wished to sell it at lower odds than I bought it. Just before I went to bed last night I tried to sell the bet at odds of 41.8. And somebody bought it this morning! (at 9.56am looking at the figures).

This means that I do not lose any money, nor gain any money if there are 1 to 6 goals. But if there are 7 goals I win the difference between the 2 bets i.e (£2*63.89) - (£2*41.8)

The important thing to bear in mind here is that the person who sold at odds of 63.89 and the one who bought at odds of 41.8 (presumably not the same person!) don't have to have much of a clue as to the true odds.

Of course sometimes you might buy a bet, and the next day it transpires that this particular bet is being bought and sold at higher odds (rather than lower). It's all a question of knowing which are value bets and/or which way the market is likely to move.

I'm just dipping my toe in at the moment and making very small bets. Just to see if I can make a profit in the long run. I've only been trading since January (ie both buying and selling bets for one particular outcome). In the previous 9 months of betting before that, where I did not trade at all, I was very slightly down.

 

[joe87]

Ian, in looking at your description of the bets you made, and not being a gambler, I'm a bit confused by the terminology; specifically, the terms "buying" and "selling" bets.

For the Chelsea v Everton match today I tried to buy a bet: namely for 7 goals or more with a stake of £2 at odds of 63.89. According to my calculations the odds exceed the true odds hence it being worthwhile if someone should buy my bet. And someone did buy my bet!

Here you say you tried to buy a bet, but when the bet was accepted, you say someone bought your bet. That appears to be a contradiction. If he bought, didn't you sell? When a bet is accepted between two participants, how is it determined which one does the buying and which does the selling? I gather from subsequent comments that this first bet would pay you £63.89 if more than 6 goals were scored, and you would lose £2 if 6 or less goals were scored, correct?

But even if I am correct and I got good odds the match will be very unlikely to result in 7 goals or more.

Which means that it is very likely that you would lose £2 on this particular bet.

So after somebody bought the bet I tried to sell it. But of course I wished to sell it at lower odds than I bought it. Just before I went to bed last night I tried to sell the bet at odds of 41.8. And somebody bought it this morning! (at 9.56am looking at the figures).

This means that I do not lose any money, nor gain any money if there are 1 to 6 goals. But if there are 7 goals I win the difference between the 2 bets i.e (£2*63.89) - (£2*41.8)

This sounds like a good idea, lock in a high probability of breaking even with the small probablilty of a big gain. However, if the odds move away from you, you can't sell the bet at a profit and you are stuck with only the initial bet that is very likely going to cost you £2.

So it seems that in order to have a positive expectation, you must be able to predict which way the odds will move more than half the time. If you bet at random, the odds will move against you half the time, and you will not have a positive expectation. This is like buying and selling any stock or option or future in the financial markets. If you can correctly guess which way the price will move more than half the time, you can buy low and sell high and make money.

But we know this is very difficult in most liquid markets because they are generally "efficient." In a truly efficient market no strategy can have a positive expectation due to the small but non-negligible cost of doing transactions (here, your transaction cost is 1%).

So is seems to me that your hope is that the market you are trading is inefficient, and that your ability to trade profitably is better than that of the average participant. Your "odds" market is probably less efficient than most stock & bond markets, due to the lower volume of trading, so you have a better chance of being able to be profitable. However, inefficient markets tend to become more efficient with time, so don't expect strategies that are initially profitable to last forever, they usually are traded away as soon as people find out about them. In any case, good luck, come back in a year and let us know how your strategy worked out.

 

[Interesting Ian]

Ian, in looking at your description of the bets you made, and not being a gambler, I'm a bit confused by the terminology; specifically, the terms "buying" and "selling" bets.


II
For the Chelsea v Everton match today I tried to buy a bet: namely for 7 goals or more with a stake of £2 at odds of 63.89. According to my calculations the odds exceed the true odds hence it being worthwhile if someone should buy my bet. And someone did buy my bet!

joe87
Here you say you tried to buy a bet, but when the bet was accepted, you say someone bought your bet. That appears to be a contradiction. If he bought, didn't you sell?

I should have said accept my bet. I put up an offer. Thus I put up an unmatched bet which tells everyone I want to buy a bet at 63.89 for 7 or more goals. And someone can accept (i.e match) that bet.

When a bet is accepted between two participants, how is it determined which one does the buying and which does the selling? I gather from subsequent comments that this first bet would pay you £63.89 if more than 6 goals were scored, and you would lose £2 if 6 or less goals were scored, correct?

No. These are decimal odds. What I would get back is £2 * 63.89 = £127.78 But that includes the stake which is £2. So (ignoring commission) I would win £125.78. But I pay 1% commission in this market so that's £1.26, so I would only have won £124.18.

Take a look at the Betfair site to see how it works. You don't know the people who you buy and sell bets from.

II
But even if I am correct and I got good odds the match will be very unlikely to result in 7 goals or more.

joe87
Which means that it is very likely that you would lose £2 on this particular bet.

Well yes. If you have odds of 60 odd then it is highly likely you will lose. It would have to be odds of less than 2 for a win to become more probable than a loss! But of course you would win vastly less (less than £2 for a £2 stake).

II
So after somebody bought the bet I tried to sell it. But of course I wished to sell it at lower odds than I bought it. Just before I went to bed last night I tried to sell the bet at odds of 41.8. And somebody bought it this morning! (at 9.56am looking at the figures).

This means that I do not lose any money, nor gain any money if there are 1 to 6 goals. But if there are 7 goals I win the difference between the 2 bets i.e (£2*63.89) - (£2*41.8)

joe87
This sounds like a good idea, lock in a high probability of breaking even with the small probablilty of a big gain. However, if the odds move away from you, you can't sell the bet at a profit and you are stuck with only the initial bet that is very likely going to cost you £2.

The odds move in my favour more often than they don't. You have to know what you're doing. But even if the odds didn't move the right way that doesn't mean to say that my bet was bad value. If the true odds for 7 goals or more is only 55 for that particular match, but I got odds of 60 odd, then in the long run I will be up if I persistently make such value bets.

So it seems that in order to have a positive expectation, you must be able to predict which way the odds will move more than half the time. If you bet at random, the odds will move against you half the time, and you will not have a positive expectation. This is like buying and selling any stock or option or future in the financial markets. If you can correctly guess which way the price will move more than half the time, you can buy low and sell high and make money.

It's more difficult apparently to make money from trading bets than playing about on financial markets. 90% of betfair users are overall losers. (and people should bear that in mind if you're considering betting on there. Gambling can also be extremely addictive. Some people lose absolutely everything. Their house their wife, their job, their life).

But we know this is very difficult in most liquid markets because they are generally "efficient." In a truly efficient market no strategy can have a positive expectation due to the small but non-negligible cost of doing transactions (here, your transaction cost is 1%).

I don't know what an "efficient market" is. But if you get people buying and selling bad value bets (and you do), then there is potential to make money.

However, inefficient markets tend to become more efficient with time, so don't expect strategies that are initially profitable to last forever, they usually are traded away as soon as people find out about them. In any case, good luck, come back in a year and let us know how your strategy worked out.

Certainly. About £250 up since January 1st so far.

 

[Art Vandelay]

So is seems to me that your hope is that the market you are trading is inefficient, and that your ability to trade profitably is better than that of the average participant.

As I said before, it's not enough merely to be better than the average participant. The average participants will tend to cancel each other out; some will overvalue a bet, while others will undervalue it. Professionals, however, will generally move the market price towards the true value, and therefore will have a disproportionate effect on the price. It's quite possible to have 90% of the participants be complete idiots and there still be an efficient market.

Note to II:
"Efficient market:
A market in which security prices reflect all available information and adjust instantly to any new information."

 

[T'ai Chi]

Ian, what, in words, are your x and y ?

 

[Interesting Ian]

As I said before, it's not enough merely to be better than the average participant. The average participants will tend to cancel each other out; some will overvalue a bet, while others will undervalue it. Professionals, however, will generally move the market price towards the true value, and therefore will have a disproportionate effect on the price.
It's quite possible to have 90% of the participants be complete idiots and there still be an efficient market.

Note to II:
"Efficient market:
A market in which security prices reflect all available information and adjust instantly to any new information."

A meaningless string of words.

What is your precise disagreement? Are you still maintaining that one cannot possibly win in the long term?

 

[Interesting Ian]

Ian, what, in words, are your x and y ?

Well it's not really relevant any more. With the help of Jarom everything is now sorted.

 

[joe87]

A meaningless string of words.

Only meaningless if you haven't studied market theory. Here's some discussion of the eficient market hypothesis from www<dot>investorhome<dot>com/emh<dot>htm:

An issue that is the subject of intense debate among academics and financial professionals is the Efficient Market Hypothesis (EMH). The Efficient Market Hypothesis states that at any given time, security prices fully reflect all available information. The implications of the efficient market hypothesis are truly profound. Most individuals that buy and sell securities (stocks in particular), do so under the assumption that the securities they are buying are worth more than the price that they are paying, while securities that they are selling are worth less than the selling price. But if markets are efficient and current prices fully reflect all information, then buying and selling securities in an attempt to outperform the market will effectively be a game of chance rather than skill.

What is your precise disagreement? Are you still maintaining that one cannot possibly win in the long term?

I agree with Art that if the market you are trading is truly efficient, then you cannot win in the long run. That's why I said that you have a chance to win if your betting market is inefficient, which it probably is compared to the financial markets. In addition to trading an inefficient market, in order to be profitable long-term, you must also devise (or somehow find) a strategy to take advantage of the inefficiencies.

 

[Interesting Ian]

Only meaningless if you haven't studied market theory. Here's some discussion of the eficient market hypothesis from www<dot>investorhome<dot>com/emh<dot>htm:


I agree with Art that if the market you are trading is truly efficient, then you cannot win in the long run. That's why I said that you have a chance to win if your betting market is inefficient, which it probably is compared to the financial markets. In addition to trading an inefficient market, in order to be profitable long-term, you must also devise (or somehow find) a strategy to take advantage of the inefficiencies.

How can the current prices possibly reflect all information?? Take the case where you are betting when the match is already being played. By what information do people asses the correct probabilities?? By watching the match and going by their feelings generally speaking, that's how! And when it comes to long shots people tend to think that outcomes are much more probable than they actually are.

In the heat of the match people tend to make instantaneous decisions. They are certainly not placing their bets by having access to all possible information!

 

[joe87]

How can the current prices possibly reflect all information?? Take the case where you are betting when the match is already being played. By what information do people asses the correct probabilities?? By watching the match and going by their feelings generally speaking, that's how! And when it comes to long shots people tend to think that outcomes are much more probable than they actually are.

In the heat of the match people tend to make instantaneous decisions. They are certainly not placing their bets by having access to all possible information!

In order for the efficient market hypothesis to work, it isn't necessary that all bettors have all knowable information, only that some of them do. Guys like you, for example, who are betting for profit rather than for entertainment (I assume that you are happy to bet against your favorite team if the odds are right). It won't take very many people playing your game to dry up the opportunities for you. That's known as arbitraging away inefficiencies, and it tends to happen in all markets, including your betting market. If you have a very profitable betting scheme, you will tend to bet it more and that by itself will reduce your opportunities. There are only so many people out there who will take a bad bet. So if you find a good strategy, don't let others know about it or they will use it and it will become less profitable.

On a separate issue, you say that most bettors think that unlikely outcomes are more likely than they actually are. Have you tested that hypothesis? It should be easy to test if the data are available. If you have the scores of the most recent, say 3000 matches of the league that you are betting on, you can count how many times 7 or more goals were scored. If that number is 30, for example, then the odds are 100 to 1 on that outcome. If it happened 150 times, then the odds are 20 to 1. This assumes that the next 3000 matches will have about the same outcome as the past 3000 matches, in terms of number of goals scored.

And you can futher refine the odds if you find that some teams tend to score more goals than others, so the odds shift if two high-scoring teams are playing each other. But you can carry this analysis too far, if you don't have enough overall data. If an outcome has happened only a few times, your confidence in the odds on it happening is poor. For example, if team A and team B have played each other 30 times, and the number of goals exceeded 7 once, you really can't make any legitimate conclusion from that data. This sort of analysis is called "data mining," and won't help your betting performance.

But if you don't have any historical data, only your gut feeling based on having followed and bet on the league you are following, then your conclusion about the likelihood of 7 or more goals being scored is not grounded in analysis, and is likely to be biased.

 

[Soapy Sam]

Just checking. Ignore me.

 

[Soapy Sam]

Just checking. Ignore me.

Lest any be confused by that, I tried pasting some Excel data in, then erased it. Ian's right of course, the numbers are preserved but the formatting is lost. Same holds if you paste from a comma separated file.