Help needed with Excel.

[Lothian]

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.

What about the [table] tag

 

[Art Vandelay]

You can also turn it into an image file, and then post it. Does suck up bandwidth, though.

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.

It's not even necessary that any one person has all the information. That's really the core of the EMH: it says that a market in effect acts as a collective consciousness that is capable of combining disparate pieces of information into a coherent whole. That was the idea behind the ill-fated terrorism betting market.

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.

But your gut feeling may very well be based on subconscious analysis. And lots of people, all betting their gut, can be much more accurate than one.

 

[Jarom]

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

I understand what an efficient market is. As per the definition you quoted, for betfair.com to be an efficient market would mean that you believe both of the following:

"The prices of bets at betfair.com reflect all available information."
"The prices of bets at betfair.com adjust instantly to any new information."

It seems evident to me that these are untrue statements. In particular, if the second one were, then if a (pregame) bet on a particular team to win is trading at 1-5, then is trading at 1-6 just five minutes later, it follows that at some time during those five minutes, information became available to the market showing that the team was actually precisely 14.258...% (not approximately!) less likely to win than previously thought.

This seems to be an untenable position. However, I may have misunderstood you. Do you in fact claim that betfair.com is an efficient market?

 

[Interesting Ian]

I was thinking I really ought to get sky sports since I only have the 5 main channels and hence I can watch virtually none of the matches. Since I am betting during the matches themselves in the "correct score" "total goals" and "who will score the next goal" markets, and the people I am betting against most probably certainly are watching the matches, this conveys some disadvantage to me (despite the fact I'm still making very slightly more money than I'm losing).

However! Just last night I discovered this site:

https://cantor.spreadfair.com/

Similar to betfair in the sense it's p2p betting. But people bet on spreads here. Thus for example they pick the number of goals they reckon will be in the match eg 2.78 and win or lose depending on the actually number of goals scored and how much below or above it is from the figure bet on (needless to say the actual number of goals scored will of course be a whole number).

They also bet during the match so now I can see how the total goals expectation varies as the match progresses. (which might well vary from my pre-match calculations). And the goal superiority is also listed which is even more useful!

Watch the money roll in now!

 

[Interesting Ian]

I understand what an efficient market is. As per the definition you quoted, for betfair.com to be an efficient market would mean that you believe both of the following:

"The prices of bets at betfair.com reflect all available information."
"The prices of bets at betfair.com adjust instantly to any new information."

It seems evident to me that these are untrue statements. In particular, if the second one were, then if a (pregame) bet on a particular team to win is trading at 1-5, then is trading at 1-6 just five minutes later, it follows that at some time during those five minutes, information became available to the market showing that the team was actually precisely 14.258...% (not approximately!) less likely to win than previously thought.

This seems to be an untenable position. However, I may have misunderstood you. Do you in fact claim that betfair.com is an efficient market?

Yes what Art and Joe are saying is simply not applicable. The odds normally tend to waltz all around the place. However it is possible to discern certain patterns. I don't wish to give too may clues , but just to give one example, in the correct scores market when a powerful team plays against a weaker team, the odds for the scoreline 2--0 to the more powerful team will tend to shorten significantly from say 2 days beforehand, to the last few minutes before the start of the match. But certainly not always; more as a general tendency. This happens not because there is new information, but because people simply fancy this particular outcome. A match is about to start, many punters think to themselves "oh I fancy team A to beat team B by 2--0" and they put there money on even though the price for this scoreline just before the match beforehand represents very bad value. People wanting to bet on this scoreline forces the price down you see.

To everyone:

I'm certainly not recommending people gamble on betfair. As I said, 90% of all people lose. You really have to know what you're doing to make a profit. You have to do all the necessary research, do the calculations, never bet on impulse just for the fun of it etc.

And it's 5% commission rate people pay, not 1% in virtually all markets. That tends to drag your money down much more significantly than you might think. (that's why I was thinking of betting on the total goals market where it is only 1%. Previous to that I was only betting in the correct scores market where one has to pay 5% commission. On the other hand value bets are slightly more common in the correct scores market).

 

[Art Vandelay]

It seems evident to me that these are untrue statements. In particular, if the second one were, then if a (pregame) bet on a particular team to win is trading at 1-5, then is trading at 1-6 just five minutes later, it follows that at some time during those five minutes, information became available to the market showing that the team was actually precisely 14.258...% (not approximately!) less likely to win than previously thought.

Obviously, it's an apporximation. If there are no values between 1-5 and 1-6, then it is fallacious to say that the probability is "precisely" 14.258...% less. Furthermore, the commission also interferes with arbitrage.

However, I may have misunderstood you. Do you in fact claim that betfair.com is an efficient market?

I think that thinking it's not is rather dangerous.

 

