Streaming charts:

rm2010

New member
Joined
Oct 13, 2011
Messages
11
Reaction score
0
Points
0
Hi All

Unfortunately I know very very little about VBA. I'd be most grateful for some help.

Here's the challenge:

I have a VBA enabled data sheet supplied by my broker (Interactive brokers).

I created a new sheet. In cell G4 and G5 I have two streaming prices (which is grabs from another sheet).
In cell C10 I have the equation "G4-G5" which gives me another streaming variable.

Now; if I chart cell C10 all I get (or should get) is a jumping dot. As the data changes in that single cell the chart jumps about.

I want a streaming chart.

Furthermore, I need the answer explaining in dummy language abcd :(. I'm using office 2007

Thanks people
 
Is there any way you can post a sample of what you have so we can look at it? I'm not quite following here... When you say streaming, is the data automatically refreshing? Charts don't need VBA to refresh, they should constantly update based on the data inputs...
 
Thank you for your response Ken

Unfortunately the file is so large and requires a link to interactive brokers for it to function so a sample couldn't work. However, yes you are exactly right; Cell C10 has streaming information. It's connected to the stock exchange and has a fluctuating real-time price. The number is just randomly changing every second/milisecond. Imagine you're looking at a stock market price...it's a single cell flashing a new price every second as it changes. Its dynamic and not static. The cell is automatically updating itself every second. I hope this is clearer?
 
So what you're looking to do is preserve the history to chart it? We can probably do that, but the issue is that it will only chart the history that is captured while the workbook is open. If it closes for any reason, the period until it opens again would be lost. Are you OK with that?
 
Yes that's absolutely fine. In fact thats exactly what I need. Here's what I do at the moment

Load up my excel sheet
Enable macros
Switch on DDE feeds into excel.
Observe streaming stock price

So yes, I need to capture the history of that single cell. Imagine the price in cell A1 flashes $1.05, $1.04, $1.03, $1.02, $1.01, $1.02, $1.03, $1.04, $1.05 - I want to see a chart with a V shape. This would be easy if that information was in cell A1:A9, but it isn't. It's all flashed away in the last two minutes in cell A1.

Sorry if I'm over-explaining.

Thanks Ken
 
Okay, some questions:

How often does the cell update?
How often do you need to capture the data?
When the worbook opens, do you want to clear the existing chart and start over?
 
Hi Ken

The cell is streaming, so it could update anywhere between upto 1/10th of a second or even more. It could literally have 10 different prices in less than a second. If that's an issue, I'm happy to capture prices every second. Or better yet, set the time frame of capture. Now that'd be true jedi genius.

Yes when I open the workbook I would need the functionality to view the previous chart and then have the options to either 1) Continue previous session (as if I never closed) or 2) clear and start again

Genuinely appreciate your help Ken
 
Okay, I'll work on this a bit over the weekend if I get some time, but...

I can't do it on refresh, as I don't have a way to tap into their refresh cycle, so it needs to be done on time. Setting up to capture 10 times per second means you would be capturing up to 864,000 records per 24 hours... even with Excel 2007, you'd only be able to capture 1.2 days of data. Moving back to 1 capture per second you'd be able to get up to 12 days.

I can get the data captured and appended to a data table, the only concern I've got here is that code will run every second. That means you'll need to run this in an Excel instance separate to any other Excel use you need to do or the program could essentially be rendered unusable, since it's constantly working.
 
Okay, so let's give this a go...

First thing is that you need to get this code in the right place, then makes some edits. So the first thing you need to do is press Alt+F11 to go into the VBE. Find your file in the project explorer (on the left) and drill right down into it's ThisWorkbook module. Once the blank code pane opens up, paste in the following:

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'Stop workbook refreshing    
Call Chart_Stop
End Sub

