2 Attachment(s)
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.
Attachment 22687Attachment 22688
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.
