PDA

View Full Version : Adding to a Cell Which Time Decays



CPerry
08-15-2018, 01:53 AM
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 the countdown in Cell C4 as a reference to note when a second has passed)
This is really important as this sub routine can't just loop each second as my machine won't check for updates in other subs which need to run every second also.

‘Every time an even cell between K10:K20 increases the increased amount is added to the corresponding last traded price (K9:K19) – This information will appear in the column AQ for changes in K10, AR for changes in K12 etc
For example, if £50 has been added to K10 since the last update and K9 is showing 4.0 then AQ171 will increase by £50. The problem is K9 will refresh much quicker than 1 second at a time and within that second K9 may flicker between two of three prices in really volatile situations so £20 may be at 4.0, £20 at 4.2 and £10 at 4.4???

‘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.
As if the code wasn't complicated enough as it is, I then need the figures that appear in AQ (and AR:AV for the other 5 selections) to decay over a 30 second period so my machine doesn't have to 'remember' that much.

End Sub

Please help! I can't get my head around how the time decay fits into an already defined range and how the code for all 6 selections will work together and with the other sub routines which also get called every second. I really appreciate anybody taking their time to have a look at this for me and would really appreciate any kind of feedback.

Thank you,

CPerry.

22712

CPerry
08-18-2018, 03:50 PM
As you can see from the spreadsheet, it is obvious that K9 is matched with K10, this information goes in column AQ. K11 and K12 go in AR, so there needs to be 6 different cycles happening simultaneously. I was just hoping it could be simplified together for when I expand this to more than 6 for efficiency.

So far I have...



Sub MatchedTest ()

Dim MatchedRange As Range
Dim TimeDecay As Integer

If K10, K12, K14, K16, K18 or K20 change then ‘If no change it should go straight to End if

NewMoneyFormula = what K9, K11, K13, K15, K17 or K19 IS minus what it WAS
New amount gets added to AQ, AR, AS, AT, AU or AV adjacent to reading in K9:K19

Set MatchedRange = ‘Currently active cells in column AQ

MatchedRange = ‘Formula for what I want to do with the defined range goes here

TimeDecay = Rank/465*MatchedRange – 1/465 ‘Where the rank = how many seconds have passed

End If

End Sub

Please somebody help me fill in the blanks!

I have attached yet another spreadsheet:

22734

This shows what I'm hoping to achieve. I have a stationary 30-1 count in column A, random integers between 0:100 in column B and C is the time decay'd end result which I will be recording each second such as in the line graph.

offthelip
08-20-2018, 08:57 AM
Hi Cperry,
Still working on your Betangel project I see!!
I don't fully understand what you are trying to do, but if you are trying to decay the values which create the values in column C of "timedecay.xlsx". I think all you have to do is once every second multiply each of the values in columns A and B by 29/30, this is very easily done using a macro triggered every second. You already have yoiur worksheet change event triggering frequently so just run the macor when time has increased by a second since it last ran.

CPerry
08-21-2018, 11:08 AM
Hi offthelip, yeah still working hard around school terms on this project of mine!

The software does this but the only information on the spreadsheet is the last traded price and the volume matched on the runner:

https://www.youtube.com/watch?v=WYXa5K18cJ4&t=51s

So basically I was hoping to recreate the pink 60 second range, or in this case 30 seconds. But I’ve had a think and I reckon instead of the whole formula having time decay I’d like to explore a few hundred or thousand of markets where just the money just decays over a rolling 30 seconds, got any ideas how this can be done please?

As you can see in the video the price sometimes moves a few times before a second has passed and in some markets it moves incredibly slowly. The only information I have to go on is K9 the last traded price and K10 amount matched for that one specific selection. The recreation of the pink thing is happening in column AQ. Like I’ve typed before the 30 seconds will decay like the following: 30/465, 29/465, 28/265.


The problem I am having is with piecing together my idea. Say I already have the following in AQ:

4.0 = £100*30/465
4.2 = £150*30/465
4.4 = £220*30/465
4.6 = £85*30/465

If K10 increases by £30 and K9 is showing 4.4 for the entire second then AQ should now show:

4.0 = £100*29/465
4.2 = £150*29/465
4.4 = £220*29/465 + £30*30/465
4.6 = £85*29/465

