I will be streaming data from the programme BetAngel at 20m/s so I'm just trying to get as close to that number as possible. The thing that is getting recorded at that price is simply the back price (10 iterations) at 20m/s to create OHLC figures every 200m/s. If the data doesn't come as through as fast and I only get 7/8 iterations per 200m/s instead of 10 that is fine as my system will be consolidating it's calculations with larger average movements. It's ok if it misses a few signals as prices don't move that quickly, as close to 10 is what I'm trying to achieve. As long as I am getting substantial numbers to build my OHLC table up I'm happy. So all of the code as follows in this order in one single module?:
P.s. Again being new and stupid, how does this code know which cells I want the calculations to take place in exactly? Other than the Sub twenty code shown below, non of the others have cell/range commands, do they? And also how does the sheet know to only do it when it's a specific time? I have a countdown (in seconds) timer in cell G40 and a start time shown in cell G39. Do any triggers need to be made in the spreadsheet itself? And is the following code all going in one single module? How does this module know that it is to be present in EVERY sheet I have, is that just default?
Private Declare Function getFrequency Lib "kernel32" Alias _
"QueryPerformanceFrequency" (cyFrequency As Currency) As Long
Private Declare Function getTickCount Lib "kernel32" Alias _
"QueryPerformanceCounter" (cyTickCount As Currency) As Long
Private Const sCPURegKey = "HARDWARE\DESCRIPTION\System\CentralProcessor\0"
Private Const HKEY_LOCAL_MACHINE As Long = &H80000002
Private Declare Function RegCloseKey Lib "advapi32.dll" (ByVal hKey As Long) As Long
Private Declare Function RegOpenKey Lib "advapi32.dll" Alias "RegOpenKeyA" (ByVal hKey As Long, ByVal lpSubKey As String, phkResult As Long) As Long
Private Declare Function RegQueryValueEx Lib "advapi32.dll" Alias "RegQueryValueExA" (ByVal hKey As Long, ByVal lpValueName As String, ByVal lpReserved As Long, lpType As Long, lpData As Any, lpcbData As Long) As Long
Function MicroTimer() As Double
'
' returns seconds
'[
Dim cyTicks1 As Currency
Static cyFrequency As Currency
'
MicroTimer = 0
If cyFrequency = 0 Then getFrequency cyFrequency ' get ticks/sec
getTickCount cyTicks1 ' get ticks
If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency ' calc seconds
Call twenty
End Function
'Calling macro
Sub Test()
Dim i As Long
Dim Tim As Double
Dim Result1 As Double, Result2 As Double
Dim Factor As Long
Factor = 10000 '<== adjust to show clear result
Tim = MicroTimer
For i = 1 To 100000
DoEvents
Next
Result1 = MicroTimer - Tim
Tim = MicroTimer
For i = 1 To 1000 ‘or perhaps 50?
DoEvents
Next
Result2 = MicroTimer - Tim
Call twenty ()
End Sub
Do While Now < starttime + 2.31481E-06 '1/5 second according to Excel Loop
StartTime = Now
Call MainSub
End Sub
Sub twenty() Dim outarr(1 To 1, 1 To 4) As Variant
inarr = Range("H45:H51")
cnt = Cells(42, 1)
If cnt = 10 Then
cnt = 1
End If
indi = 1
For i = 1 To 7 Step 2
outarr(1, indi) = inarr(i, 1)
indi = indi + 1
Next i
Application.EnableEvents = False
Range(Cells(cnt + 1, 38), Cells(cnt + 1, 41)) = outarr
cnt = cnt + 1
Cells(42, 1) = cnt
Application.EnableEvents = True
End Sub