Hi all,
I'm currently making a spreadsheet that records data ( pre-race trading).
Eventually, it will build into a decent sized database that can be interrogated by Course, Date, Runners, etc and you'll be able to replay the last ten mins (or whatever), of each race and pick out any moments of interest and see what was happening to the other horse prices/over round at that particular time.
I've only got Excel 2003, so am limited to 225 columns, roughly 8 mins on a 2 second timer, 10 mins on a three second timer.
I've tried a two second timer, but it seems to short.
Has anyone any advice or suggestions as to what to record and when?
Thanks,Nigel
Recording Data. Advice needed.
Just record the data in rows instead of columns. I guess, you will not need to capture more than 255 datapoints per timeslot and the rows are only limited by 65 k so that should be sufficient. I am recording in soccer by that approach ~ 200 data points every 15 seconds (quotes, volumes, times).
Cheers, Fidibus
Cheers, Fidibus
Just record the data in rows instead of columns. I guess, you will not need to capture more than 255 datapoints per timeslot and the rows are only limited by 65 k so that should be sufficient. I am recording in soccer by that approach ~ 200 data points every 15 seconds (quotes, volumes, times).
Cheers, Fidibus
Cheers, Fidibus
I've done something similar and maybe you should try the same. Excel wasn't designed to be used like this and if your sampling market data say once a second your going to end up with broken/full file fairly quickly.
What you need is a database rather than a spreadsheet which can handle millions of entries and concurrent writes to it. Try mysql or the like.
The problem with this database approach is you would need to write some basic software which connects to the BF api and harvests and processes the data your interested in and then writes it to your database.
It also depends on how much detail you want. If you were to capture all detail about a race (market depth info and price info etc) for ten mins pre race every race at an interval of 1 sample per second your database is going to get into the gigabits in size fairly quickly due to the sheer amount of info being processed and stored.
At this point you should decide what is relevent and what is not to your investigations. Perhaps you don't need second accuracy or maybe your not interested in market depth.
Once you have this db its quite staggering what you can do.
I was able to "record" a race to the database then have another application play it back in realtime with a ladder per runner. If you so wish you could also generate any charts you wanted from this data as well as any other analytic techniques you can think of.
This isn't the only way to do it but its the way i found worked best. You can quite happily offload the extra work created by this task to another pc connected to the internet.
If your doing this on the same account your trading on you need to watch out for data charges. If i remember correctly to capture everything it required an additional 4 requests per second to the api at a sample rate of 1 sample per second.
Tash
What you need is a database rather than a spreadsheet which can handle millions of entries and concurrent writes to it. Try mysql or the like.
The problem with this database approach is you would need to write some basic software which connects to the BF api and harvests and processes the data your interested in and then writes it to your database.
It also depends on how much detail you want. If you were to capture all detail about a race (market depth info and price info etc) for ten mins pre race every race at an interval of 1 sample per second your database is going to get into the gigabits in size fairly quickly due to the sheer amount of info being processed and stored.
At this point you should decide what is relevent and what is not to your investigations. Perhaps you don't need second accuracy or maybe your not interested in market depth.
Once you have this db its quite staggering what you can do.
I was able to "record" a race to the database then have another application play it back in realtime with a ladder per runner. If you so wish you could also generate any charts you wanted from this data as well as any other analytic techniques you can think of.
This isn't the only way to do it but its the way i found worked best. You can quite happily offload the extra work created by this task to another pc connected to the internet.
If your doing this on the same account your trading on you need to watch out for data charges. If i remember correctly to capture everything it required an additional 4 requests per second to the api at a sample rate of 1 sample per second.
Tash
Check this sheet out first. Doesn't this record data for the entire day?
viewtopic.php?f=31&t=2385&start=0
viewtopic.php?f=31&t=2385&start=0
I think the op mentioned something about being able to interrogate his data.
That made me think. You could probably add some functionality to the above sheet to write out the data to a db at the end of the day. Thus preserving the sheet for another days battle and allowing you to store your info in something designed to handle and interrogate lots of data.
That made me think. You could probably add some functionality to the above sheet to write out the data to a db at the end of the day. Thus preserving the sheet for another days battle and allowing you to store your info in something designed to handle and interrogate lots of data.
Hi all,
Thanks for all the replies, but for what I had in mind, it would be necessary to record the time data in columns rather than rows.
If you look at the attached, you'll see what I'm trying to do.
On the "Main" sheet, (B2) you've got a date and in C2 a course. At the mo' it's showing 24/07/10 and Ripon Hcap 10 runners.
The first 5 runners entire price history is shown on the left and the time and price on the right charts.
Looking at the 1st chart for Atlantic Beach, I can see straight away that it hit 4.3 several times (only touched 4.4 once).
If I 'tab' along, using the scroll bar, bottom right hand side, I can see that it hit 4.3 for the first time at 06:58 ( right hand chart), clicking on the little blue "06:58" time box at the top, will show the highs and lows of each horse at that time, and also the over round (green chart).
Tabbing along further to examine each time it hit 4.3 and looking at the status of each horse at that time should ( hopefully), give me an idea of why it did not/will not? go through that main resistance, given the status of the other horses, overround etc.
You can select another date and a race traded on that date, and examine as required.
(This in only a test sheet, and the data shown has been changed many times). The final recording will all be done one one sheet rather than 3 as at the moment ( TimeData, BookData and DataT)
Given the amount of data to be recorded, do you think it will be viable to have a "database" of trades daily/weekly or whatever. Do you think all horses should be recorded rather than just the first five? What about the Volume etc?
All suggestions and comments welcome.
On the Control sheet, there are a few bits and pieces that I put in but are not used anymore ( correlation) for example.
Just found out that the max file size allowed to be uploaded is only 256 kb, how I can I upload as a zip file?
Just deleted some data and got it down to necessary size.
Thanks for all the replies, but for what I had in mind, it would be necessary to record the time data in columns rather than rows.
If you look at the attached, you'll see what I'm trying to do.
On the "Main" sheet, (B2) you've got a date and in C2 a course. At the mo' it's showing 24/07/10 and Ripon Hcap 10 runners.
The first 5 runners entire price history is shown on the left and the time and price on the right charts.
Looking at the 1st chart for Atlantic Beach, I can see straight away that it hit 4.3 several times (only touched 4.4 once).
If I 'tab' along, using the scroll bar, bottom right hand side, I can see that it hit 4.3 for the first time at 06:58 ( right hand chart), clicking on the little blue "06:58" time box at the top, will show the highs and lows of each horse at that time, and also the over round (green chart).
Tabbing along further to examine each time it hit 4.3 and looking at the status of each horse at that time should ( hopefully), give me an idea of why it did not/will not? go through that main resistance, given the status of the other horses, overround etc.
You can select another date and a race traded on that date, and examine as required.
(This in only a test sheet, and the data shown has been changed many times). The final recording will all be done one one sheet rather than 3 as at the moment ( TimeData, BookData and DataT)
Given the amount of data to be recorded, do you think it will be viable to have a "database" of trades daily/weekly or whatever. Do you think all horses should be recorded rather than just the first five? What about the Volume etc?
All suggestions and comments welcome.
On the Control sheet, there are a few bits and pieces that I put in but are not used anymore ( correlation) for example.
Just found out that the max file size allowed to be uploaded is only 256 kb, how I can I upload as a zip file?
Just deleted some data and got it down to necessary size.
You do not have the required permissions to view the files attached to this post.
I can see what you're doing but I agree with your earlier poster in that this is not really what excel was designed for.
I went for the route of getting an additional program to record the data to a database using the free api then having something query the database and show charts on this data. This way it doesn't stop auto trading whilst looking at the markets.
Looking at your spreadsheet it does seem to error pretty regularly which makes it hard to look round but that might be because I had BA open at the same time.
If you're still keen to go down this route I could spend a bit of time looking at it with you but I'm not sure ultimately it will be as useful as you're envisaging?
Best of luck,
David
I went for the route of getting an additional program to record the data to a database using the free api then having something query the database and show charts on this data. This way it doesn't stop auto trading whilst looking at the markets.
Looking at your spreadsheet it does seem to error pretty regularly which makes it hard to look round but that might be because I had BA open at the same time.
If you're still keen to go down this route I could spend a bit of time looking at it with you but I'm not sure ultimately it will be as useful as you're envisaging?
Best of luck,
David
Thanks Dave,
I didn't have BA open whilst designing it, so there could well be conflicts of interest in it. I would be interested to hear of anything that could do this sort of job better, or any ideas that anyone has.
As to the use? it could turn out to be non-runner,but I'm always fiddling with Excel and trying out different things, more of a hobby really ( better than Corrie or Eastenders in the evenings!)
Thanks,Nigel
I didn't have BA open whilst designing it, so there could well be conflicts of interest in it. I would be interested to hear of anything that could do this sort of job better, or any ideas that anyone has.
As to the use? it could turn out to be non-runner,but I'm always fiddling with Excel and trying out different things, more of a hobby really ( better than Corrie or Eastenders in the evenings!)
Thanks,Nigel