Add a 30 Sec Logger to my WorkSheet
-
- Posts: 575
- Joined: Wed Apr 19, 2017 5:12 pm
- Location: Wolverhampton
Update: When I reopened the file to test it one last time before submitting this message it is working with nothing in M40 (though my previous question about knowing when a market changes is still standing), now it's doing another strange thing...
When I click on the manual button I have added it uploads two cells at a time per second to start with. Any idea why it's doing this please? I've uploaded the spreadsheet to this message. The code is in module 1 and is called from sheet 1 ... module (I think it's called?)
Would appreciate anyone looking over it for me, testing and seeing if you can spot any other flaws. Once the cracks have been smoothed out I'll share it for others to use.
You do not have the required permissions to view the files attached to this post.
-
- Posts: 3140
- Joined: Sun Jan 31, 2010 8:06 pm
You can change the code so that it'll stick the current market into M40 if it's different to B1, so M40 shouldn't ever be blank or if it is it'll make no difference.CallumPerry wrote: ↑Mon Jul 30, 2018 6:18 pmBA Rolling Logger Test.xls
Bit of a delayed reply, I was working on this before my holiday and only just remembered to pick it back up again! I think it is working now but seems to be a bit temperamental when there isn't something inside cell M40, what should go in here exactly to help determine when there's a change between markets?
Update: When I reopened the file to test it one last time before submitting this message it is working with nothing in M40 (though my previous question about knowing when a market changes is still standing), now it's doing another strange thing...
When I click on the manual button I have added it uploads two cells at a time per second to start with. Any idea why it's doing this please? I've uploaded the spreadsheet to this message. The code is in module 1 and is called from sheet 1 ... module (I think it's called?)
Would appreciate anyone looking over it for me, testing and seeing if you can spot any other flaws. Once the cracks have been smoothed out I'll share it for others to use.
When you fire off some code manually with a button because that code changes data on the sheet it'll also trigger off your worksheet_change routine so the logger routine runs twice, to stop that you can simply turn off and on events within the logger routine itself. The rest of it I have no idea as I don't have BA so cant test it for you. If you took a little time to understand the code you'd be able to do these tweaks yourself.
Code: Select all
Sub Logger()
Application.EnableEvents = False 'Turn off events so changes to cells don't retrigger event
Application.Calculation = xlCalculationManual ' set calculations to manual whilst code runs
If Range("M40").Value <> Range("B1").Value Then Range("B40:L71").ClearContents:Range("M40").Value = Range("B1").Value
If Range("B39").Value = "" Then Range("B39").Value = Time() - TimeValue("00:00:01")
If Time() >= TimeValue(Range("B39").Text) + TimeValue("00:00:01") Then
Range("B40:K71").Value = Range("B40:K71").Value
Range("B41:K71").Value = Range("B40:K70").Value
Range("B39").Value = Time()
End If
Application.Calculation = xlCalculationAutomatic 'turn on calculations
Application.EnableEvents = True 'Turn back on events
End Sub
Must admit I'm not sure why you have a line
Code: Select all
Range("B40:K71").Value = Range("B40:K71").Value
-
- Posts: 575
- Joined: Wed Apr 19, 2017 5:12 pm
- Location: Wolverhampton
Fantastic! I'll try out the on/off events code when I get chance in the next few days, in the meantime please feel free to download the spreadsheet and see if it helps you with your automated trading ideas. Let me know if you run into any bugs (no matter how minor) and I can try to tweak the code.
I have no idea why I have that line either :') I usually only have time to learn about the markets, coding and general trading learning late at night after a full day at work! Looking forward to the day when I am good enough to do this full-time!Must admit I'm not sure why you have a line