How to record data from Bet Angel spreadsheet

[nigelk]

Theres a routine in module 1

Code: Select all

Sub StartTimer()
    dTime = Now + TimeValue("00:00:01")
    Application.OnTime dTime, "ValueStore", Schedule:=True
End Sub

which calls this routine once a second.

Code: Select all

Sub ValueStore()
Dim dTime As Date
    ' Count of non empty data cells for this market
    'ThisWorkbook.Sheets("ChartView").Range("$S$34").Value = i
    Call StartTimer
    i = i + 1
    ' If there are no more data cells, or if the next cell is blank
    If i = ThisWorkbook.Sheets("ChartView").Range("YA19").Value - 6 Then
        Call StopTimer
        Sheets("ChartView").PlayButton.Caption = "Play"
    Else
    ThisWorkbook.Sheets("ChartView").Range("$S$34").Value = i
    End If
End Sub

Then theres the routine to stop it recording:

Code: Select all

Sub StopTimer()
    On Error Resume Next
    Application.OnTime dTime, "ValueStore", Schedule:=False
    i = 1
    ' Reset to the oldest Time
    ThisWorkbook.Sheets("ChartView").Range("$S$34").Value = 1
End Sub

To create and record you own data once every 5 seconds it would be along the lines of:

Code: Select all

Sub StartTimer()
    dTime = Now + TimeValue("00:00:05")
    Application.OnTime dTime, "RecordMyData", Schedule:=True
End Sub

Code: Select all

Sub RecordMyData()


'put your own code here

''''''''''''''''''

''''''''''''''''''

'etc etc

Call StartTimer


End Sub

[workpeter]

For those of you wishing to take your excel to the next level I would suggest you look at GetSystemTime in combination with worksheet change event. This is more granular than timers and doesnt cause your application to freeze.

[RonObvious]

Thanks to everyone who took the time to respond to my query. With your help (especially NIgelK) I've got a better understanding now of how it works.

One last thing though is it possible to plot the data into a real time chart? Like my own version of the market overview chart?

I've checked online and have seen some videos where this is possible but they all seem to need some other software and look very complicated.

Just to be clear I'm looking I'd like to be able to plot the updated date into a chart in real time.

Thanks

[workpeter]

you could have a fixed range with the data updating dynamically within that range or you could look at dynamic data range. Personally fixed range is more simple and easy to interprete.

Let's assume you data ranges across 60 columns, I would make the last column (60) update every interval with fresh data then copy from range 2 to 60 to range 1 to 59. You do this in VBA. Visualise for a few minutes how this will play put. You will see you data within the range consistents always of the last 60 intervals. Your graph looking at this range willl show this. In other words if each interval was 1 second, the graph will always show the last 60 seconds of data thus dynamically updating.

[RonObvious]

Hi Workpeter,

I've been trying to do something similiar with the Bet Angel spreadsheet. I attached a copy of it that another forum user modified so that when its connected to excel and the 'Tick to record' function on the data sheet is enabled it copies data from Row 5 every second. I've tried to capture this data as its updating and populating rows below using fixed ranges and populate a line chart in real time but it wont work, the chart just gets pushed down the sheet as another row of data appears.

I thought something like this would be relatively simple but the only video I could find that shows what I'm trying to do is this one : https://youtu.be/xLTEnd1u5Eo

And that seems to suggest its very complex

[sa7med]

For those of you wishing to take your excel to the next level I would suggest you look at GetSystemTime in combination with worksheet change event. This is more granular than timers and doesnt cause your application to freeze.

Hey workpeter, would you be willing to elaborate more about how this could be done? Im using switesh's sheet which tends to freeze up and eat up resources. I'm a novice at VBA but am learning and getting better.

[nigelk]

the chart just gets pushed down the sheet as another row of data appears.

Select the chart, then on the ribbon under "Chart Tools", select "Format"

On the far right hand side you'll see a box giving the height and width. In the bottom right hand corner is a tiny box/arrow.

Click on this and you'll get a pop up box. Choose the properties tab and select "don't move or size with cells"

[RonObvious]

Thanks NIgel this fixed the problem of the chart moving but unfortunately the range I've selected is still moving down under the data. I'm guessing it must be something to do with how the sheet is coded or something

[nigelk]

Excel updates any formulas/named range references when you insert/delete any rows/columns.

To overcome this when a new row/column is inserted, you need to update the chart so it still points to the original range.

On the attached on sheet 1 is a routine that calls the code in module 1 everytime a new row is inserted (row 2 only, change to suit or use the column ref instead).

You'll probably need to change the chart name if, like me, you've had half a dozen or more previous charts that you've deleted. Can easily get up to "Chart 20" or thereabouts!

If you're not sure of the name, select it and look at the name box (left of the formula bar below ribbon)

[workpeter]

For those of you wishing to take your excel to the next level I would suggest you look at GetSystemTime in combination with worksheet change event. This is more granular than timers and doesnt cause your application to freeze.

Hey workpeter, would you be willing to elaborate more about how this could be done? Im using switesh's sheet which tends to freeze up and eat up resources. I'm a novice at VBA but am learning and getting better.

The worksheet change event will trigger everytime BA communicates with excel. Even with streaming I don't get 20ms, more like 70ms, but thats still good. Within this routine use a conditional statement to compare the current system time to the captured system time. Pseudo code:

Begin Sub routine change event
IF CurrentSystemTime >= oldSystemTime + 100ms
{
//Do some code every 100 ms
//Then update global variable
OldSystemTime = CurrentSystemTime
}
End Sub Routine

[workpeter]

Hi Workpeter,

I've been trying to do something similiar with the Bet Angel spreadsheet. I attached a copy of it that another forum user modified so that when its connected to excel and the 'Tick to record' function on the data sheet is enabled it copies data from Row 5 every second. I've tried to capture this data as its updating and populating rows below using fixed ranges and populate a line chart in real time but it wont work, the chart just gets pushed down the sheet as another row of data appears.

I thought something like this would be relatively simple but the only video I could find that shows what I'm trying to do is this one : https://youtu.be/xLTEnd1u5Eo

And that seems to suggest its very complex

I'm not at home so cannot open your spreadsheet fully. However our approaches are somewhat different. If you spend some time investigating my solution you will find it will not have the issues you describe. In a nutshell it's overwriting the same cells so nothing moves around distorting formula and positioning. Also there's nothing preventing you using a blend of both solutions. Your solution of adding rows of data will ensure you have an entire history and them elsewhere on the spreadsheet you use my solution which focuses on the last X number of iterations to which your graph points to.

[RonObvious]

Excel updates any formulas/named range references when you insert/delete any rows/columns.

To overcome this when a new row/column is inserted, you need to update the chart so it still points to the original range.

On the attached on sheet 1 is a routine that calls the code in module 1 everytime a new row is inserted (row 2 only, change to suit or use the column ref instead).

You'll probably need to change the chart name if, like me, you've had half a dozen or more previous charts that you've deleted. Can easily get up to "Chart 20" or thereabouts!

If you're not sure of the name, select it and look at the name box (left of the formula bar below ribbon)

Hi Nigel,

I'm unable to open the file, nothing happens when I double click it.

[nigelk]

Needs to be unzipped, you should get a box pop up when you double click it?

[RonObvious]

I've had to open excel and try to open the file from within Excel but I'm getting the attached pop up box.

Not sure what the issue is but I've tried re-installing MS Office and still nothing.

[RonObvious]

Not sure if the file is corrupted or not but when I open it as you can see in the preview box of the screen grab I sent its just random symbols.