[hodgy]

Originally Posted by Interesting Ian :
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?

Do you still need the answer to this?

 

[Jarom]

Obviously, it's an apporximation. If there are no values between 1-5 and 1-6, then it is fallacious to say that the probability is "precisely" 14.258...% less.

This is true, sorry. However, there are odds available such as 3-7, so going from 1-5 to 1-6 would indicate (if the market were efficient) that information made available to the market in the last five minutes dropped the team's actual chances to win by more than 10%. The absurdity is hardly lessened.

I think that thinking it's not is rather dangerous.

Unclear. Do you mean: "It is an efficient market, so believing falsely that it's not is rather dangerous", or "It is not an efficient market, but correctly recognizing this is rather dangerous", or something else?

 

[Interesting Ian]

Do you still need the answer to this?

Well yes, it would certainly be useful.

 

[Interesting Ian]

Unclear. Do you mean: "It is an efficient market, so believing falsely that it's not is rather dangerous", or "It is not an efficient market, but correctly recognizing this is rather dangerous", or something else?

I think he's saying it might be dangerous, therefore assuming it's not could lose you a lot of money.

So a little bit of back-peddling here.

 

[joe87]

I think he's saying that thinking you have an edge when you don't can be dangerous. If betfair.com is an efficient market, by definition you don't and can't have an edge. If you nevertheless assume that you do have an edge, and bet aggressively to take advantage of your assumed edge, you will likely lose a lot of money.

So it's important to get an accurate determination of whether your presumed edge would have worked in the past by backtesting your strategy against historical data. If you don't do that, you are shooting in the dark, and may wind up as one of the 90% who lose money betting.

As far as whether betfair.com is an efficient market or not, I don't have a clue. But my experience with a number of other markets is, any liquid market tends to be efficient, i.e., difficult to consistently profit from. There are many clever bettors/traders out there who are quite willing to take advantage of the losers, and that tends to make inefficiencies disappear quickly.

 

[joe87]

In the above post I suggested backtesting. Backtesting allows you to try a number of strategies in search of profitable systems, and tune your betting parameters to optimize your system. However, it may not be easy or even possible to collect data on how the betfair market odds change during a game, and if that's the heart of your system, then backtesting is not an option. In that case, you are reduced to trying each system you think might work, to demonstrate "proof by profit."

Originally Posted by Interesting Ian :
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?

Do you mean here that you would like a specific cell, say A5, to display the value in, say B5 for 5 minutes, then C5 for the next 5 minutes, then D5 etc. etc.? Where the 5 minutes can be whatever is specified in cell A2, for example? This can be easily done with a macro, but you will have to learn how to create and use macros.

When I was trading stocks, I set up an excel spreadsheet using macros and the web query function to read specific internet pages on a recurring basis during the trading day, say once a minute, and bring back the current price of specific stocks of interest from those pages back into my spreadsheet. Then the spreadsheet used a model I has set up (and backtested) to determine if the stock met my buy/sell criteria, and all stocks that were "buys" had a green cell by their name and the "sell" stocks had a red cell by their name. Excel can do a lot if you know how to program it. It's not quite as capable as a program written in a real language like C++, but it's a lot quicker and easier to set up than writing and debugging a new program from scratch.

 

[ClusterBoy]

A few thoughts:
1) Although one can estimate a lambda for each team for use in a Poisson distribution and then generate a bivariate distribution for the total goals in a match, it is not ideal; the odds are set using a correction for the fact that 0 or 1 goals for each team are significantly higher (extreme value theory applies i believe, or it might be a use of semi-extended zero-inflated Poisson model, not sure exactly)
2) Betfair is clearly not an efficient market. The reason the price changes is often as follows: traditional bookmakers take a bet in their shop, or over the net increasingly these days. BF is then used by the bookies to hedge their own positions in the event of an imbalance in their book. Given that betfair boasts that it almost always offers better value than the bookmaker, this is almost always possible. The price they offer is designed to give them a more balanced book not one that necessarely reflects true value. E.G.
"The prices of bets at betfair.com adjust instantly to any new information."
To a point. Depends on the information. If the information is "some half-wit has remortgaged and bet his house that Middlesborough will score 4 goals in the next hour" then it does. But it doesn't change the underlying probability of it happening
3) That said, BF is quick to react. I watched the 2004 election results on BBC in the UK, and BF as well. The presidential odds, and the odds for taking FL and OH reacted as soon as they were called for the Reps. Instantly. It took about half an hour for that news to filter through onto the BBC.
4) It is a moot point as to whether BF dictates the bookies odds or vice versa. I believe it is increasingly doing so.
5) The amount of research required, even with a strong stats background and a fanatical devotion to football, means that however much you win, given the limited liquidity of the market it isn't a sensible career option, in terms of the amount won v time invested. It always work out at way less than minimum wage
6) Ian knows exactly what he is talking about regarding BF; his trend spotting may well be spot on. His modelling probably not If it is, then there i know company who will offer him a job straight away, as they are often advertising for people like that.

 

