Add a 30 Sec Logger to my WorkSheet

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
CallumPerry
Posts: 575
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

BA 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.
You do not have the required permissions to view the files attached to this post.
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

CallumPerry wrote:
Mon Jul 30, 2018 6:18 pm
BA 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.
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.

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
in your code as it doesn't do anything
CallumPerry
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.
Must admit I'm not sure why you have a line
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!
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”