Recording a current cell value in excel

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
StellaBot
Posts: 818
Joined: Thu Jan 26, 2017 11:52 am

Any help please?

I think something similar has been asked in the forum using signals etc to capture prices, vol, ltp etc.
But can a current cell value at specific time be copied eg 2pm A1 =10 2.01 pm A1 =11.
Can A2 retain value 10 and A3 retain value 11.
Have read many articles on excel clock and VBA.
Ive googled and searched various forums e.g mr excel.
But some answers in each forum say yes and others no.
links to some perused

https://www.quora.com/How-can-I-record- ... nt-in-time

https://stackoverflow.com/questions/285 ... cific-time

https://www.extendoffice.com/documents/ ... value.html

I already have a "prototype" spreadsheet in mind but the above would be the main stumbling block.
The spreadsheet wouldnt need updating in milliseconds maybe eg every minute or 5mins
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

It's very easy to maintain an audit trail in excel with VBA either in memory or saved to the sheet and the data can easily be timestamped or set to save at regular intervals.

I posted some example code on this thread

viewtopic.php?f=19&t=16663&p=158026#p158032


With regards to specific times you're limited to the refresh rate Bet Angel is sending the data to excel at as I don't think you can force BA to fetch the latest prices more the fact that you have to be receiving the data at a reasonable speed for your needs if that makes sense to you. i.e. if you want to record the data at 2.00pm you set your code to grab the data once the time is equal to or after 2.00pm so if your refresh rate is every 30 seconds your data could be grabbed at 14:00:29 rather than 14:00:00. Once the data is grabbed (i.e. <>"") the code would stop so you don't overwrite it

Your code would simply be something like

Code: Select all

If Time() >= TimeValue("14:00:00") And Range("A2").Value = "" Then Range("A2").Value = Range("A1").Value
If Time() >= TimeValue("14:01:00") And Range("A3").Value = "" Then Range("A3").Value = Range("A1").Value
StellaBot
Posts: 818
Joined: Thu Jan 26, 2017 11:52 am

Hi SB
Thanks for reply.
Looks like I will have to ask someone for assistance( or pay someone)
Thought I could just copy and paste your example vba into a cell
Forgot its more complicated than that!!
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

You could find some empty cells and enter
=IF(Time() =TimeValue("14:01:00") , B9,0)

=IF(Time() =TimeValue("15:01:00"), B9,0)

Etc

B9 goes into each cell at the specified time otherwise it shows zero.
StellaBot
Posts: 818
Joined: Thu Jan 26, 2017 11:52 am

Thanks Shaun will give it try
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

StellaBot wrote:
Tue Jul 10, 2018 8:02 pm
Hi SB
Thanks for reply.
Looks like I will have to ask someone for assistance( or pay someone)
Thought I could just copy and paste your example vba into a cell
Forgot its more complicated than that!!
VBA isn't that complicated but if you want to do it with formulas you need to use circular references and enable iterative calculations, might be simpler to learn a little VBA to be honest
CallumPerry
Posts: 575
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

I've recently been on holiday so haven't finished the code from the thread spreadbetting linked to but should get it done in the next few days StellaBot. Keep your eye on the forum, hopefully I create something that you will find useful. Not exactly what you're looking for but with the other poster's comments you may be able to figure out the rest :)
StellaBot
Posts: 818
Joined: Thu Jan 26, 2017 11:52 am

Thanks all
Basically Im using the bet angel data capture sheet
F3=start time
C3 = time now
G9=present back price of fav

so for example If Race time was 14:00 and Time now is 13:55
I would like value of G9 in AF9 and not to change
Then at 13:56 value of G9 in AG9 and not to change

The remainder of my spreadsheet I have already worked out in my head.
Its the "formula/solution" for AF9

Only piece of jigsaw I need

Thanks again
spreadbetting
Posts: 3140
Joined: Sun Jan 31, 2010 8:06 pm

I can't fully test this as I don't have BA but it should work, looks more complicated than it is , if you need any part explaining just ask

Basically press Alt + F11 in excel to bring up the VBA screens or access it via the developer tab in excel if you have that enabled. In the left hand side you'll see the vba project window double click on the line that probably says Sheet1 Bet Angel and paste the following code into that window. Hopefully it'll work first time otherwise it'll at least get you started

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
Static market As String
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual

With Target.Parent

If .Range("B1").Value <> market Then

.Range("AF9:AG9").Value = ""
market = .Range("B1").Value

Else

If .Range("AF9").Value = "" And Time() >= .Range("F3").Value - TimeValue("00:05:00") Then .Range("AF9").Value = .Range("G9").Value
If .Range("AG9").Value = "" And Time() >= .Range("F3").Value - TimeValue("00:04:00") Then .Range("AG9").Value = .Range("G9").Value

End If

End With

Application.EnableEvents = True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub
StellaBot
Posts: 818
Joined: Thu Jan 26, 2017 11:52 am

Hi

Been studying vba via books and over internet
I wonder if anyone could comment on this please?
I know its weekend but answer anytime eg next week onwards is fine.
Thank you.

I may have to pay some money or share some of my own system secrets ;)



Dim RunWhen As Double ' time that timer is set to expire
Const TimerIntervalSeconds As Double = 60 * 5 ' the amount of time in seconds to wait from
' when the timer is set to when it expires


Public Sub StartTimer()

SetNextTimer

End Sub

Public Sub SetNextTimer()

' Convert seconds to fraction of a day
RunWhen = Now + TimerIntervalSeconds / (CLng(24) * CLng(60) * CLng(60))
Application.OnTime _
EarliestTime:=RunWhen, _
Procedure:="TimedAction", _
Schedule:=True

End Sub


Public Sub StopTimer()

On Error Resume Next

Application.OnTime _
EarliestTime:=RunWhen, _
Procedure:="TimedAction", _
Schedule:=False

End Sub

' You can name this anything you want. Update the call to OnTime above to match.
Public Sub TimedAction()

Static count As Long

SetNextTimer

' Insert code here to copy data from column G to next available column starting in AG

End Sub
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”