[Art Vandelay]

In the above post I suggested backtesting. Backtesting allows you to try a number of strategies in search of profitable systems, and tune your betting parameters to optimize your system.

You do have to be careful, however. Backtesting can very easily turn into data mining and post hoc "pattern" recognition.

It's not quite as capable as a program written in a real language like C++, but it's a lot quicker and easier to set up than writing and debugging a new program from scratch.

Excel is essentially a very elaborate GUI for Visual Basic. When you create a macro, you're creating a Visual Basic subroutine; you can edit the code and write your own programs directly if you want.

Do you mean: "It is an efficient market, so believing falsely that it's not is rather dangerous", or "It is not an efficient market, but correctly recognizing this is rather dangerous", or something else?

I mean "That it is an efficient market should be the null hypothesis, discarded only after very careful consideration. If it is an efficient market, you will lose money. If it is not an efficient market, but your analysis is wrong, you will still lose money. So no matter what, you should be careful."

Or, more succinctly, "Pride goeth before a fall".

 

[Interesting Ian]

A few thoughts:
1) Although one can estimate a lambda for each team for use in a Poisson distribution and then generate a bivariate distribution for the total goals in a match, it is not ideal; the odds are set using a correction for the fact that 0 or 1 goals for each team are significantly higher (extreme value theory applies i believe, or it might be a use of semi-extended zero-inflated Poisson model, not sure exactly)

Where are you getting this from? I've looked at approximately 24,000 matches. The data simply does not vindicate your claim.

 

[Interesting Ian]

Do you mean here that you would like a specific cell, say A5, to display the value in, say B5 for 5 minutes, then C5 for the next 5 minutes, then D5 etc. etc.? Where the 5 minutes can be whatever is specified in cell A2, for example?

yes

When I was trading stocks, I set up an excel spreadsheet using macros and the web query function to read specific internet pages on a recurring basis during the trading day, say once a minute, and bring back the current price of specific stocks of interest from those pages back into my spreadsheet.

I was messing around for ages trying to do that from Betfair; I can tell you the web query function certainly doesn't work. It does for something like http://readabet.bestbetting.com/?partner=readabet&market=21905296&origin=scr though.

Can it be done for Betfair? I don't know anything about "macros". Nor about computer programming.

 

[joe87]

I was messing around for ages trying to do that from Betfair; I can tell you the web query function certainly doesn't work. It does for something like http://readabet.bestbetting.com/?partner=readabet&market=21905296&origin=scr though.

Can it be done for Betfair? I don't know anything about "macros". Nor about computer programming.

It doesn't look easy to do from betfair. They can make it difficult to do if they want to, and perhaps they have. The readabet format looks like it was set up to be read, as the name implies. I used to have to revise my web query format when the web page that I was using changed their format, it was annoying. Web query is not a "smart" function.

To do the cell shifting thing you need to write a macro using the now() function. You could start using the Excel help function to investigatge macros. You can set up a macro by recording your keystrokes as you step through the actions you want the macro to perform. There are books at any large bookstore discussing Excel macros, and visual basic. Better yet, if you know someone who knows how to set up macros, sit down with them for a while and get them to show you how to do it. Or as a last resort, you could hire someone, although that could be expensive. It's not the kind of thing that can easily be learned from a forum like this one, especially if you have no background in programming.

 

[Interesting Ian]

A few thoughts:
1) Although one can estimate a lambda for each team for use in a Poisson distribution and then generate a bivariate distribution for the total goals in a match, it is not ideal; the odds are set using a correction for the fact that 0 or 1 goals for each team are significantly higher (extreme value theory applies i believe, or it might be a use of semi-extended zero-inflated Poisson model, not sure exactly)

You know a lot about statistics, and I know virtually nothing, so I'd just like to ask you a question if I may.

The average number of goals in a football match is 2.59 (1.51 to the home team and 1.08 to the away team). Contrary to what you say, applying a Poisson distribution using this figure gives probabilities which are bang on with real world statistics. Thus using actual match results from 24,000 matches, the proportion of games where goals are over 0, 1, 2, 3 etc mirror the prediction of a Poisson distribution using this 2.59 figure.

However would this mean that for any given specific match where, say, it is estimated that the average number of goals is 2.1, that applying a Poisson distribution to this figure will thereby reflect the true odds for the number of goals there will be in a match?


