how to automatically record markets in a quick list

[cjones198]

every time a price moves 2 ticks it creates a two tick range

but on occasions it moves more such as spikes etc


e.g

2.58 : 2.62---------------------------------- [£2.3K ]
2.54 : 2.58 ------------------------[£1K ] -------------[£1K ]------------- --------------------------------------------------[ £4K]
2.50 : 2.54 ---------------[£2K ]------------------------------- [£1.7 K ] -----------------------------------------[£2K ] -----------[ £2,5K]
2.46 : 2.50 ---------------------------------------------------------------------[ £0 (SPIKE)] -----------[£0.7K ]-------------------------------- [£1K ]
2.42 : 2.50 ----------------------------------------------------------------------------------------[£3 K ]

so market opened AT 2.50
2.54 RECORD, 2.58 RECORD, 2.62 RECORD , 2.54 RECORD, 2.50 RECORD 2.42(as spike has happend i wish to record price 2.46 (2 ticks below 2.50) as £0 stats

[Captain Sensible]

None the wiser to be honest, I never use charts and wouldn't have a clue what Renko ones are and even more confused will all this £o stats etc

It's easy enough to log your ticks in multiples of two and if it's 8 you just do your record 4 times instead of once using the floor function i posted..

[cjones198]

sorry for not being clear lol.

so using the floor function i could get over spikes?

so i am logging things in 2 increments,

so for example (recorded ticks in bold)

open recording at 2.50

2.50 2.51 2.52 2.53 2.54 spike of money now enters and 2.62 is matched so i need in my sequence to log the price at 2.58 and then 2.62 after it

if the price would have jumped from 2.54 to 2.66 then 2.58 and 2.62 would need to be logged and then 2.66

[Captain Sensible]

Using floor you can count the amount of 2 tick jumps in your tick increment increase/decrease. So 4 jumps in an 8 tick increase. You can then do whatever 4 times rather than once with a simple loop.

[Captain Sensible]

If you're doing a loop you could probably just use Step and go up in two's

Code: Select all

Sub floor()
Dim i As Integer

Dim old_price As Currency
Dim new_price As Currency

old_price = 2.54
new_price = 2.66

i = getTicks(old_price, new_price)
i = Abs(i)


For i = 2 To i Step 2
MsgBox "Recording price " & plusTicks(old_price, 2)
Next i
End Sub

[cjones198]

Thanks Captain i will have a play with that tomorrow also any idea why this wont work?

Sheets("Selection").Cells(16, iCol).Value = SumIf(Sheets("Selection").Range("D10:ZZ10"), Price, Sheets("Selection").Range("D12:ZZ12"))

sumIf is declared as a static variable as variant

[Captain Sensible]

Sumif is an excel function, you can't declare them. If you want to use Excel functions within VBA they usually have to be written like

WorksheetFunction.SumIf

[Captain Sensible]

https://docs.microsoft.com/en-us/office ... tion.sumif

[cjones198]

Thanks Captain.

So with floor function, I could set a simple set of code in loop that's in multiples of 2?

So for example

Sheet 1 ("market")

Start recording at 2.50 so that is "Price"

Sheet 2 cell (4,3) = Price
Cell (5,3) = "START"
Cell (6,3) = amount matched

Variable iCol = 4

Price hits 2.52

Sheet 2 "selection"

Price logs as such
Cells(10,iCol) = Price,
Cells(11,iCol) = "l"
Cells(12,iCol) = "amount matched

ICol = iCol + 1

So if the actual price matched skips a load of ticks I need it to log it sequentially until it logs the actual traded price if you get me?
So in this example
Say the next price is 2.60

I need

Cells(10,iCol) = Price - 2 ticks (2.56
Cells(11,iCol) = "l"
Cells(12,iCol) = 0 amount matched (it skipped it)

ICol = iCol + 1

Cells(10,iCol) = Price, (2.60)
Cells(11,iCol) = "l"
Cells(12,iCol) = "amount matched

ICol = iCol + 1

And so on and so on

[cjones198]

Sorry pretend that example starts at 2.48

[Captain Sensible]

The floor function would give you the amount of two tick jumps in a number of ticks. I.e. in 9 ticks you have 4 , floor would give you the number 4 which was what it appeared you wanted to start with.

But if you simply want to loop through in steps of 2 then the method here would be better viewtopic.php?f=3&t=10750&p=54505#p54501

[cjones198]

Thanks Captain, I've got it set up ok for now but it's a big load of code.

I have only looped through data in tables. On not live sheets.

simple
For next loops,
So I will have to have an experiment with it, I dont quite understand how it works.

[Captain Sensible]

That's why it's best to break code up into manageable sub routines rather than one long chunk of code.

The for loop is relatively simple it just gets the number of ticks then steps thru it in steps of 2 so 2 4 6 8 etc until it runs out of steps. Within each step you simply do your copying , I just made the example loop msgbox the current price it was on in the loop.

[cjones198]

CODE: SELECT ALLSub floor()
Dim i As Integer

Dim old_price As Currency
Dim new_price As Currency

old_price = 2.54 ??
new_price = 2.66 ??

i = getTicks(old_price, new_price)
i = Abs(i) I want to work with negatives so delete?


For i = 2 To i Step 2
'Column in x in sheet 2 copy new price into it
Rest of the code blah blah blah...
Next i
End Sub

It it should just log in multiples of 2, for example if a price started at 1.50 and suddenly went to 1.56,
It would log 1.52, 1.54 and then 1.56 (whichxis where I would need to log money matched etc, the preceding prices would just be 0 money matched etc

[Captain Sensible]

If you want negatives you'd step -2 instead of 2 so just use an if statement to see if getticks is positive or negative and choose which step to loop.

To log the volume for the last price you can use WorksheetFunction.Floor to find the last number in the range of ticks your logging. If it's the last entry of the loop record the volume if not record 0