Thread: logging data from dynamically chaning cells

I want to log data from a number of cells that have their values updated each second. I want to be able to produce graphs and averages over time of the values of these cells. how do I go about doing this please?

ok Its Betfair data logged into excell via the gruss betting assistant program. It produces data as provided in the attached sheet. Sheet 1 has data on the runners in the race and their prices and sheet 3 records betting activity on the race.
What I want to do is graph the changing prices of horses over time using column O as the data cell for each horse. I would also like a way to store this data for later review.
I also want to be able to record volume of the last trade on a horse, but that would involve both the last traded price column O and total volume column P, running a simple calculation on P to only use the change in volume. Would it be possible to have a second graph showing price over time which also incorporated the voume data?
I would like to log this data too for later review.

ok . Trawling the forum and cobbling together ideas from other answers I so far got this:

Code:

Sub logbetfair()
Dim PauseTime, Start, Finish, TotalTime
Dim openprice, closeprice, highprice, lowprice, theprice

checklog = True     'used to stop the logging via an interface button
PauseTime = 5       ' Set duration.
  
  Dim Arr As Variant    'to hold the data
  Static R As Long      'to control row increments
  
  Do While checklog
    ReDim Arr(1 To 1, 1 To 4)
      With Worksheets("Sheet1")
        theprice = .Range("O5")
        Arr(1, 1) = theprice    'set open price
        highprice = theprice
        lowprice = theprice
        Start = Timer    ' Set start time.
        Do While Timer < Start + PauseTime
            DoEvents    ' Yield to other processes.
            If highprice < theprice Then
                highprice = theprice        'set high price
            End If
            If lowprice > theprice Then
                lowprice = theprice         'set low price
            End If
        Loop
    
        Arr(1, 2) = highprice
        Arr(1, 3) = lowprice
        Arr(1, 4) = theprice        'set close price
        
        ' logging the data. Current row equals row 1 plus the value in R.
        Worksheets("Sheet2").Range("A1:D1").Offset(R, 0).Value = Arr

        'Increment the row offset value
        R = R + 1
      End With
    Loop

End Sub

This will log a single cell into 4 columns creating open, high, low, close info I can get a candle graph from. Problems now are the candle graph simply stops when it reaches the far right of the graph area and does not update. Any way to fix this? Is there a programable way to create graphs ? Are there graphing packages that will link to an excel file in real time? Are there excel graphing packages and user created stuff available?

tidied that up a bit and added a volume column .....

Code:

Sub logbetfair(ltpcell As String, volumecell As String, period As Integer)
Dim PauseTime, Start
Dim openprice, closeprice, highprice, lowprice, theprice, startvol
' ltpcell is the last traded price cell , volumecell is the total volume cell and period is the time of a candle
checklog = True     'used to stop the logging via an interface button
PauseTime = period      ' Set duration.
  
  Dim Arr As Variant    'to hold the data
  Static R As Long      'to control row increments
  
  Do While checklog
    ReDim Arr(1 To 1, 1 To 4)
      With Worksheets("Sheet1")
        theprice = .Range(ltpcell)
        startvol = .Range(volumecell)
        Arr(1, 1) = theprice    'set open price
        highprice = theprice
        lowprice = theprice
        Start = Timer    ' Set start time.
        Do While Timer < Start + PauseTime
            DoEvents    ' Yield to other processes.
            theprice = .Range(ltpcell)
            If highprice < theprice Then
                highprice = theprice        'set high price
            End If
            If lowprice > theprice Then
                lowprice = theprice         'set low price
            End If
        Loop
    
        Arr(1, 2) = highprice
        Arr(1, 3) = lowprice
        Arr(1, 4) = theprice        'set close price
        
        ' logging the data. Current row equals row 1 plus the value in R.
        Worksheets("Sheet2").Range("A1:D1").Offset(R, 0).Value = Arr
        Worksheets("Sheet2").Range("E1").Offset(R, 0).Value = .Range(volumecell) - startvol

        'Increment the row offset value
        R = R + 1
      End With
    Loop

End Sub

If you want to test the procedure yourself goto http://www.gruss1-software.co.uk/ and download betting assistant. Open up a uk horse race and a clean workbook and use the excell menu entry to link the race to excel . Add a couple of simple buttons to turn off and on logging and add procs to link to them...

Code:

Sub stoplog()
checklog = False
End Sub
Sub button1()
Call logbetfair("O5", "P5", 20)
End Sub

Great thanks.

How do I assign a names range thats dynamic for adding volume chart ? I cant find it in any of the properties