It get's even more difficult when a price moves a few times within a second, the time decay I still want to happen every second.

offthelip
08-21-2018, 12:14 PM
I think what you say just confirms what I suggested that all you have to do is multiply one of the values in your equation by 29/30 every second, it doesn't matter whether you add other values in or not, they will all decay exponentially with 30 second time frame.

CPerry
08-26-2018, 05:08 AM
@ offthelip

I have tried with some dummy values to multiply each next value by 29/30 but this doesn’t work, as you can see in the example spreadsheet it works if you multiply by 29/30, 28/29, 27/28 etc. This can be seen as: Multiplier = P*(R/R+1) Where P is the previous result in the range and R is the rank within the range, in other words, how many seconds left. For example, with 14 seconds left the multiplier will be P*14/15.

As well as this though, I still have no clue how to take this dripping of new money (from Cell K10) and add it to cell AQ before putting the time decay range on. All I know is that I think the code should go a little something like this…


Sub MatchedRange

MoneyVariable = K10

If MoneyVariable changes then
Add New amount to column AQ ‘Do this by looking at the value in K9, matching it with Cell AL and then adding this new value to AQ by offsetting the identified (AL) cell.
'Also multiply this new added amount by 30/465 to create the first weighted amount.

If one second has passed, then
‘ What is present in the active range in column AQ will decay at the rate discussed at the beginning of this post.

End Sub

Is this possible? I don’t know how to add to a value of a cell, how to identify an active range where there’s not a clear beginning (a stationary Row A) or end and also this only works for the first selection (involving K9, K10 and column AQ). It does not account for any of the other selections.

22776

offthelip
08-26-2018, 08:52 AM
What you are doing is subtracting a fixed amount for each amount that has been added from the sum each iteration,
so to get the results you need you are going to need to keep a track of when an amount is added in and also how much.
The easiest way is when an amount is added don't just add it into the sum, record it in a buffer with the time it was added. Then you subtract 1/30 of this amount from your rolling total every 1 sec. You do this for every value in the buffer. When an amount has been in the buffer for 30 seconds you delete it.

CPerry
08-26-2018, 01:29 PM
I'm not finding a lot of useful information about recording in a buffer other than a lot of guidance on buffer overflows which sounds like something I need to avoid :clap: clever I am. So, being serious again, how does a buffer come into play and why is it necessary? I'm guessing the code will start to look like this?...


Start Sub

New money *30/465 'so it's already 'weighted' when it enters the range
New money get's placed in column AQ

If a second has passed then
Everything in the buffer * decay formula (P*(R/R+1))

End Sub

Please could you explain how to include a buffer in this respect?

offthelip
08-26-2018, 02:59 PM
Hi Cperry
I have written some code to show you how this should work.
I am using 4 columns A to D , "Amount" "Time Added" , "Fraction" and "Current Value"
I have allowed for a buffer with a maximum of 30 values but it can be any number you like.
there are two subroutines
"AddAmount" this add an amount ( this code using cell J1 as the source of the amount) to the buffer and records the time and the fractional amount to be decremented
"Decrement" is the routine that will run every second and will keep the current value updated and decremented over 30 sec. 30 seconds after an amount is added this routine will delete the row making it available for the next amount which is added.

It is all very simple code so should be self explanatory


Sub Decrement()



inarr = Range(Cells(1, 1), Cells(30, 4))
timenow = Time()
sec30 = 1 / (24 * 60 * 2)
For i = 2 To 30
' Check time

timediff = Abs(timenow - inarr(i, 2))


If timediff > sec30 Then
For k = 1 To 4
inarr(i, k) = ""
Next k
Else
' decrement
If inarr(i, 4) = "" Then
' first iteration
inarr(i, 4) = inarr(i, 1) - inarr(i, 3)
Else
inarr(i, 4) = inarr(i, 4) - inarr(i, 3)
End If

End If

Next i
Range(Cells(1, 1), Cells(30, 4)) = inarr
End Sub


Sub addamount()
inarr = Range(Cells(1, 1), Cells(30, 3))
For i = 2 To 30
If inarr(i, 1) = "" Then
' this is a blank row so populate it from j1
inarr(i, 1) = Cells(1, 10)
inarr(i, 2) = Time()
inarr(i, 3) = inarr(i, 1) / 30
Exit For
End If
Next i
Range(Cells(1, 1), Cells(30, 3)) = inarr
End Sub

