kingsley park
New member
- Joined
- Jul 26, 2016
- Messages
- 14
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- 2007
Hello Forum,
I have a link to a sports markets site via an API which gives me a live feed of prices and amounts matched in the markets and I would really appreciate some code that logs this information so it can be fed into another sheet for analysis. In the workbook example, the data would be logged in the 'schedule' sheet and then I could go on to use it in the 'racecard sheet' once I have designed this.
I have attached a work book to show the desired format for this data to be captured.
The 'livefeed' sheet will refresh at 1 second intervals when I set the API to log into excel. I have a 'schedule' sheet which states the times I wish to capture the data.I would make sure that the livefeed is triggered a few seconds either side of the schedule times earlier in the day before the feed is stopped. As the intervals on the schedule reduce nearer the end of the market (30 seconds over the last 4 minutes of pre event trading), the feed would be on consistently till the race goes In Play.
The final time to log the data in my schedule is OFF when the race/event actually begins, this is often seconds to a few minutes after the scheduled event off time. In my example the scheduled race time was 13:30:00 and actually went off at 13:30:38. The determination of the off time is when cell E2 in 'livefeed' sheet changes from 'Not In Play' to 'In Play'.In horse racing and many events the market is turned In Play and prices can be traded whilst the event is actually happening as well as before the event starts, I am only interested in the point right up to the event starting.
So the process itself...
I load the cards up early and the first thing is for the 'schedule' to determine the runners from those stated in the 'racecard' (column I from I3 down), these runners should populate row1 in 'schedule' as can be seen in the example.
At the point of the schedule logging times each runner in row1 should be called up and it's relevant data Last price (lP) (column O in 'livefeed') and Total amount matched (colum P in 'live feed') should be recorded into the schedule.It's important to note that some of the runners in row1 of the schedule may end up either not being in the market to start with come the first logging time (they were taken out the previous evening or before 09:00am today) or as the market matures through the day runners again may be taken out.
In the case of the above, this would be evidenced when runners in row1 of the 'schedule' cannot be found in the runners range (column A, A5 down...) of the 'livefeed' sheet. In the 'nr example' sheet this is what would be shown come the off time if Alicia Darcy had been declared a non runner before 9:00:00 and then Jazzameer had been pulled out at 1:06:00.
I would be very grateful if some kind soul could provide a solution to this.
Cheers
paulView attachment Book3.xlsx
I have a link to a sports markets site via an API which gives me a live feed of prices and amounts matched in the markets and I would really appreciate some code that logs this information so it can be fed into another sheet for analysis. In the workbook example, the data would be logged in the 'schedule' sheet and then I could go on to use it in the 'racecard sheet' once I have designed this.
I have attached a work book to show the desired format for this data to be captured.
The 'livefeed' sheet will refresh at 1 second intervals when I set the API to log into excel. I have a 'schedule' sheet which states the times I wish to capture the data.I would make sure that the livefeed is triggered a few seconds either side of the schedule times earlier in the day before the feed is stopped. As the intervals on the schedule reduce nearer the end of the market (30 seconds over the last 4 minutes of pre event trading), the feed would be on consistently till the race goes In Play.
The final time to log the data in my schedule is OFF when the race/event actually begins, this is often seconds to a few minutes after the scheduled event off time. In my example the scheduled race time was 13:30:00 and actually went off at 13:30:38. The determination of the off time is when cell E2 in 'livefeed' sheet changes from 'Not In Play' to 'In Play'.In horse racing and many events the market is turned In Play and prices can be traded whilst the event is actually happening as well as before the event starts, I am only interested in the point right up to the event starting.
So the process itself...
I load the cards up early and the first thing is for the 'schedule' to determine the runners from those stated in the 'racecard' (column I from I3 down), these runners should populate row1 in 'schedule' as can be seen in the example.
At the point of the schedule logging times each runner in row1 should be called up and it's relevant data Last price (lP) (column O in 'livefeed') and Total amount matched (colum P in 'live feed') should be recorded into the schedule.It's important to note that some of the runners in row1 of the schedule may end up either not being in the market to start with come the first logging time (they were taken out the previous evening or before 09:00am today) or as the market matures through the day runners again may be taken out.
In the case of the above, this would be evidenced when runners in row1 of the 'schedule' cannot be found in the runners range (column A, A5 down...) of the 'livefeed' sheet. In the 'nr example' sheet this is what would be shown come the off time if Alicia Darcy had been declared a non runner before 9:00:00 and then Jazzameer had been pulled out at 1:06:00.
I would be very grateful if some kind soul could provide a solution to this.
Cheers
paulView attachment Book3.xlsx
