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

Welcome to Live View – Take the tour to learn more
Start Tour
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.
Pause • Switch to Standard View What's the most efficient way of...
Show More
Loading...
Report Mr Magoo • October 11, 2011 11:31 AM BST
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.
Report DivideByZeroError • October 11, 2011 11:50 AM BST
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.
Report Mr Magoo • October 11, 2011 1:03 PM BST
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)
Report Contrarian • October 11, 2011 1:34 PM BST
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.
Report ZEALOT • October 11, 2011 2:19 PM BST
See what we're up against fellas ??

Some real bright sparks on here
Report ZEALOT • October 11, 2011 2:19 PM BST
See what we're up against fellas ??

Some real bright sparks on here
Report Mr Magoo • October 11, 2011 2:27 PM BST
Surely the initial import doesn't really matter that much? You only have to do it once per week...
Report Contrarian • October 11, 2011 2:30 PM BST
Yes, but starting from scratch, I have to import hundreds of files, if I'm to cover a few years' worth of data.
Report Mr Magoo • October 11, 2011 2:55 PM BST
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.
Report DivideByZeroError • October 11, 2011 3:00 PM BST
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?
Report DivideByZeroError • October 11, 2011 3:02 PM BST
This should probably be on the BDP forum which is sadly lacking in activity.
Report JPL66 • October 11, 2011 10:33 PM BST
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.
Report Contrarian • October 12, 2011 9:19 AM BST
Thanks JPL66.
Report Beat The OverRound • October 13, 2011 6:22 AM BST
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.
Report Mr Magoo • October 13, 2011 9:54 AM BST
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?
Report bf_fananatic • October 13, 2011 1:07 PM BST
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?
Report bf_fananatic • October 13, 2011 1:08 PM BST
*eror=error lol[;)]
Report Contrarian • October 13, 2011 1:15 PM BST
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.
Report bf_fananatic • October 13, 2011 1:24 PM BST
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
Report Contrarian • October 13, 2011 1:38 PM BST
40 million pieces of form data

How are you quantifying 'data' here exactly? How big are the relevant Excel files?
Report bf_fananatic • October 13, 2011 1:59 PM BST
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.
Report bf_fananatic • October 13, 2011 2:02 PM BST
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 [;)]
Report pumpkinslayer2 • October 13, 2011 2:03 PM BST
I write my own software. Far more flexability in how you querie the database and much easier to handle large volumes of data.
Report bf_fananatic • October 13, 2011 2:10 PM BST
How many events pumkinslayer can you queiry at one pc memory load over how many fields?
Report bf_fananatic • October 13, 2011 2:14 PM BST
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.
Report bf_fananatic • October 13, 2011 2:19 PM BST
I can queiry 14,000 field per 1600 race, which is over 20 million comparisons in virtual memory/hard memory 6 gig format!
Report pumpkinslayer2 • October 13, 2011 2:21 PM BST
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.
Report bf_fananatic • October 13, 2011 2:21 PM BST
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.[;)]
Report bf_fananatic • October 13, 2011 2:23 PM BST
Reckon we will need super computers soon pumpkinslayer, give it another ten years and we will have them in the home too[;)]
Report pumpkinslayer2 • October 13, 2011 2:25 PM BST
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.
Report bf_fananatic • October 13, 2011 2:31 PM BST
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.
Report bf_fananatic • October 13, 2011 2:34 PM BST
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.
Report bf_fananatic • October 13, 2011 2:37 PM BST
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.
Report Contrarian • October 13, 2011 2:42 PM BST
Pumpkinslayer,

Do you mean that you have written your own database software? Ie. an alternative to mySQL, say?
Report pumpkinslayer2 • October 13, 2011 2:47 PM BST
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.
Report pumpkinslayer2 • October 13, 2011 2:48 PM BST
bf, I agree this live forum is so much better.
Report Contrarian • October 13, 2011 3:17 PM BST
Wow. Pretty impressive.
Post Your Reply
<CTRL+Enter> to submit
Please login to post a reply.

Wonder

Instance ID: 13539
www.betfair.com