CPerry
08-27-2018, 09:29 AM
Ok I’ve had a little play with this code manually by just clicking a macro button (sometimes twice a second, sometimes missing a second) and I think I get how this works by stepping in turn with the code every line. Sometimes it is a bit sporadic where it starts but I think this is just down to me playing with the sheet manually. The only part I don’t understand is the fraction, I’m assuming this should go 29/30, 28/29, 27/28, 26/27 etc but it is only showing ½ for me. I was getting random fractions appear at first but there was no pattern.

This is what I was getting...
22777

I know this was just a theoretical example piece of code and I get how the pieces (minus the fraction) can work but still unsure on how this is going to combine and materialise itself in column AQ with a moving range.

22778

The differences each second in K10 need to go over to column AQ alongside their K9 readings each time the sheet updates. Worksheet 1 code shows the general structure of my subs. I've been trying really hard to improve since the beginning of the year but looks like I still have a long way to go, just got to try and learn as much every day as I can I suppose.

offthelip
08-27-2018, 10:21 AM
look back at your TimedecayExp2.xlsx, what you wanted was a linear time decay that went from whatever the original value was to zero in 30 seconds that is exactly what this code does.
You have formatted the Fraction column incorrectly is should just be a decimal number. If you look at the code this is just the value in column A times 1/30. This is the amount that is subtracted off the "current value" every second. So it should be zero after 30 second having gone down in a linear manner.
Your workbook "time decay template" is complicated and i haven't really looked at so i don't understand what you are trying to do with it.
All i have shown you is how to do the time decays , where you apply it is a different matter.
If you explain in terms of "the values in K10 are added into the Value in Aq30 whenever K9 changes, and each value should be decayed over 30 secs", then i might be able to help.
I just need to know what cells values are moving or being added into what and what decays are needed.
I forgot to point out that if subtract D6 from E6 on you TimedecayEXP2 worksheet and then copy the equation across the row you will find that the amount being deducted each time is exactly the same
this is because: assume you start with a value of 30 then
30*29/30= 29 now multiply this by 28/29 and you get 28 etc etc the decrements are always the same. That is why my "fraction" doesn't need to change it is simple 1/30 whic his deducted each time

CPerry
08-28-2018, 07:05 AM
Ok so the TimeDecay Template is the BA Template that you saw many many months ago, with the instruction field, the prices, the volume etc granted it was ages ago so the important variation is columns AL:AP. The other columns are used for something else so only AL is important for now.

The problem with K10 is it just shows the volume on the selection, it does nothing for how this money is spread amongst varying prices of that selection.

For example, in 22782 the total volume is £51,936.30 (C4) of which the first selection Skin Deep has a volume of £40,386.30 (K10) but not all of this was at 1.56 (K9) so I am trying to have the latest 30 seconds worth of volume to be recorded in column AQ.

To illustrate this, I have another example spreadsheet: 22783where you can see A2 being most recent and A31 being 30 seconds ago. Column B shows the amounts added each second (this assumes that all the volume each second is at the same price). Column C shows the money was matched at each second and column D has the Time Decay formula in.

I have then sorted this information in columns F and G and from that created the orange chart. My end goal is simply to analyse (over hundreds and thousands of markets whether there is an edge in observing the previous 30 seconds worth of movement alongside a momentum indicator that is currently is the recording phase).

So to summarise, as money enters the market (Seen by K10 increasing) for the first selection I need my spreadsheet to take the new money and place is in column AQ. If the money entered at 1.56 (K9) it would need to appear in AQ57 as this is 5 cells right of 1.56 in column AL. As the new amount enters the market it is multiplied by 30/465 and then for every second it is present within the 30 second range, it decays until after 30 seconds that splash of money is ‘forgotten’. My spreadsheet only has to monitor 30 seconds rather than ‘remember’ hours of information.

I hope this makes sense what I’m trying to do. Columns F and G in TimeDecayExp3 is what AL and AP will be in the final thing.

CPerry.

offthelip
08-28-2018, 08:49 AM
I think I understand what you are trying to do and what I create is exactly what you need. I have done the buffer vertically, you need to swap it to a horizontal buffer and you then have a separate buffer for each price point down Aq

