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

There are two parts to my problem. Firstly, I have some 'logging' code from an example spreadsheet that I found from this forum in the past but am struggling to identify where it should be added around my existing code and secondly, after 30 iterations I want the code to 'forget' the values so to continuously only focus on the previous 30 seconds and not clog up my procession power with values from 4 hours ago that are now meaningless.

I am using the StateMachine spreadsheet that I previously posted a few weeks back so am just struggling to identify where the code should be placed.

The following code, from another user (Apologies, I usually like to give credit but can't remember who this came from), uses 'Sheet2' but I specifically want my logged Cells to be B40:K40 on 'Sheet1', you can see what I mean if you open up the attachment! (30 second iterations = B41:K71).

Code: Select all

Private Sub Worksheet_Calculate()

If Range("A4") = "TICK" And Range("A6") = 0 And Range("A1") = True Then Call TICK

If Range("A4") = "TOCK" And Range("A6") = 1 And Range("A1") = True Then Call TOCK
End Sub
And then in a separate module there is this code:

Code: Select all

Sub Record_data()
'
' Record_data Macro
'
Sheets("Data").Select
    Sheets("Data").Rows("5:5").Select
    Sheets("Data").Range("B5").Activate
    Selection.Copy
    Sheets("Data").Rows("7:7").Select
    Sheets("Data").Range("B7").Activate
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Data").Rows("7:7").Select
    Sheets("Data").Range("B7").Activate
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    
End Sub

Sub TICK()

Range("A6") = 1
Call Record_data

End Sub

Sub TOCK()

Range("A6") = 0
If Sheets("Data").Range("G1") = 1 Then Call Record_data

End Sub
Sub Delete_data()
'
' Delete_data Macro
'

'
Sheets("Data").Select
    Sheets("Data").Range("A8").Select
    Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
    Selection.ClearContents
    Sheets("Data").Range("A1").Select
End Sub
And then to make it all fit in, Sheet1 already has this code in place (I assume a LogSub would come immediately after StateMachineSIX?):

Code: Select all

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
 Application.EnableEvents = False 'Turn off events so changes to cells don't retrigger event
 Application.Calculation = xlCalculationManual ' set calculations to manual whilst code runs
 
Call StateMachineONE
Call StateMachineTWO
Call StateMachineTHREE
Call StateMachineFOUR
Call StateMachineFIVE
Call StateMachineSIX

 Application.Calculation = xlCalculationAutomatic 'turn on calculations
 Application.EnableEvents = True 'Turn back on events
End Sub
Side note:
How far down does the BA template (that this spreadsheet is built from) look for ‘Report’, ‘Odds’ and ‘Stake’ selections in columns L, M and N? I don't want to be accidentally firing in orders or confusing excel.

Have Tick/Tock happening in the invisible A column or cell I2 or I4.

so even if the above code can be modified to fit the space I have ready for the values to scroll between, I still have the problem of it only remembering the previous 30 seconds and not more.

Can anybody at least start me off in the right direction please?

Kind regards,

Callum.
BetAngel_Cal_Template.xls
You do not have the required permissions to view the files attached to this post.
CallumPerry
Posts: 575
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

Basically I'd like to record whatever is in the yellow row of cells for the next 30 seconds, but have settings adjustable.

I've looked in some VBA forums but they only really show timers for this type of code. Thought I'd post in here first as you guys actually understand the purpose of the end result I'm trying to achieve. As ever, once I've coded something that works I'm happy to share with other users :)

The line of code I can't seem to find is when it gets 30 seconds away, the system needs to stop tracking those values.

Again, any help please?

Callum.
sa7med
Posts: 800
Joined: Thu May 18, 2017 8:01 am

Havent looked at your sheet but a quick thought:

This would depend on how often your data is logged but you could have a counter (if 1 sec intervals then count to 30)
when it reaches 30 it will clear the content that is logged. You could use a cell for this, in the below example: A1

pseudocode:
'your logging code here

range("a1").value = range("a1").value + 1
if range("a1").value >=30 then LogRange.clearcontents

I believe that should reset your log sheet for you. hope that helps
CallumPerry
Posts: 575
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

Hi sa7med.

The problem with that is, it would delete the last 30 seconds entirely wouldn't it? I need it to delete just the 31st second ever second so each and every second it has the latest 30 seconds in time order with the most recent reading being at the top (Drinking game, have a shot each type I said second just then lol).

Another problem is if you just had a line of code that said clear 31st second then surely the logger would just keep ticking blank values down the page and slowing down the entire system? You see my problem? :cry:
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

What I used to do to get a rolling record of previous odds is to simply copy the old data to a range then enter the new data into the first cell of the range.

So lets say I want a rolling record of cell A1 and I want a copy of the last 30 values . For simplicity I'll save them starting at B1. All need to do to keep a rolling record is to copy the data from B1:B29 and stick it into B2:B30 then enter the last value into B1

i.e.

Code: Select all

sub copy()
Range("B2:B30").Value=Range("B1:B29").Value
Range("B1").Value=Range("A1").Value
end sub

Upon entering a new market I can easily clear the contents of that range and start again.

Like sa7med I haven't looked at the sheet as it's always a bit of a pain to try and decode someone else's code, it's just easier if you state exactly what you're looking to do rather than get people to second guess from a complete sheet.
CallumPerry
Posts: 575
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

So in my sheet code if I had a line to call the logger and then in a separate module I had something like:

Code: Select all

sub copy()

'Some line that says if 'time to event' is >= 'adjustable figure' i.e. 10 mins then activate, 10 minutes and 1 sec = clear and reload.
'If one second passes then everything in B40:K40 moves down the range B41:K71
Range("B41:B71").Value=Range("B40:B70").Value 'I don't understand the role of these two lines
Range("B40").Value=Range("A1").Value ' And this line? I've changed the cells from the last message!
if range("a1").value >=30 then LogRange.clearcontents 'A1? Again values from B40:K70 move down B41:K71 before disappearing!

end sub
I'm also going to need a specific cell going tick/tock or something to determine a second passing? Could anyone help me fill out the blanks please?
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

I have no idea what tick tock means. You can just run a sub routine by calling it by name anywhere in your code i.e. "Call SUB_ROUTINES_NAME" , not sure you can have a routine called "copy" to be honest, so best call it something else, I just typed up that example of how to do it thinking you'd be able to follow the code and amend it.



Here's a example of code you can use to log data every second , I have no idea what you're tracking so you will have to enter that data yourself in the range B40:K40.

Code: Select all

Sub CopyData()
If Range("M40").Value <> Range("B1").Value Then Range("B40:M70").ClearContents

If Time() >= Range("L40").Value + TimeValue("00:00:01") Then
    Range("B41:K70").Value = Range("B40:K69").Value
    Range("B40:K40").Value = YOU NEED TO ENTER THE DATA YOU'RE LOGGING HERE EITHER IN A RANGE OR EACH CELL SEPARATELY
    Range("L40").Value = Time()
    If Range("M40").Value = "" Then Range("M40").Value = Range("B1").Value
End If

End Sub
Because we're logging every second, not refresh, we need to keep a track of what time the last data was entered so a timestamp gets put in L40, change it's address if it's in the way. We will also log the current market so we can completely clear the data when we go to a new market, we'll log that in M40. I don't use BA but from what I can tell the market details are in cell B1, if they are not you will need to enter the correct cell into the code.

The code works as follows

Code: Select all

If Range("M40").Value <> Range("B1").Value Then Range("B40:M70").ClearContents
all that line does is check to see we haven't switched to a new market, if we have it simply erases the data and we start logging again into a clear range

Code: Select all

If Time() >= Range("L40").Value + TimeValue("00:00:01") Then
That line is checking that 1 second has passed since we last entered data, it's simply comparing the current time to the logged time in L40 + 1 second

Code: Select all

Range("B41:K70").Value = Range("B40:K69").Value
This line gives us our rolling record of previous data, it just copies the old data one step below itself, we then write the new data into the cells B40:K40 and timestamp it for the next time

Code: Select all

    Range("B40:K40").Value = YOU NEED TO ENTER THE DATA YOU'RE LOGGING HERE EITHER IN A RANGE OR EACH CELL SEPARATELY
    Range("L40").Value = Time()  

The last line of code is simply logging the market details if they've been cleared so we're able to track the market and clear data if we switch markets

Code: Select all

    If Range("M40").Value = "" Then Range("M40").Value = Range("B1").Value
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

CallumPerry wrote:
Fri Jun 15, 2018 9:07 am

The line of code I can't seem to find is when it gets 30 seconds away, the system needs to stop tracking those values.

From what I can see BA puts the event time in cell F3 so to find the current number of seconds, to/after the off, we can use the DateDiff function

Code: Select all

DateDiff("s", time(), Format(Range("F3").Value, "hh:mm:ss"))
The above gives us the difference in seconds between the current time and the time held in cell F3 so if you only want your code to run in a certain time slot i.e. 600 seconds out (10 minutes) and stop 30 seconds from the off you could use the following within your worksheet_change routine. It's up to you if you place it before or after all your other statemachine routines, that depends if you want a log of before your code has run or after the sub routines have run.

Code: Select all

If Range("F3").Value <> "" Then ' just there to error check that data is in F3 as I'm not sure of how BA enters data to excel
If DateDiff("s", Time(), Format(Range("F3").Value, "hh:mm:ss")) >= 30 And DateDiff("s", Time(), Format(Range("F3").Value, "hh:mm:ss")) < 600 Then
Call CopyData
End If
End If
CallumPerry
Posts: 575
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

As ever fantastic support spreadbetting, much appreciated!

I have just tried the code and I get:

Run-time error ‘13’:
Type mismatch

Debugging shows this line:

Code: Select all

If Time() >= Range("L40").Value + TimeValue("00:00:01") Then
Hovering over the line shows:

Code: Select all

If Time () = *Current Time
*Even when I move my house away and hover back over it keeps showing the current time accurately and I have tried with various refreshes of 20m/s : 500m/s

What's strange is that L40 is in fact showing the correct time up to the nearest second so why is it getting confused? Does there need to be an Else if line perhaps?
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

Works fine for me, maybe it's some settings on your PC. Type =TYPE(L40) into cell L41 see what type of data it's holding, 1 is number 2 is text. Mismatch 13 just means you're trying to add two incompatable data types , usually excel is very good at determining data types but will throw up errors if it thinks you're trying to add text to a number so you'd need to force the data into a type excel could understand.

Is it throwing the error with the time in L40 or without it in L40?

You can try this in the meantime as it should force the data to time

Code: Select all

Sub CopyData()
If Range("M40").Value <> Range("B1").Value Then Range("B40:M70").ClearContents

If Range("L40").Value = "" Then Range("L40").Value = Time() - TimeValue("00:00:01")


If Time() >= TimeValue(Range("L40").Text) + TimeValue("00:00:01") Then
    Range("B41:K70").Value = Range("B40:K69").Value
    Range("B40:K40").Value = YOU NEED TO ENTER THE DATA YOU'RE LOGGING HERE EITHER IN A RANGE OR EACH CELL SEPARATELY
    Range("L40").Value = Time()
    If Range("M40").Value = "" Then Range("M40").Value = Range("B1").Value

End If

End Sub
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”