Now, right click the ThisWorkbook object in the explorer (or one of the other objects) and choose Insert-->Module. In the blank code pane, paste (all of... don't forget to scroll down) the following:
Code:
Option Explicit
'Update the values between the quotes here:
Private Const sChartWSName = "Chart"
Private Const sSourceWSName = "Sheet1"
Private Const sTableName = "tblValues"
Public RunTime As Double
Private Sub Chart_Setup()
'Create the structure needed to preserve and chart data
    Dim wsChart As Worksheet
    Dim lstObject As ListObject
    Dim cht As Chart
    Dim shp As Button
    'Create sheet if necessary
    Set wsChart = Worksheets.Add
    wsChart.Name = sChartWSName
    'Set up listobject to hold data
    With wsChart
        .Range("A1").Value = "Time"
        .Range("B1").Value = "Value"
        Set lstObject = .ListObjects.Add( _
                        SourceType:=xlSrcRange, _
                        Source:=.Range("A1:B1"), _
                        xllistobjecthasheaders:=xlYes)
        lstObject.Name = sTableName
        .Range("A2").NumberFormat = "h:mm:ss AM/PM (mmm-d)"
        .Columns("A:A").ColumnWidth = 25
        .Select
    End With
    'Create the chart
    With ActiveSheet
        .Shapes.AddChart.Select
        Set cht = ActiveChart
        With cht
            .ChartType = xlXYScatter
            .SetSourceData Source:=Range(sTableName)
            .PlotBy = xlColumns
            .Legend.Delete
            .Axes(xlCategory).MajorUnit = 1 / 24 / 60    'Major Unit = 1 Hour
            .Axes(xlCategory).MinorUnit = 1 / 24 / 60 / 60    'Minor Unit = 1 Minute
        End With
    End With
    'Add buttons to start/stop the routine
    Set shp = ActiveSheet.Buttons.Add(242.25, 0, 83.75, 33.75)
    With shp
        .OnAction = "Chart_Initialize"
        .Characters.Text = "Restart Plotting"
    End With
    
    Set shp = ActiveSheet.Buttons.Add(326.25, 0, 83.75, 33.75)
    With shp
        .OnAction = "Chart_Stop"
        .Characters.Text = "Stop Plotting"
    End With
End Sub
Public Sub Chart_Initialize()
'Initialize the routine
    Dim wsTarget As Worksheet
    Dim lstObject As ListObject
    'Make sure worksheet exists
    On Error Resume Next
    Set wsTarget = Worksheets(sChartWSName)
    If Err.Number <> 0 Then
        Call Chart_Setup
        Set wsTarget = Worksheets(sChartWSName)
    End If
    On Error GoTo 0
    'Check if chart data exists
    With Worksheets(sChartWSName)
        Set lstObject = .ListObjects(sTableName)
        If lstObject.ListRows.Count > 0 Then
            Select Case MsgBox("You already have data.  Do you want to clear it and start fresh?", vbYesNoCancel, "Clear out old data?")
                Case Is = vbYes
                    'User wants to clear the data
                    lstObject.DataBodyRange.Delete
                Case Is = vbCancel
                    'User cancelled so exit routine
                    Exit Sub
                Case Is = vbNo
                    'User just wants to append to existing table
            End Select
        End If
        'Begin appending
        Call Chart_AppendData
    End With
End Sub
Private Sub Chart_AppendData()
'Append data to the chart table
    Dim lstObject As ListObject
    Dim lRow As Long
    With Worksheets(sChartWSName)
        Set lstObject = .ListObjects(sTableName)
        If lstObject.ListRows.Count = 0 Then
            lRow = .Range("A1").End(xlDown).Row
        End If
        If lRow = 0 Then
            lRow = .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).Row
        End If
        .Range("A" & lRow).Value = CDate(Now)
        .Range("B" & lRow).Value = Worksheets(sSourceWSName).Range("C10").Value
    End With
    RunTime = Now + TimeValue("00:00:01")
    Application.OnTime RunTime, "Chart_AppendData"
End Sub
Public Sub Chart_Stop()
'Stop capturing data
    On Error Resume Next
    Application.OnTime EarliestTime:=RunTime, Procedure:="Chart_AppendData", Schedule:=False
End Sub

Now it's time to make a couple of edits... at the top of this module, notice the following:
Code:
'Update the values between the quotes here:
Private Const sChartWSName = "Chart"
Private Const sSourceWSName = "Sheet1"
Private Const sTableName = "tblValues"

What I need you to do is change these to what you want to use in your workbook. Currently, this routine will set up a sheet called "Chart" to chart the data. If you already have a sheet called Chart, or want to change it to something else, then just change it on that line. Make sure you leave the quotes surrounding it though.

I don't know what sheet your C10 cell is on, so I built this to refer to "Sheet1". If it's on a different sheet, just change the name there as well. Again, leave the quotes.

This file sets up an Excel list to hold the data, and the chart is built off that list. I called it tblValues, but you can change it there if you like.

Once you've got all that in place, then:
  • Go back to Excel
  • Save the file
  • Press Alt+F8
  • Choose Chart_Intitiliaze
  • Click Run
Let me know how it works. (I also attached a sample from my interpretation using a random number as the stock price.
 

Attachments

  • xlgf482-1.xlsm
    22.3 KB · Views: 232
Last edited:
Ken thank you so much for this. I didn't get an alert to log in. Will be giving this my all day on Monday and let you know how I get on. Thanks again
Riz
 
Wow

not sure what I've done but its working

What would be good now is a line chart Ken! -

This is just great! You've taught a man how to fish!
 
Last edited:
sorry I mean a line chart and not a scatter chart. ideally with a 1.25pts thickness.

Thanks Ken
 
Try replacing the entire "Chart_Setup" routine with the following and see if looks better. :)