CPerry
08-30-2018, 03:54 PM
The only problem with that is there are 351 possible cells in AQ, so each one would need a separate buffer? I'm trying to make this as efficient (and quick) as possible so was trying to figure out a way of only having active - and only having a buffer for - the cells in AQ that have been traded in the previous 30 seconds. For example if money has entered at 5.0 (as calculated by K10 increasing and K9 showing '5.0') then that increased amount would enter the buffer in the AQ cell adjacent to 5.0 in the AL column. This way 351 buffers don't need to exist, do you know what I mean?

offthelip
08-31-2018, 12:33 AM
Yes each one will need a separate buffer.
The fact the buffers exist doesn't cost you any time, the only time taken is updating a buffer , it doesn't really matter which buffer you are updating, whether it is one buffer or one out of 351 buffers the time taken is the same

CPerry
09-01-2018, 03:01 PM
Ok, thanks for this. I have a bit of time tomorrow dinner to sit down and look at this in detail for the first time in a few days. In the meantime, around Christmas time last year when I was looking at RSI and MACD indicators you said you'd take a look at some information to pinpoint good entry and exit points. May I just ask, was this a matter of using the solver plugin with some common adjustable variables or is your process a little more complex? I'm just planning ahead and thinking of how to analyse (and what I need to analyse) once I've worked out this stage and logged a few thousand markets.

Cheers,
CPerry.

offthelip
09-03-2018, 03:11 PM
was this a matter of using the solver plugin with some common adjustable variables or is your process a little more complex?
I think what you are referring to is the system I set up for back testing strategies. The final system I ended up with was extremely complex although the basic principle was very simple.
I started with worksheet with a long list of OHLC price data in columns A to E, (column A was the date or the time depending on whether it was daily data or intraday data). I then added columns from column F onwards which calculated all sorts of indicators such emas RSi , PSAR, ADX , candlestick patterns etc , etc. I wrote functions for some of these, others I just calculated using one or more columns, I then combined these columns to create "Buy" triggers and "Sell" triggers.
Then I ran two macros a "Long" macro which would look first for "Buy" triggers and enter market on a Buy trigger and then hold the position til a "sell " Trigger" was encountered. The macro below does just this.
I also developed a "short" macro which was virtually identical but going short
However I developed the system further to take account of setting the initial stoploss and trailing the stoploss and using multiple closeout signals, and multiple positions and many other things as well. Including recording any other paramaters which I wanted to record.
I used this system to test strategies, and I was able to run tests on enormous amounts of data e.g I could run tests which included all the stocks in the FTSE 350 over any period, although due to historical data problems I rarely ran test on stocks over more than 6 years. However on things like the FTSe 100 and DOW I was running over decades of years of data.
Having found a strategy that seemed to work I would then test it on an entirely new set of data to ensure that I wasn't just "curve fitting" If it passed this stage, I then would look at how I could actually trade the strategy and how I could implement it automatically. Often the strategy would fall over at this stage. If I was able to get it passed this stage I would then test it on a dummy account. This step was essential because it is only when running on the real interface with the provider do you find out the timing quirks and the problems of what happens when your system or their system goes off line, and other things such my system and their system getting out of step.
I also developed tools for analysing the results. Finding trades which are very profitable is not the only thing to be looking for, you might have system that only wins one in ten times but when it wins it makes £100, when it loses it loses £10. so after 20 entries you would expect it to have one twice (+£200) and lost 18 times (-£180) . How do you compare this to a system that wins 55% of the time but only wins £10 when it wins and loses £10 on losers After 20 entries you would expect 11 winners (+£110) and 9 losers (-£90) . The profit is the same.
Another aspect is you might find a system that wins £100 and has win rate of 75%, absolutely brilliant you might think, however if this only occurs 6 times in 6 years it is going to be difficult to make much money using it.
there were lots of other things I was looking at such maximum expected drawdown, maximum risk, capital needed, lots of other things
All of the system and tools I developed were trying to ensure that the strategy was usable and generated sufficient trades to ensure the statistics were valid and profitable.

I hope this helps




Sub golong()


