Create Realtime Data Trend Chart

PatR78

New Member
Joined
Aug 10, 2017
Messages
6
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,997
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

New Member
Joined
Aug 10, 2017
Messages
6
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,997
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.

Code:
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

New Member
Joined
Aug 10, 2017
Messages
6
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>
 

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
5,997
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

New Member
Joined
Aug 10, 2017
Messages
6
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.
 

Forum statistics

Threads
1,081,834
Messages
5,361,594
Members
400,639
Latest member
fleyd

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top