How to record data from Bet Angel spreadsheet

Discussion regarding the spreadsheet functionality of Bet Angel.
nigelk
Posts: 352
Joined: Wed Apr 15, 2009 11:00 pm

Thu Aug 10, 2017 7:18 pm

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

User avatar
workpeter
Posts: 165
Joined: Sat Jul 30, 2016 8:29 pm

Fri Aug 11, 2017 9:44 pm

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
Posts: 122
Joined: Mon Dec 09, 2013 4:12 pm

Sat Aug 12, 2017 11:17 am

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

User avatar
workpeter
Posts: 165
Joined: Sat Jul 30, 2016 8:29 pm

Sun Aug 13, 2017 11:01 pm

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
Posts: 122
Joined: Mon Dec 09, 2013 4:12 pm

Mon Aug 14, 2017 11:58 am

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 :?
You do not have the required permissions to view the files attached to this post.

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

Mon Aug 14, 2017 12:29 pm

workpeter wrote:
Fri Aug 11, 2017 9:44 pm
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
Posts: 352
Joined: Wed Apr 15, 2009 11:00 pm

Mon Aug 14, 2017 1:33 pm

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
Posts: 122
Joined: Mon Dec 09, 2013 4:12 pm

Mon Aug 14, 2017 1:54 pm

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
Posts: 352
Joined: Wed Apr 15, 2009 11:00 pm

Mon Aug 14, 2017 3:40 pm

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)
You do not have the required permissions to view the files attached to this post.

User avatar
workpeter
Posts: 165
Joined: Sat Jul 30, 2016 8:29 pm

Mon Aug 14, 2017 11:07 pm

sa7med wrote:
Mon Aug 14, 2017 12:29 pm
workpeter wrote:
Fri Aug 11, 2017 9:44 pm
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

Post Reply
  • Information
  • Who is online

    Users browsing this forum: No registered users and 1 guest