buycol = 9
sellcol = 10
profitcol = 11
firstrow = 16
LastRow = 274
bytrig = Range(Cells(firstrow, buycol), Cells(LastRow, buycol))
selltrig = Range(Cells(firstrow, sellcol), Cells(LastRow, sellcol))
Range(Cells(firstrow, profitcol), Cells(LastRow, profitcol)) = ""


Profit = Range(Cells(firstrow, profitcol), Cells(LastRow, profitcol))
closep = Range(Cells(firstrow, 5), Cells(LastRow, 5))
Longf = False
For I = 1 To (LastRow - firstrow)
If Not (Longf) Then
If bytrig(I, 1) Then
buyp = closep(I, 1)
Longf = True
End If
Else
' If bytrig(i - 5, 1) Then

If selltrig(I, 1) Then
Profit(I, 1) = 100 * (closep(I, 1) - buyp) / buyp
Longf = False
End If
End If
Next I
Range(Cells(firstrow, profitcol), Cells(LastRow, profitcol)) = Profit


End Sub

CPerry
09-05-2018, 01:28 PM
Hi offthelip.

I have added your example code (But amended it so it now operates in T2:W2 and takes the added amount from K10) but I am still confused how this works if K9 changes more than once per one second update.

Everything in this updated example spreadsheet: 22832is wired up correctly and works. If you look at A19:E48 you can see an example 30 seconds. Now if you change K9 to whatever is in column B (working up the list from the bottom to the top) and pressing the macro buttons in the correct order you will see the final outcome is well off. I was still using the timenow function so had to ‘play’ the buttons with the correct timings, took me a few times (thank god I’m a mediocre musician). You may have a smarter idea of how to run this.

Macro button order:
Check K9 is the next number in column B (B48 first reading) working from the bottom of the list to the top.

Press £25/£50/£100 macro button based on reading in column C.
Add Amount Macro Button
Time Decay Button
TickTock button (Counts down one second – now repeat the process again with B47 and ascend the list)

On the second cycle you type 1.18 into K9, press £25, AddAmount, TimeDecay then type 1.19, press £25, AddAmount, TimeDecay and then press TickTock. This is where the problem is, when there’s more than one price (K9) per update. F19:G25 is what the final figures should show with this feux data and ultimately G19:G25 should be R14:R20. Just can't seem to get this final R14:R20 no matter what I try!!

Cheers,
CPerry.

CPerry
09-05-2018, 01:40 PM
^^ That is all very interesting to read, you have given me a number of additional things to consider when I run my next lot of logs.

I’m excited for the analysis part of this new system. The one big difference with these markets to the FTSE stocks is that they are much shorter and they end so I can’t have any long ranging indicators like for example a very slow EMA. I think the short nature of these markets means I’m going to have to go about my analysis a little bit different but I have some good ideas with how this can be done!

If I collect enough data i.e. over 10,000 markets do you reckon splitting it into two 5,000’s (back test and new data) would be a wide enough range to sniff out any ‘curve fitting’ or would you rather have three 3,333’s (back test, new data, even more new data)?

I have my practise account ready to go, playing practise mode first then opening up my funds with a max of £100 I’m willing to lose by trading £2 stakes many many times. Before any of this though I’m looking forward to really delving into the analytics and seeing if I have anything that looks promising.

You make some very good points about strike rate and my win/loss ratio. I’m going to be analysing potential strategies’ ROI, strike rate etc just to see which kind of strategies work best with my ideas. This is more of a swing trading system than a scalper so I’m expecting my profits and losses to be a lot looser; bigger ROI’s but smaller strike rate’s I’m anticipating.

CPerry.

offthelip
09-05-2018, 03:24 PM
I had a look at Previous_working_exp workbook and you have put my code in it, but my code was only a single buffer you need to modify it so that it creates a buffer for each one of the prices. So I am not in slightest bit surprised it doesn't do what you expect.
As a clue you are not looking at cell(9,11) which is the one that holds the price. What you need to do is use the price as an index into the array of buffers. For convenience I would put the buffers in columns AA to some far column. remember you need 3 values for each entry so effectively you have three buffers offset along the same row, I would offset them by a large number which you know will be more than the maximum possible entries on a row. e.g 50 or 100

the other thing I would do is set up your list of test data, to run automatically, remember that when testing you don't have to do it in real time, you can do it faster (preferable) or slower ( not preferable, but sometimes it happens)

So with your data in A19 to C48, sort it into the order you want it to be "played" use a separate row for each item, reverse the order of time count so that 1 is the first to "played" 30 is the last , if two items have the same time that is fine.
Then set up a loop running a count starting at 1 up to 30, if count is 1 call "addamount" with the values for each row with a 1 in the time col, then call decrement, then increment the count and do time slot 2, then decrement again.
This should seconds at most to run, (Not 30 seconds)
Writing code to do this should take no longer than putting all those buttons on the worksheet which you have put on to do it manually. #
Try to start thinking about how to automate as much as possible of what you are doing. Everytime you develop a little system you have then got a little item for your toolbox you can modify to and reuse for something else and you teach yourself how to really advance in the game.

CPerry
09-10-2018, 03:17 AM
I just tried to use the single buffer to be honest as I’m struggling to visualise the final thing but perhaps I’m getting ahead of myself so I had a go at creating a buffer for each price. I have been scratching my head over this all weekend.

Ok so breaking it down to the bare basics, I firstly want Sub AddAmount to identify the price in (9, 11) and use this as an index you say, so the array can place the money in the correct buffer? Save having 351 separate buffer subs, there will be a single array which accounts for each of the 351 possible prices. So I just need to modify the AddAmount sub routine not the Decrement one yet?

I thought that makes sense and I began reading on how to use an index with an array in VBA:
https://stackoverflow.com/questions/7031416/return-index-of-an-element-in-an-array-excel-vba

I thought the first example in this link would fit nicely in but then I got confused with the part you typed after. I thought arrays were behind the scenes, so why do I need rows from AA onwards for the buffers. I assumed the final figure at each price would be written only to column AQ for the first selection and there wouldn’t be anything else being written to the sheet at this stage?

If I understand that the: ‘Amount, Time Added and Fraction’ columns are what you are referring to when you say I need 3 values for each entry and the Current Value is in fact column AQ when these figures get sorted then I don’t understand why there would be more than 3 columns (50 to 100 you said?). If I use (9, 11) as an index, how am I referring to an array with that figure? Does it precede the part already in the AddAmount routine? If so wont this just fill in 3 columns?

I think I’ve over thought a lot of this, just need some clarity please.

offthelip
09-12-2018, 08:06 AM
Hi Cperry,
Sorry for delay in reply I have been away. I have written some code which will do the add amount and decrement using 351 separate buffers as defined on your "previous _Working_exp " workbook
I haven' actually writtne any information back to the workbook because I didn't know what you wanted or where.
You were absolutely correct about my comment about writing to the columns to the right of Q. it is entirely unnecessary I was thinking you needed to record them.
Not the msgbox, is just for debug so you know where to look for the values in the 3 dimensional array G3darr

You must run "Init" first, you could do this on workbook open


Public G3darr(1 To 351, 1 To 30, 1 To 4) As Variant
Public prices() As Variant


Sub init()
For i = 1 To 351
For j = 1 To 30
For k = 1 To 4
G3darr(1, j, k) = ""
Next k
Next j
Next i
prices = Range(Cells(1, 13), Cells(351, 13))


End Sub
Sub Decrement()


timenow = Time()
sec30 = 1 / (24 * 60 * 2)
For p1 = 2 To 351
For i = 2 To 30
'Check Time
If G3darr(p1, i, 2) <> "" Then
timediff = Abs(timenow - G3darr(p1, i, 2))


If timediff > sec30 Then
For k = 1 To 4
G3darr(p1, i, k) = ""
Next k
Else
'Decrement
If G3darr(p1, i, 4) = "" Then
'First Iteration
G3darr(p1, i, 4) = G3darr(p1, i, 1) - G3darr(p1, i, 3)
Else
G3darr(p1, i, 4) = G3darr(p1, i, 4) - G3darr(p1, i, 3)
End If
End If
End If


Next i
Next p1

End Sub
Sub AddAmount()
amount = Cells(10, 11)
Price = Cells(9, 11)
' find buffer
For k = 1 To 351
If Price = prices(k, 1) Then
p1 = k
MsgBox (k)
Exit For
End If
Next k
For i = 2 To 30
If G3darr(p1, i, 1) = "" Then
'This is a blank row so populate it from j1
G3darr(p1, i, 1) = amount
G3darr(p1, i, 2) = Time()
G3darr(p1, i, 3) = G3darr(p1, i, 1) / 30
Exit For
End If