Code:
Private Sub Chart_Setup()
'Create the structure needed to preserve and chart data
    Dim wsChart As Worksheet
    Dim lstObject As ListObject
    Dim cht As Chart
    Dim shp As Button
    'Create sheet if necessary
    Set wsChart = Worksheets.Add
    wsChart.Name = sChartWSName
    'Set up listobject to hold data
    With wsChart
        .Range("A1").Value = "Time"
        .Range("B1").Value = "Value"
        Set lstObject = .ListObjects.Add( _
                        SourceType:=xlSrcRange, _
                        Source:=.Range("A1:B1"), _
                        xllistobjecthasheaders:=xlYes)
        lstObject.Name = sTableName
        .Range("A2").NumberFormat = "h:mm:ss AM/PM (mmm-d)"
        .Columns("A:A").ColumnWidth = 25
        .Select
    End With
    'Create the chart
    With ActiveSheet
        .Shapes.AddChart.Select
        Set cht = ActiveChart
        With cht
            .ChartType = xlLine
            .SetSourceData Source:=Range(sTableName)
            .PlotBy = xlColumns
            .Legend.Delete
            .Axes(xlCategory).CategoryType = xlCategoryScale
            With .SeriesCollection(1).Format.Line
                .Visible = msoTrue
                .Weight = 1.25
            End With
        End With
    End With
    'Add buttons to start/stop the routine
    Set shp = ActiveSheet.Buttons.Add(242.25, 0, 83.75, 33.75)
    With shp
        .OnAction = "Chart_Initialize"
        .Characters.Text = "Restart Plotting"
    End With
    Set shp = ActiveSheet.Buttons.Add(326.25, 0, 83.75, 33.75)
    With shp
        .OnAction = "Chart_Stop"
        .Characters.Text = "Stop Plotting"
    End With
End Sub
 
excellent

Ken

Even if I was a Jedi I'd wanna be like you. None of that stuff makes any sense to me but given that I know ctrl C / ctrl V - its done the job!

Are there any online idiots guide type courses for this stuff? Or could you maybe recommend a book which is for 10 levels below novice VBA?

Genuinely appreciate your help here - Thank you

RM
 
Even if I was a Jedi I'd wanna be like you.

LOL! That's pretty high praise!

Are there any online idiots guide type courses for this stuff? Or could you maybe recommend a book which is for 10 levels below novice VBA?

For sure! Actually, have a look here: http://www.excelguru.ca/forums/content.php?213-Books The VBA For Dummies might be a good start. The Power Programming book is awesome, but you might want a bit more background before you jump into it. Couple those with a good forum and you'll be off to the races in no time!
 
Ken,

I'm also fairly new to VB and I too have a similar need - I found this thread, copied the code and am only getting a new sheet called "Chart" and a list at the top with Columns being populated for Time and Value, with a List Element taking up the top 3 rows...

Its populating the data I chose from the selected worksheet and cell, however the cell I chose is a value cell for stocks (like the original author) but the data worksheet goes out 6 to 8 decimal places for the value indicated - very important to see these small changes for my calculations...

It is currently populating the list on "Chart" with a $XX.XX value
The Time is incrementing up for each value based on actual time - so that looks like its working...

I clicked on the list element, then I added a chart - line chart...

In the Chart Wizard that popped up

1 - I selected Line Chart
2 - I selected the basic Line chart subtype
3 - Step 2 was pre-filled
the rest I left alone...

I get a chart that has the values but only for what is already saved - not continuing to add new data plots as the Chart worksheet populates

Also - having trouble displaying time axis in legible format for 3d chart. I have several streaming quotes I would like to watch on the same chart and do some data manipulations to so I can watch for movements of the same... basically I am connecting to my brokerage account, importing my watchlist of several stock symbols which then loads excel with the streaming quote for that second for the watchlist...

On a separate sheet I take the totals of each section, then divide for an average value of the entire set of stocks. This field I want to track - its updated as quickly as the data arrives and changes the source data page of streaming quotes.

I also have several other calculations such as difference between stocks and various algorithmic sums calculated on the fly on my "Calc" worksheet.

I'd like to chart all of these calculated values on a single or on multiple charts... watching for overlaps and crosses.

Any ideas on how to do this? I think this streaming quotes setup is very close to what I am looking for but am just not getting it to work quite right...
 
Can you please post a complete script with the edits mentioned above - I may have copied over some of the script that is not supposed to be copied over or I am simply not seeing something simple to get the chart to display...
 
This is what I have:

Option Explicit
'Update the values between the quotes here:
Private Const sChartWSName = "Chart"
Private Const sSourceWSName = "Calc"
Private Const sTableName = "tblValues"
Public RunTime As Double
Private Sub Chart_Setup()
'Create the structure needed to preserve and chart data
Dim wsChart As Worksheet
Dim lstObject As ListObject
Dim cht As Chart
Dim shp As Button
'Create sheet if necessary
Set wsChart = Worksheets.Add
wsChart.Name = sChartWSName
'Set up listobject to hold data
With wsChart
.Range("A1").Value = "Time"
.Range("B1").Value = "AvgValue"
.Range("C1").Value = "Diff"
Set lstObject = .ListObjects.Add( _
SourceType:=xlSrcRange, _
Source:=.Range("A1:B1"), _
xllistobjecthasheaders:=xlYes)
lstObject.Name = sTableName
.Range("A2").NumberFormat = "h:mm:ss AM/PM (mmm-d)"
.Columns("A:A").ColumnWidth = 25
.Select
End With
'Create the chart
With ActiveSheet
.Shapes.AddChart.Select
Set cht = ActiveChart
With cht
.ChartType = xlLine
.SetSourceData Source:=Range(sTableName)
.PlotBy = xlColumns
.Legend.Delete
.Axes(xlCategory).CategoryType = xlCategoryScale
With .SeriesCollection(1).Format.Line
.Visible = msoTrue
.Weight = 1.25
End With
End With
End With
'Add buttons to start/stop the routine
Set shp = ActiveSheet.Buttons.Add(242.25, 0, 83.75, 33.75)
With shp
.OnAction = "Chart_Initialize"
.Characters.Text = "Restart Plotting"
End With
Set shp = ActiveSheet.Buttons.Add(326.25, 0, 83.75, 33.75)
With shp
.OnAction = "Chart_Stop"
.Characters.Text = "Stop Plotting"
End With
End Sub
Public Sub Chart_Initialize()
'Initialize the routine
Dim wsTarget As Worksheet
Dim lstObject As ListObject
'Make sure worksheet exists
On Error Resume Next
Set wsTarget = Worksheets(sChartWSName)
If Err.Number <> 0 Then
Call Chart_Setup
Set wsTarget = Worksheets(sChartWSName)
End If
On Error GoTo 0
'Check if chart data exists
With Worksheets(sChartWSName)
Set lstObject = .ListObjects(sTableName)
If lstObject.ListRows.Count > 0 Then
Select Case MsgBox("You already have data. Do you want to clear it and start fresh?", vbYesNoCancel, "Clear out old data?")
Case Is = vbYes
'User wants to clear the data
lstObject.DataBodyRange.Delete
Case Is = vbCancel
'User cancelled so exit routine
Exit Sub
Case Is = vbNo
'User just wants to append to existing table
End Select
End If
'Begin appending
Call Chart_AppendData
End With
End Sub
Private Sub Chart_AppendData()
'Append data to the chart table
Dim lstObject As ListObject
Dim lRow As Long
With Worksheets(sChartWSName)
Set lstObject = .ListObjects(sTableName)
If lstObject.ListRows.Count = 0 Then
lRow = .Range("A1").End(xlDown).Row
End If
If lRow = 0 Then
lRow = .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0).Row
End If
.Range("A" & lRow).Value = CDate(Now)
.Range("B" & lRow).Value = Worksheets(sSourceWSName).Range("B8").Value
.Range("C" & lRow).Value = Worksheets(sSourceWSName).Range("G12").Value
End With
RunTime = Now + TimeValue("00:00:01")
Application.OnTime RunTime, "Chart_AppendData"
End Sub
Public Sub Chart_Stop()
'Stop capturing data
On Error Resume Next
Application.OnTime EarliestTime:=RunTime, Procedure:="Chart_AppendData", Schedule:=False
End Sub
 
I added another field to track. "Diff" which is the difference of two calculations from streaming quotes on another worksheet titled "Calc"

I have several other fields on that worksheet that would be nice to see on the same chart the historical visual relation of the two... for now - one will work.

I added .Range("C".... row value relation toward the end and gave the title at the beginning - but I know its messy... the resulting table on Chart worksheet this generates is now populating the Time, Value and Diff fields as expected - however the populated values and Diff fileds are rounded to the nearest penny. I right clicked the column and set to go out 8 decimal places but its now just showing 6 extra trailing zeros...

Each decimal place past the penny means quite a bit overall in the calculations I am doing... how can I get this to show up? If I have to we can simply multiply the Range "G12" and Range "B8" values by 1000 or more to get the full number to show - but it will skew my $xx.xxxxxxx placement... anyhow - any advice much appreciated.
 
Back
Top