New Data Capture Spreadsheet

Post Reply
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

There are lots of very good data capture sheets on this forum but nothing for the longer term trades that can go on for hours.

If you are trading an event which takes 3 hours, you can set your timer to 1 second and have 10,800 thousand rows/columns of data and have a job sorting the wheat from the chaff as many are likely to contain the exact same figures. If you set it to 60 seconds or so, chances are you'll miss that critical price point change.

With this is mind, I've created a spreadsheet that only captures data when either the back/lay prices change. If it only changes once in an hour, you'll only get 1 set of data on the sheet.

Although it was designed with Betdaq, it should work ok with Betfair, just copy and paste the code in module 1 and the Bet Angel sheet into your own spreadsheet.

On the Bet Angel sheet,columns AF:AG you'll see the back/lay % for each runner and the total %s (ignore the 2 figures above, they only act as flags to stop the thing running away with itself).

The data captued is back/lay prices, volume back/lay amounts, back & lay book percentages and the difference , and it also lets you know if there are any gaps between prices.

You shouldn't really need the new market setup button, as it will(should) automatically detect a new market, but does turn on/off the events. So if you fiddle around with the code and get an error, chances are the events will need turning on again, so the event re-set button can come in handy.

Looking at the data, it's quite hard sometimes to figure out why the thing has fired. In col C & D everything look the same, but in row 34 a back price has gone from 30 to 31 and in row 46 a lay price from 114 to 112


On sheet 2 there are a couple of formulas that might help if you want to create your own summary sheet.

I know that Betfair/Betdaq had different prices at one time, and if you have a ladder of your own, just copy and paste it over the existing one.

As mentioned, it should be ideal for the longer term markets, but will work equally well with horse racing.
You do not have the required permissions to view the files attached to this post.
User avatar
gutuami
Posts: 1858
Joined: Wed Apr 15, 2009 4:06 pm

thanks
ijamieson
Posts: 27
Joined: Fri Nov 16, 2012 2:14 am

Nice one :)
Spike
Posts: 13
Joined: Fri May 30, 2014 10:27 am

Thank you.
PeterLe
Posts: 3715
Joined: Wed Apr 15, 2009 3:19 pm

Thanks for that Nigel
I haven't downloaded yet but as wondering if it may be possible capture the VWAP figure too which is an option on the excel setting in betangel?
Would expec that this may be useful for a lot of the longer term swing traders?
Regards
Peter
User avatar
Dallas
Posts: 22671
Joined: Sun Aug 09, 2015 10:57 pm
Location: Working From Home

Fantastic sheet and also many thanks for sharing it will be most useful
annuity
Posts: 81
Joined: Wed Dec 21, 2011 8:03 pm

You the man Nigel.
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

PeterLe wrote:Thanks for that Nigel
I haven't downloaded yet but as wondering if it may be possible capture the VWAP figure too which is an option on the excel setting in betangel?
Would expec that this may be useful for a lot of the longer term swing traders?
Regards
Peter
Thanks guys. How is the VWAP figure worked out? (doh!)
Nigel.
PeterLe
Posts: 3715
Joined: Wed Apr 15, 2009 3:19 pm

Hi Nigel,
The VWAP (Vol Weighted Average Price) is already calculated in Betangel, in the settings area there is an option to output the price to column AF on the spreadsheet, so its just a case of including that fig in your spreadsheet too)

This figure typically shows if the current selection is trading under its average price or over it (kind of over bought or oversold indicator)

I just thought that that is something which would enhance your spreadhseet esp for the longer term swing trader.
(maybe someone may like to comment as to if/how they have found VWAP as a help?)

There is a fairly good explanation on Wiki if anyone is interested

Either way, thanks for posting this Nigel
Regards
Peter
You do not have the required permissions to view the files attached to this post.
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

The only option with Betdaq is the reduction factor.
However, if you want it included you could either send a pm or post a Betfair sheet with that included, it shouldn't be to hard to add it.

See what the general consensus is.
icarus121
Posts: 259
Joined: Mon Nov 16, 2009 10:07 pm

Good work Nigel!

Over or Under bought indicator might work . A step closer to the Grail maybe? :)
nigelk
Posts: 469
Joined: Wed Apr 15, 2009 11:00 pm

Has anyone used it yet over a period of time? Any feedback / questions on its performance?

Nigel.
ygtpamuk
Posts: 1
Joined: Sun Nov 19, 2017 5:04 pm

Hi Peter,

Is VWAP formula is based on a running window or it just takes all of the trades from the start of the preoff?
Post Reply

Return to “Betfair Data”