Next i

End Sub

CPerry
09-15-2018, 10:11 AM
Hi offthelip, hope you’ve been away somewhere nice!

I have been looking through the code and just have a few questions. Before that though, the information that needs to be written back to the sheet is just the numbers in column R in “Previous_30_Working_Exp” workbook. So after the figures have ‘decayed’ they go in R alongside the value in M (which value exactly is taken from K9). For example, If K9 = 1.4 then 1.4 that can be found in M41 therefore the decayed value will be added to R41 as this is 5 cells to the right. 30 seconds worth of data will occupy a mini range within column R and this is the only information which needs to be written back to the sheet.

My first question is regarding the Sub init. I’ve been stepping through to see how it works and it appears it cycles through each letter I, j, k in turn and does a total of 30,000 to 40,000+ loops. Luckily I did a bit of maths and didn’t click through that many times aha but please tell me what is this for? How many times does it need to be called, just once as the workbook opens or as each new market opens?

I ask because the first bug I get is in the Sub AddAmount (). After


For k = 1 to 351
If Price = Price(k, 1) Then

It returns a type mismatch and shows Price = 1.1 (Makes sense as this is the value taken from Cell K9) but then hovering the mouse after the = it shows Price(k, 1) <Type Mismatch>.

Before I start messing around I wanted to know what the purpose of the init sub was please. I understand the letters having to be established for later on but why do this cycle of 4 inside a cycle of 30 inside a cycle of 351? It is the letter k I assume that is the problem at the moment and may be the other letters after this line has been debugged. Why the mismatch?

Looking like it’s not that far away now!

Thank you as always for taking a look for me,
CPerry.

offthelip
09-15-2018, 02:50 PM
The purpose of the sub init is to initialise the 3 dimensional array with blank because we start using the numbers in the decrement subrotine so we need test if they are zero to see if they are in use or expired.
This sub also loads the Prices array with the prices in column M of your spreadsheet.
You have to decide when to call this. Are the prices of the odds the same for every race? what do you want to do about values which still remain in the array after the end of the race? will you still keep calling decrement every second between races? Would it be easier to reinitialise for every race?


The reason you get a type mismatch is because the first value in the array prices is the header in colum M which is the word "Odds". Obviously when you try to comare 1.1 to a word you get a type mismatch. thus the index in your loop should start at 2
e.g

For k = 2 To 351

If Price = prices(k, 1) Then

I think what you want written back to worksheet in column R will be the sum of decrementing values. although I am not usre from you explanation of what you want:
I think this code will do what you want: (untested)


Sub Decrement()
outarr = Range(Cells(1, 18), Cells(351, 18))
timenow = Time()
sec30 = 1 / (24 * 60 * 2)
For p1 = 2 To 351
p1sum = 0
For i = 2 To 30
'Check Time
If G3darr(p1, i, 2) <> "" Then
timediff = Abs(timenow - G3darr(p1, i, 2))


If timediff > sec30 Then
For k = 1 To 4
G3darr(p1, i, k) = ""
Next k
Else
'Decrement
If G3darr(p1, i, 4) = "" Then
'First Iteration
G3darr(p1, i, 4) = G3darr(p1, i, 1) - G3darr(p1, i, 3)
p1sum = G3darr(p1, i, 4)
Else
G3darr(p1, i, 4) = G3darr(p1, i, 4) - G3darr(p1, i, 3)
p1sum = p1sum + G3darr(p1, i, 4)
End If
End If
End If


Next i
outarr(p1, 1) = p1sum


Next p1
Range(Cells(1, 18), Cells(351, 18)) = outarr
End Sub

CPerry
09-17-2018, 02:28 AM
I think it would work best if it reinitialises for each market. At the moment my sheet 1 code looks like this and I’ve written notes with what I think should be included:


Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If Range(“C4”).Value > 1 Then ‘Check the sheet had loaded before returning an error

‘Variable = B1 (Name of market), If B1 changes then reinitialise sub init and clear my rolling logger (call two separate subs)

‘Every update
Call AddAmount

Call StateMachineOne
Call StateMachineTwo
Call StateMachineThree
Call StateMachineFour
Call StateMachineFive
Call StateMachineSix

‘Every second
Call Decrement
Call RollingLogger
Call DataTab

End if

Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

End Sub

So there are clearly different areas for subs which need to run each time a cell changes, subs which need to update once a second and subs which need to run each time a new market loads into the sheet.

The prices are different for every selection for every market. At the start of a new market column R needs to be emptied. The next race will load immediately after the previous until the end of the day so decrement can continue to be called every second, it doesn’t really matter. Not if it falls into the above code how I’ve laid it out.

Ahhhh. It’s mixing text with numbers, I get it! Yes that’s exactly what I want I column R, the sum of the decremented values. I will try this code out right now and see if it works!

CPerry
09-17-2018, 02:31 AM
Update:

All three subs now run smoothly with no errors and column R fills up with 0’s (These would be more beneficial to remain blank, also better if doing this speeds up the process). The big problem is the cell next to 1.1 in the R column is also 0. I’ve tried adding an amount to K10 multiple times and then pressing the add difference button and time decay button each time but nothing happens. The msgbox appears so I know the subs are running fine but the cell doesn’t increase by a decremented amount. I’ve attached the spreadsheet below. Ran init at the start then keep adding amounts and pressing the buttons. Nothing is increasing in column R.

22885

offthelip
09-17-2018, 08:37 AM
you must be much more careful about copying code: also try and understand how it works, if you do it will obvious why it doesn't work. , it took me seconds to see why you code didn't work,
you have missed out two vital lines of code:
your code:

Else 'Decrement
If G3darr(p1, i, 4) = "" Then
'First Iteration
G3darr(p1, i, 4) = G3darr(p1, i, 1) - G3darr(p1, i, 3)
Else
G3darr(p1, i, 4) = G3darr(p1, i, 4) - G3darr(p1, i, 3)
End If
End If




look back at post 24 and note the difference.

CPerry
09-18-2018, 06:00 AM
Hi offthelip,

Yes, very stupid mistake! To cut a long story short, the coding happens on my brother’s laptop (Windows - but I use the internet (inc this forum on my MacBook)) so I manually copy the code over and try it out a line at a time usually to learn how it works, completely overlooked two vital lines this time round obviously.

That has sorted the problem of only returning 0’s but now there’s another problem, it’s decrementing the entire volume in K10 so if K10 increases from £1,000 to £1,025 it should only show 0.05376 (£25 * 1/465) and then this amount will decrement by 1/465 every second. Instead it’s doing £1025 * 1/465. It should only do the most recent addition of money not the entire volume on the runner if that makes any sense.

Not sure how to test manually so at the moment I’ve been using formulas to come up with what a final set of numbers should look like. Please take a look at yet another example spreadsheet (sorry it’s how I work things out) and note how each second decrements independently on top of additional money being added to each Price each second before being summed to create what column R should look like after the first 30 seconds. This time the list is reversed so prices at the top (red number 1 in A2) is the most recent.

Ps, this example naively plays with the idea that there is only one possible price each second, remember it could be multiple but I believe the system already accounts for this. Just making it apparent that this is a very basic screenshot.

CPerry.

22890

CPerry
09-18-2018, 06:49 AM
22891

Another stupid mistake! Please look at this spreadsheet example instead! In the previous one I first multiplied everything in column C by 1/465 when it should have been 30/465. Fixed now. Minor error, need to stop making so many!

offthelip
09-18-2018, 10:03 AM
As I stated right at the beginning of this thread I am not clear what you are trying to do.
What I thought you were trying to do was something like this:
Data comes in from betangel giving details of the latest bets which are placed. This data includes the amount and the odds they are placed
You want to create a dynamic view which updates every second which shows where the money has been placed in the last 30 seconds
You want to use a linear decay for each amount which is placed so that each amount decays to zero over 30 seconds
you want to display the total decayed amount placed at each price.

Is this correct?

your latest worksheet doesn't help me to understand at all.

CPerry
09-24-2018, 02:04 PM
22922

Not that it was vital, but here is the picture I was referring to in the last message. All it's showing is the chunks gradually decrease in size equally and altogether equal 1. That's how I got the denominator as 465 as 30+29+28+27 etc = 465. That's why the big chunk is 30/465 and I was on about using this as a multiplier in the decrement sub.