Page 1 of 3 1 2 3 LastLast
Results 1 to 10 of 27

Thread: Streaming charts:

  1. #1

    Streaming charts:



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,064
    Articles
    79
    Blog Entries
    14
    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...
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    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?

  4. #4
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,064
    Articles
    79
    Blog Entries
    14
    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?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  5. #5
    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

  6. #6
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,064
    Articles
    79
    Blog Entries
    14
    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?
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  7. #7
    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

  8. #8
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,064
    Articles
    79
    Blog Entries
    14
    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.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  9. #9
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,064
    Articles
    79
    Blog Entries
    14
    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.
    Attached Files Attached Files
    Last edited by Ken Puls; 2011-10-22 at 07:46 AM. Reason: Bug fix
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  10. #10
    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

Page 1 of 3 1 2 3 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •