Create Realtime Data Trend Chart

[PatR78]

Hi,

I've got a worksheet where I have realtime data updating into a new row every second. I'm trying to then transfer this in real time into a line chart but have a very basic understanding of Excel and also can't seem to find an answer to this query anywhere.

I have linked a video on youtube below which shows exaclty what I want to do but this seems to suggest that I need some extra software.

Can somebody please confirm to me if this is possible using Excel 2016 as it is or if not please tell me how I can do this.

Thank you in advance I've linked the video here : Use Excel as realtime trend SCADA - YouTube

Pat

 

[John_w]

Yes, this is possible with Excel 2016 without needing any extra software.

The Worksheet_Calculate event handler can set the chart's SetSourceData property so that the chart updates dynamically as the data arrives in Excel.

How is the real-time data being put into the Excel cells?

You say that a new row is updated every second which, if I understand you, implies that the number of data rows increases, however the video shows a fixed number of rows (21) with the data moved up 1 row and new data put in the last row as new data arrives. Which is your scenario?

 

[PatR78]

Hi John,

Thanks for trying to solve this for me.

As long as the sheet is being updated a new row of data will be added once every second so its not fixed, the newest data will stay at the top. I've tried using a simple line chart to collect this data but as a new row is added it pushes the range I want to capture down the sheet so the end result is nothing gets captured.

 

[John_w]

It's not clear to me how the new row is being inserted, so I'm not sure if this will work for you. Put this code in the sheet module of the worksheet containing your data. The code assumes the chart is placed on the same sheet.

The chart data is expected to be in column A (time) and column B (data value). The first data row is FIRST_DATA_ROW (row 11 in the code below) and this is where new rows are inserted, and you want NUM_ROWS_ON_CHART data points shown on the chart. You can change these values as needed.

Private Sub Worksheet_Calculate()

    Const NUM_ROWS_ON_CHART = 40
    Const FIRST_DATA_ROW = 11
    
    Dim chartObj As ChartObject
    Dim chartSourceData As Range, XValuesData As Range
    
    'Define chart source data range.  This is 2 columns starting at column A and row FIRST_DATA_ROW for NUM_ROWS_ON_CHART rows
       
    Set chartSourceData = Cells(FIRST_DATA_ROW, "A").Resize(NUM_ROWS_ON_CHART, 2)
    Set XValuesData = Cells(FIRST_DATA_ROW, "A").Resize(NUM_ROWS_ON_CHART, 1)
    
    Set chartObj = ChartObjects(1)
    chartObj.Placement = xlFreeFloating         'prevent chart expanding as new row is inserted
    With chartObj.Chart
        .SetSourceData Source:=chartSourceData
        .SeriesCollection(1).XValues = XValuesData
        .Axes(xlCategory).MinimumScale = XValuesData(NUM_ROWS_ON_CHART)             'X axis first time value is NUM_ROWS_ON_CHART'th time (oldest time)
        .Axes(xlCategory).MaximumScale = XValuesData(1) + TimeValue("00:00:01")     'X axis last time value 1st time (newest time)
    End With

End Sub

 

[PatR78]

Unfortunately I don't have permission to post attachments if I did It might have made it easier.

I copied the code into the worksheet module but got an error : Run-Time error '1004'
Method 'ChartObjects' of object'_Worksheet' failed

When I press debug its then highlighting the following line of code: Set chartObj = ChartObjects(1)
****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">

Set chartObj = ChartObjects(1)Set chartObj = ChartObjects(1) Set chartObj = ChartObjects(1)</pre></body>

 

[PatR78]

Forget the last 2 lines of code not sure where they came from!

 

[John_w]

I copied the code into the worksheet module but got an error : Run-Time error '1004'
Method 'ChartObjects' of object'_Worksheet' failed

Is the chart on a Chart sheet or a Worksheet? I can reproduce that error if the chart is on a Chart sheet. The code works for me when the chart is on a Worksheet, but crashes Excel when the chart is on a Chart sheet.

 

[PatR78]

Would it be possible to email a copy of the file to you? I'm not understanding a lot of this and I feel like I'm doing a terrible job of explaining it to you.