I was just thinking that it might be possible that, for example, for games with a low goals expectation the probability might be less than indicated by a poisson distribution, where as for games with a high goal expectation, it might exceed that which is suggested by a Poisson distribution. Notwithstanding this the average might nevertheless fit a Poisson distribution.

I was thinking about this before, but I thought probably not. On the other hand I know absolutely nothing about statistics.

 

[ClusterBoy]

Where are you getting this from? I've looked at approximately 24,000 matches. The data simply does not vindicate your claim.

well, i went to a symposium on the use of operational reserach in sport a few years ago, and one of the guys talking used to be a 'professional' football match gambler, before he turned gamekeeper and set up a company that sets the odds for the bookmakers. I think the problem occurs at the upper end of the spectrum, not with 0,1,2,3. Basically, if you used a Poisson distribution with lambda = 2.8, and then slightly corrected for the 0,1 values (which would be underestimated using this approach), you may well get an even better fit. And a better fit means better winnings for the bookies.
The upper end of the spectrum is important. I know of a guy who bet on Celtic to win either 5-0 or 5-1 every week, except when they played Rangers. He turned a handsome profit. This loophole has been closed so i don't mind sharing


"I was just thinking that it might be possible that, for example, for games with a low goals expectation the probability might be less than indicated by a poisson distribution, where as for games with a high goal expectation, it might exceed that which is suggested by a Poisson distribution. Notwithstanding this the average might nevertheless fit a Poisson distribution."
Entirely possible, depepnds on what you mean by exceed. And the probability of what event you are referring to. If you mean its not a good fit for either but on average it is, then yes.


Another problem with a Poisson distribution, is that the number of goals you are 'likely' to score in any one given match depepnds not only on how good your attack is, but how good your oponents defence is. And their defence is not measured by this approach. PM me if you want to discuss further. Gambling strategy is best not discussed in public

 

[Interesting Ian]

well, i went to a symposium on the use of operational reserach in sport a few years ago, and one of the guys talking used to be a 'professional' football match gambler, before he turned gamekeeper and set up a company that sets the odds for the bookmakers. I think the problem occurs at the upper end of the spectrum, not with 0,1,2,3. Basically, if you used a Poisson distribution with lambda = 2.8, and then slightly corrected for the 0,1 values (which would be underestimated using this approach), you may well get an even better fit. And a better fit means better winnings for the bookies.
The upper end of the spectrum is important. I know of a guy who bet on Celtic to win either 5-0 or 5-1 every week, except when they played Rangers. He turned a handsome profit. This loophole has been closed so i don't mind sharing

My own research has revealed that only a very slight correction is required to make a poisson distribution fit for 0 and 1 goals. However I can't now recall if I made a slight modification and constrained the possion distribution to fit the 1 x 2 real world percentages.

Moreover there is no deviation for high scoring matches from what my (possibly modified) poisson distribution reveals. It has to be said though that I can't imagine my statistics for the high scoring matches will be particularly reliable since there are not enough of such high scoring matches -- even when considering 24,000 matches! However, from the limited data I was amazed at how well they do fit.

Betting on high scoring "correct scores" represents incredibly bad value for money. You have to bet at the bookies for such scores (as Betfair only goes up to 3-3). Sometimes you only get odds of about a 100 when the real true odds are more like a 1000!

However I have no idea what it was like years ago. I only made my first ever bet a year ago so know nothing about what it was like before then. I would suggest this guy that you knew realised that in many of Celtic's matches they were scoring many goals. The bookies, on the other hand, were not doing their homework and were deriving the odds by other factors.

II
"I was just thinking that it might be possible that, for example, for games with a low goals expectation the probability might be less than indicated by a poisson distribution, where as for games with a high goal expectation, it might exceed that which is suggested by a Poisson distribution. Notwithstanding this the average might nevertheless fit a Poisson distribution."

Clusterboy
Entirely possible, depepnds on what you mean by exceed. And the probability of what event you are referring to. If you mean its not a good fit for either but on average it is, then yes.

That's very unfortunate. Much further analysis of the statistics is required (if I can get hold of them which is fairly doubtful)

Another problem with a Poisson distribution, is that the number of goals you are 'likely' to score in any one given match depepnds not only on how good your attack is, but how good your oponents defence is. And their defence is not measured by this approach.

No, this is not a problem with the poisson distribution. The pre-match goal expectation is what the poisson distribution uses. In order to estimate such a pre-match goal expectation you have to take uinto account the factors you mentioned. So you've already factored for this.

PM me if you want to discuss further. Gambling strategy is best not discussed in public

I really don't think I have any good ideas which are not common sense or couldn't easily be found on a search

However I'm very amenable to you or others contacting me to discuss all this. So should you or anyone else wish to do so I have a gmail address. The bit before the @ is "interesting.ian" (without quotes).