Results 1 to 5 of 5

Thread: Logging Results From A Live Data Feed.

  1. #1
    Seeker kingsley park's Avatar
    Join Date
    Jul 2016
    Posts
    14
    Articles
    0
    Excel Version
    2007

    Logging Results From A Live Data Feed.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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
    paulBook3.xlsx

  2. #2
    Seeker kingsley park's Avatar
    Join Date
    Jul 2016
    Posts
    14
    Articles
    0
    Excel Version
    2007

  3. #3
    Seeker kingsley park's Avatar
    Join Date
    Jul 2016
    Posts
    14
    Articles
    0
    Excel Version
    2007
    I have uploaded a workbook with the current code I am having problems with.Live feed comes in from an API into ‘W1’. AT present code logs data for 4 values (BACK ODDS,LAY ODDS,LAST PRICE MATCHED and AMOUNT MATCHED into ‘DATA W1’ after every refresh. I have used a crude method of vlookup arrays to then get this data into ‘raw1’ when the stated times match the times in ‘Data W1’.
    I only actually require two values for each runner, LAST PRICE MATCHED and AMOUNT MATCHED, if the code is modified the results would then appear as in ‘Suggested Data W1’ and ‘suggested raw1’.
    As well as less columns , ‘suggested Data W1’ as shown would have a header of horses names for every refresh entry rather than at present just the first entry (as in DATA W1). The purpose of this would be to show when the order of runners in the market may change.At present if they change the code just bangs out the 4 values but these values are no longer necessarily for the horses initially listed.
    The Desired Outcome...
    ‘suggested raw1’ row 3 will with IF functions populate runners from the race sheet ‘race1’ (column i), these will always be alphabetically sorted. In turn at each time in column c of ‘suggested raw1’ each runner in row 3 will be referenced with its relevant data in ‘suggested Data W1’ and it’s 2 values for LAST PRICE Matched and Matched Amount logged into ‘suggested raw1’. If a runner from this referencing between the 2 sheets is not found then N/A should be logged into ‘suggested raw1’.
    This process would take away the current nightmare of having to reorder my racesheet runners (‘race1’)because the order of runners might have changed in the data sheets. The runners in ‘race1’ need never be changed, currently the whole process is just flawed.
    I hope this adds some clarity to the problem and the necessary code and formula changes needed to make this possible.I really would be very grateful for any input on this and please don’t hesitate to ask me for further clarification if necessary.

    Many thanks in advance...

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,536
    Articles
    0
    Excel Version
    365
    cross posted without links:
    https://www.ozgrid.com/forum/forum/h...live-data-feed
    kingsley park/hanley1992, for your information, you should always provide links to your cross posts.
    This is a requirement, not just a request.
    If you have cross posted at other places, please add links to them too.
    Why? Have a read of http://www.excelguru.ca/content.php?184

  5. #5
    Seeker kingsley park's Avatar
    Join Date
    Jul 2016
    Posts
    14
    Articles
    0
    Excel Version
    2007
    Ok

    Apologies, having just read the link it makes sense what you are saying, I can assure you I had no idea it would cause a problem but
    fully accept it.

    You have posted the link now, this is the only other place I have posted regarding this problem.

    https://www.ozgrid.com/forum/forum/h...live-data-feed

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •