Best way to collect trading data

[ShaunWhite]

Can I just poll option about the best way to collect your transaction data.

The bethistory.xls from bf is ok...but the market is almost impossible to parse into useable columns.

[spreadbetting]

I dump the Betfair bettingPnL files into a database once a week to keep track of my betting yearly. The bethistory files have plenty of information , be nice if they included the marketId but other thn that I'm not sure what else they could include. Why are you having problems parsing it into useful data, the format seems fine to strip everything out.

[ShaunWhite]

I dump the Betfair bettingPnL files .... the format seems fine to strip everything out.

Thats' the file I'm using. But the issue I have is parsing the market field, there's no consistancy, even within the naming of horse racing markets. There isn't even a field to indicate 'Racing' or Football' or 'Politics' etc

eg
GB / Chelt 12th Nov / 12:40 2m Grd2 Juv Hrd
GB / Uttox 12th Nov / 12:30 2m Nov Hrd

'Hrd' is the 9th element in the first line and 8th in the second. It's even worse when you mix it in with football, politics or reality markets eg.
Fixtures 11 November / England v Scotland / Correct Score
Strictly Come Dancing 2016 / Week 8 / Lowest Scorer

How are you breaking your results down by say race distance? Do you have a database field which is 'Distance' or do you use queries to look into the market field. I can't see an easy way to pull out say...flat racing novices between 1 and 2 miles or just Domestic Premiership correct score markets.

There's nothing worse than database fields that contain 'Narrative' that needs to be interpretted. What's so hard about providing fields for sport, event, market, sub-market and additional market specific columns for things like distance or grade.

[spreadbetting]

I automate the BettingPandL files into the database not the bettinghistory files as I don't really need the individual bets as they'd be loads. A lot of my stuff is automated and collects it's own data so I only use it for the pnl stuff by market etc rather than individual bets.

Is there any reason why you don't use the bettingPandL files as they're in a regular format if you want to strip out distances etc.

[ShaunWhite]

Sorry sb I misunderstood your reply, because you mentioned PnL files and "The bethistory files"

The PnL files are indeed a more usable starting point, thanks. Although from a database design point of view they're for from ideal. I was just hoping to get the maximum amount of data granularity captured with the minimum amount of effort.

I'm using Excel 2016 and find that when I open the downloaded BettingPnL.xls the data types are all wrong, dates and numbers act like text fields (eg you can't format them but strangly they still sort correctly), filtering offers the wrong options etc. Even if you refer to them they act like text unless you do unnecessary maths on them , like "+ 0" and suddenly teh reference works.

I never had this issue with my beloved Excel 2002. The work around appears to be to open it, save it as a csv, then re-open it and save as xls. Data types all perfect.

I also struggle with the bug whereby you enter say '=A1' in a cell and it shows '=A1', not the value from A1. The fix for that appears to be a global replace of '=' with '=' and all the cells calculate properly. I've been using Excel since last century and Excel2016 is massively frustrating.

[spreadbetting]

You might be better off using some dedicated database program rather than excel. Have you tried Access that comes with MS office, might take a bit of getting used to, but once you start using databases they're a lot more useful for dealing with large volumes of data than spreadsheets and you can easily export out to csv if you want to number crunch in excel. I just use php and MySql databases cos they're free, php is relatively simple to use and can do all the web scraping and organising/querying of data before and after it's sent to the database.

I guess alot depends on what you're trying to do with the data as to how you go about it. Someone's already stuck a spreadsheet on the forum that'll break markets down by course , distance etc just by importing the pnl files.

[ShaunWhite]

You might be better off using some dedicated database program rather than excel. Have you tried Access ...

I had to smile at that, I was in trading system development for 25 years 82-07, VB & SQL mainly but just dull pen pushing for the last 7.

I've not even loaded an SQL database for 10 years and when I quit through boredom, I rather hoped I'd never have to again. It just seems like a bit of a sledgehammer to crack a nut given the amount of re-learning I'd need to do. My knowledge is a little bit version 1.0 compared to the newer fancy dan versions.

Thanks for the help though sb, and I really don't mean that sarcasticly because it's always good to listen to informed advice.

[spreadbetting]

No problem, if you're simply looking to split the data by distances/course etc have a look at this thread

viewtopic.php?f=31&t=133

There's a ready made spreadsheet that just loads the bettingPnL data files. I'm sure it can be tweaked for other sports if needed, should be a good starting point if you want to stick with excel.