Consulting

Results 1 to 4 of 4

Thread: Sleeper: Stored Values and Fluid Tables

  1. #1
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    128
    Location

    Sleeper: Stored Values and Fluid Tables

    I have a programme that takes information from BetFair’s API (at various speeds between 20m/s and 1 second updates) and displays it in a spreadsheet so I am using VBA code to trigger trades (like placing orders in a stock market) and am looking to explore stored values but have never used them before so I’m looking for some help please.

    I can have different amounts of selections per market but for now I’m just going to keep it simple and focus on the top 6 favourites, in the following explanation I will just explain the first favourite however. I have attached an example spreadsheet that includes the specific cells which will be used. Upon each update, if the matched volume for a selection increases (Cell K10, K12, K14, K16, K18 or K20 increases), I want to be able to visualise this and see the money entering the markets (like creating a fluid table which looks like piles of sand forming simultaneously is what I imagine) as money enters at different times, I can then use this rate of growth in my analysis.

    Say K10 (volume for selection one) changes from £500 to £550 since the last update and the last traded price (K9) was 7.8. I want £50 to be added to the 7.8 ‘sand castle’. In the next update, if another £100 has entered then the 7.8 ‘sand castle’ should now be £150 tall, does this make sense? If the price moves from 7.8 to 8.0 then I still want the spreadsheet to remember that there is £150 at 7.8 but now it’s adding to the 8.0 pile (or whatever K9 is currently showing).

    Does anybody have any idea how to practically achieve what I’m thinking about using stored values or any other aspect that they could recommend some reading for please? I am still not very good with VBA past the basics so please bare with me.

    Just one final thought. Cells AL1:AL351 show all of the possible numbers cell K9 can be (coming directly in via the exchange API). Maybe each of the 6 selections could dedicate a column next to this one and have – again with the niche visual metaphors – a ‘spider’ climbing up and down showing the previous 60 seconds worth of ‘sand pouring’ or ‘money pouring’ into each of the prices. It’s important that I can interpret which money is the newest and which is a minute old. I need to consider time decay, as the stored value falls further behind in the past (updating each second) it will lose importance .

    There’s a second increment countdown in cell C4 if this is of any use?


    Sub MatchedMoneyTest ()

    If C4 is (00:00:01) less than what it was Then

    If K10 has increased, add the difference (what K9 was minus what it is now) to the spider mechanism. The output should not only be the money here but the money multiplied by something.

    AND in another table somewhere show me that it happened a second ago.

    What was recorded a second ago will drop to the ‘two seconds ago’ rank. Each rank will have a multiplier to reduce its importance.

    30 Runner Matched Volume Test.xlsMatchedMoneyLadder.jpg

    I have also attached a picture from the software I am using that does something similar on another tab (the orange part is the range it has traded in, the pink is the latest 'sand pouring'. The software does not let me do this within a spreadsheet though so I have to create my own and also I would like to know what is precisely i.e. 4 seconds old, 7 seconds old etc so I can add weightings and other calculations.

  2. #2
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    128
    Location
    Thinking about this more, I think it would be best to create a moving ladder like the orange and pink section of the previous 30/60 seconds in columns AQ onwards, any ideas how to capture this please?

  3. #3
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    128
    Location
    This is a simpler version to follow, I really hope somebody can help me...

    I have a worksheet change event in my sheet one code that calls all of my subs each time there is a change to any of the cells. Once Sub MatchedMoneyTest () gets called I think I need something like the following to happen:

    Sub MatchedMoneyTest ()
    ‘establish a range of 30 seconds (Using Cell C4 as a reference to note when a second has passed)

    ‘Every time an even cell between K10:K20 increases the increased amount is added to the corresponding last traded price (Just like the orange and pink column in the picture from the previous but-one post) – This information will appear in the column AQ for changes in K10, AR for changes in K12 etc

    ‘The value time decays each second as it passes through the 30 second range so most recent figures have a weight of 30/465, a second later it’s weighting is 29/465, the second later 28/465 etc.

    End Sub

  4. #4
    VBAX Contributor
    Joined
    Sep 2017
    Posts
    128
    Location
    Nobody?

Posting Permissions

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