Forums

We want to share an important update with our Betfair Community users.

Over the coming months, the Betfair Community platform will be winding down. The final active day for the platform will be December 21st. From December 22nd, users will no longer be able to access the platform.

Your discussions, insights, engagement, and, most importantly, feedback have helped shape the platform and guided innovation. Your feedback continues to be important to us, and we will continue to listen via our dedicated feedback channels, as well as through product testing and innovations.

To share your feedback and experience, visit: https://support.betfair.com/app/home

General Betting

There is currently 1 person viewing this thread.
Contrarian
11 Oct 11 09:57
Joined:
Date Joined: 19 May 03
| Topic/replies: 1,028 | Blogger: Contrarian's blog
Those huge Excel files are incredibly unwieldy. I'm trying to build a database of as much historical data as possible. I presume the best way to do this is to import the Excel/CSV files into a program like MySQL, and then do all the analysis there. Part of the problem with this is that the original Excel files are not totally 'clean' (eg. some rows don't contain the appropriate data), and they need to be treated before they can be imported into MySQL.

I would be grateful to hear from anyone else who has a better way of doing this.

Cheers.

Post your reply

Text Format: Table: Smilies:
Forum does not support HTML
Insert Photo
Cancel
sort by:
Show
per page
Replies: 37
By:
Mr Magoo
When: 11 Oct 11 11:31
I think it depends on what you want to do with the records, as this will determine what you use to analyse the data and so will affect the file formats that you need to use.

For my analysis I convert the original CSV files into other text files, combining the Betfair data with other records and stats. Keeping file sizes small is probably the way to make the files easier to use, e.g. I split the horse racing data into a file for each day. That way it makes it easier to look at any given time period.
By:
DivideByZeroError
When: 11 Oct 11 11:50
You can import the csv file into Access pretty easily.

In Access 2000: choose File->Get External Data->Import...

then select text files and browse to your file. Don't forget to specify that the first row contains the field names. I import every field as text (rather than integer, date, double etc.) because Access sometimes has problems converting.

You can make a database connection in VBA and use SQL statements to pull the data into whatever you currently use in Excel.
By:
Mr Magoo
When: 11 Oct 11 13:03
One way to keep things manageable and really trim down the amount of data is to throw out as much of the matched odds as possible.

If you are testing a strategy that takes a position (i.e. no trading) then you don't need to keep all of the [odds,amount matched] pairs. e.g. if you are trying to lay each runner in a market to (say) £100, just keep the minimum number of records for the lowest odds/amounts that would match £100. You'd need to do some clever filtering to throw out spurious low matches (1.01s and other random mistakes) and also be careful of the time that bets were matched (no use using bets matched days before an event if you are going to bet in the last ten minutes)
By:
Contrarian
When: 11 Oct 11 13:34
Thanks guys.

Some of what I'm doing involves reconstructing - as much as the data permits it - the in-running histories of the events, and so is quite intensive. I have found that once the data is imported into MySQL, I can access it from my C# programs really quickly and easily. What is taking ages is actually importing it into MySQL in the first place.
By:
ZEALOT
When: 11 Oct 11 14:19
See what we're up against fellas ??

Some real bright sparks on here
By:
ZEALOT
When: 11 Oct 11 14:19
See what we're up against fellas ??

Some real bright sparks on here
By:
Mr Magoo
When: 11 Oct 11 14:27
Surely the initial import doesn't really matter that much? You only have to do it once per week...
By:
Contrarian
When: 11 Oct 11 14:30
Yes, but starting from scratch, I have to import hundreds of files, if I'm to cover a few years' worth of data.
By:
Mr Magoo
When: 11 Oct 11 14:55
If it's a problem with manually importing all of the files individually, just unzip them, join them all together into one huge text file and then do the import once. Sure, it'll take ages but you can just leave the computer to get on with it.
By:
DivideByZeroError
When: 11 Oct 11 15:00
Have you tried reading the text files using StreamReader in C#, then handling the missing fields and inserting the 'cleaned' data into MySQL using the same connection that you're querying?
By:
DivideByZeroError
When: 11 Oct 11 15:02
This should probably be on the BDP forum which is sadly lacking in activity.
By:
JPL66
When: 11 Oct 11 22:33
Contrarian - one annoyance it took me a while to understand (and I share it in case it's helpful) is that the CSV files don't escape or quote commas properly.  So if the market name or runner name has a comma in it (as they sometimes do), that record will appear to have an extra field in the middle.  So to load those records you need some logic to recognise that it's happened, and recombine the two fields into one.
By:
Contrarian
When: 12 Oct 11 09:19
Thanks JPL66.
By:
Beat The OverRound
When: 13 Oct 11 06:22
I have an access macro whch imports all csv files in a folder, makes it easier with bulk imports.

However, you must have some form of data checking in place, there is a lot of irrelevant data (markets) and some wrong data, and deadheats make a big difference.
For aussie markets they don't include the apostrophe in a horse's name.

Another point if you're looking to resimulate live data (which I tried) is that not all data is there, a comparison between Betfair and Fracsoft showed huge chunks of data missing or skipped.
By:
Mr Magoo
When: 13 Oct 11 09:54
Can you give any examples of missing data?

I know the historic data only records the first & last time for when some odds were matched, so you can't accurately chart the changing odds, but are you saying that there are also missing records of matched odds?
By:
bf_fananatic
When: 13 Oct 11 13:07
Contrarian, It would be possible to create in excel alone a workbook or books to allow you to view the data in any form you wish and too correct any erors in the source formatting but it takes time and
any programmer would want to offer you a fee for such a project?
By:
bf_fananatic
When: 13 Oct 11 13:08
*eror=error lol[;)]
By:
Contrarian
When: 13 Oct 11 13:15
bf_fananatic,

These Excel files have over 500,000 rows! Do you really think it is possible to analyse that sort of data within Excel without the whole program grinding to a halt?

As I said above, I am able to do everything I want very quickly by accessing mySQL from my C# programs. The problem is that it takes ages to import (several years' worth of) Excel files in the first place.
By:
bf_fananatic
When: 13 Oct 11 13:24
Well Contrarian I manage to use excel via running multiple instances and linking via orthodox/non-orthodox methods too have 40 million pieces of form data and god knows how many actual cells involved and the whole thing doesnt grind too a halt at all, I could resort to a programming language but prefer to keep things as they are, my point is you could easily use excel to clean up the source data for you before importing into your favourite databasing or programming appBlush
By:
Contrarian
When: 13 Oct 11 13:38
40 million pieces of form data

How are you quantifying 'data' here exactly? How big are the relevant Excel files?
By:
bf_fananatic
When: 13 Oct 11 13:59
Well one greyhound race has around 600 data elements at 5 race history level, times by 400, times by 4
equals 960 000 form data, multiplied by 16 top tracks(forget tracks with less than 2 meeting per week)
then multiply by many groups of above and you arrive at a 40 million plus for form data, but if you cosider I have at any one time at max pc load 3 gig in workbooks then arrive at the actual amount of "load" it confirms that the pc dosent grind to a halt if having huge workbooks

I would not recommend this method to anyone though as its hugely problematic and compiling the data into a more effiecnt format is much easier when handled with a programming language, I can justify your approach and that of others as being the more correct, but still its nice to see how far excel can go and its pretty impressive.
By:
bf_fananatic
When: 13 Oct 11 14:02
When I designed by first greyhound program I had to switch bits on and off to save memory as you dont get much resources on a 4 meg amiga [;)]
By:
pumpkinslayer2
When: 13 Oct 11 14:03
I write my own software. Far more flexability in how you querie the database and much easier to handle large volumes of data.
By:
bf_fananatic
When: 13 Oct 11 14:10
How many events pumkinslayer can you queiry at one pc memory load over how many fields?
By:
bf_fananatic
When: 13 Oct 11 14:14
I can write programs from basic down too machine code, though its all c# generally now as 64 bit programming is a nightmare and uneconomic.
By:
bf_fananatic
When: 13 Oct 11 14:19
I can queiry 14,000 field per 1600 race, which is over 20 million comparisons in virtual memory/hard memory 6 gig format!
By:
pumpkinslayer2
When: 13 Oct 11 14:21
That depends on what data I require. I save each data element in its own compressed, e.g horse ID, age, weight carried are stored in seperate files for each year has a seperate file. I then just read in only what I require. If thats still to big I query a year at a time and add the results together.
By:
bf_fananatic
When: 13 Oct 11 14:21
Excel may be cosidered a dinosaur compared to a programming language but dinosaurs could grow pretty big and move very fast with a bit of evolution.[;)]
By:
bf_fananatic
When: 13 Oct 11 14:23
Reckon we will need super computers soon pumpkinslayer, give it another ten years and we will have them in the home too[;)]
By:
pumpkinslayer2
When: 13 Oct 11 14:25
That will come, we already get quadcore, it's only a matter of time before parallel processing is available for pcs and the number of processors will steadily rise.
By:
bf_fananatic
When: 13 Oct 11 14:31
The biggest problems with data are the platform migration type as mentioned by contrarian with this post, this promlem is only ever solved by maverick programmers as there are no rules with formatting data output generally and those displayed on websites are typically variant and also full of errors.
By:
bf_fananatic
When: 13 Oct 11 14:34
I believe I read it somewhere that macs can have as many cores as you require if ordered, this may be an exaggereation but it validates your last statement pks.
By:
bf_fananatic
When: 13 Oct 11 14:37
This live view is great that BF have implemented but I could end up stuck on the forum for all eterenity with my persistance level of activity, yikes.
By:
Contrarian
When: 13 Oct 11 14:42
Pumpkinslayer,

Do you mean that you have written your own database software? Ie. an alternative to mySQL, say?
By:
pumpkinslayer2
When: 13 Oct 11 14:47
It has no pretty front end and runs from the command prompt, but yes. It's nothing clever, just code that reads in datafiles and queires them according to whetever I'm interested in. You could do this using any simple language. I use fortran, it's old but pretty go at that sort of thing. And I have a piece of code that I commissioned that downloads full results from betfair.
By:
pumpkinslayer2
When: 13 Oct 11 14:48
bf, I agree this live forum is so much better.
By:
Contrarian
When: 13 Oct 11 15:17
Wow. Pretty impressive.
sort by:
Show
per page

Post your reply

Text Format: Table: Smilies:
Forum does not support HTML
Insert Photo
Cancel
‹ back to topics
www.betfair.com