Solved

How can I  create a real time graph from a value that changes from real time add-ins in Microsoft Excel 2007?

Posted on 2011-09-13
7
253 Views
Last Modified: 2012-05-12
I have a spreadsheet that updates a value in real time.   I would like to store these values on a periodic basis (say every 5 minutes) and plot the values vs time.   Would consider outside software if easily obtained, but would prefer to start with the graphics available in Excel.  
0
Comment
Question by:dma70
  • 4
  • 3
7 Comments
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
You can use the OnTime function, to schedule a macro to run every 5 minutes (which could copy/paste and keep a chart's history.  The chart could be setup to look at a dynamic range of this ever-growing dataset.

Here's more on the OnTime function.  Its a similar tip that I worked on - copying data to another sheet every 5 minutes.  Hopefully, this should get you going!

http:/Q_27227948.html

dave

0
 
LVL 41

Expert Comment

by:dlmille
Comment Utility
Here's the code:
Public runWhen As Double

Sub startTimer()
Dim refreshTime As Double
Dim waitMins As Long

    Call myMacro
    waitMins = 5
    refreshTime = 15 ' after testing at 15 seconds, then change to -> waitMins * 60 'time in seconds
    runWhen = Now() + refreshTime / 86400 '# seconds in 24 hours
    Application.OnTime earliesttime:=runWhen, procedure:="StartTimer", schedule:=True

End Sub
Sub StopTimer()
    On Error Resume Next
    Application.OnTime earliesttime:=runWhen, procedure:="StartTimer", schedule:=False
    
End Sub
Sub myMacro()
  'code goes here, to copy/paste the values into an area that the chart is looking at.
End Sub

Open in new window


Paste this code into a public module.

Use startTimer() routine to get it going.  Be sure to use the stopTimer() routine when you close the workbook!

On that link I sent, page down until you get to the end, where I've posted the solution with an attached file.  You can see how that one was setup, for a bit more "drilldown" on the solution.

Cheers,

Dave
0
 

Author Comment

by:dma70
Comment Utility
Thanks Dave -- looks like what I need.  May take some time to try it.    Might save me some time if you know the cut and paste functions to use in vb, and how to save both a time stamp and a value, so I have an x and y for my graph.    

Also need to think about how the graph might refresh each day when and only start once the spreadsheet is brought up.    If the graph accesses a specific area in the spreadsheet will the graph update as new points are added automatically?  Any thought on this are welcome.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 41

Expert Comment

by:dlmille
Comment Utility
I can do that.  Can you can mock up what you want? I can better assist you to get what you need, at the end of the day.

E.g., lay out your data, show an area where you have new data coming in, and make a graph.  Just put some comments on the sheet.  I think I can help you from there.  Otherwise, I'd be building the whole thing, which may not be meeting what you need, etc.

Dave
0
 

Author Comment

by:dma70
Comment Utility
I may have trouble by the end of the day myself.   I can give you a location on sheet1 and build a graph template on sheet2 which houses the data each day.
0
 
LVL 41

Accepted Solution

by:
dlmille earned 500 total points
Comment Utility
Ok - here's your example.  All on one sheet.  The startTimer() macro runs on workbook open, closes before workbook close.  There are macro buttons for start/stop.  I have it set to 60 seconds for your testing, but the documentation in the code instructs how to change it to 5 minutes.

On the sheet, I have a place for you to put your chart function (you've not told me alot about this, so I made assumptions).  Below that, is the chart "history".  I premised 150 data points (5 minutes over 12 hour day is about that).  The startTimer macro runs "myMacro" which runs the updateHistory routine, which shifts the data down one row, then copies the data from the data feed into the top, cleaning up after 150 rows.

As a result, you now have a constant feed of last 150 points to the chart.

Take some time to look at it.  I'm happy to answer any questions.  I think this should get you going!

code:
Sub updateHistory()
Dim wkb As Workbook
Dim sht As Worksheet
Dim historyRng As Range, outRng As Range, newData As Range
Dim fRange As Range

    Application.Calculate
    
    
    Set wkb = ThisWorkbook
    Set sht = ThisWorkbook.Sheets("ChartUpdate")
    
    Set outRng = sht.Range("A7:B7")
    Set newData = sht.Range("A2:B2")
    
    Set fRange = sht.Range("A:B").Find(what:="*", searchdirection:=xlPrevious)
    
    If fRange.Row > 6 Then
        Set historyRng = sht.Range("A7:B" & fRange.Row)
        historyRng.Copy historyRng.Cells(1, 1).Offset(1, 0)
        sht.Range("A150:B" & Rows.Count).Clear
    End If
    
    outRng.Value = newData.Value
    
    
End Sub

Sub clearHistory()
Dim wkb As Workbook
Dim sht As Worksheet
Dim historyRng As Range
Dim fRange As Range

    Set wkb = ThisWorkbook
    Set sht = ThisWorkbook.Sheets("ChartUpdate")
    
    Set fRange = sht.Range("A:B").Find(what:="*", searchdirection:=xlPrevious)
    
    If fRange.Row = 6 Then Exit Sub
    
    Set historyRng = sht.Range("A7", sht.Range("B" & fRange.Row))
    
    If historyRng.Rows.Count > 1 Then
        historyRng.ClearContents
    End If
End Sub

Open in new window


timer code:
 
Public runWhen As Double

Sub startTimer()
Dim refreshTime As Double
Dim waitMins As Long

    Call myMacro
    waitMins = 5
    refreshTime = 60 ' after testing at 30 seconds, then change to -> waitMins * 60 'time in seconds
    runWhen = Now() + refreshTime / 86400 '# seconds in 24 hours
    Application.OnTime earliesttime:=runWhen, procedure:="StartTimer", schedule:=True

End Sub
Sub StopTimer()
    On Error Resume Next
    Application.OnTime earliesttime:=runWhen, procedure:="StartTimer", schedule:=False
    
End Sub
Sub myMacro()
  'code goes here, to copy/paste the values into an area that the chart is looking at.
  Call updateHistory
End Sub

Open in new window


code in ThisWorkbook: for workbook_open and _close events:
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Call StopTimer
End Sub

Private Sub Workbook_Open()

    Application.Calculation = xlCalculationAutomatic
    Call startTimer
End Sub

Open in new window


See attached.

Enjoy!

Dave
timerChart-r1.xlsm
0
 

Author Closing Comment

by:dma70
Comment Utility
Comprehensive, easy to use solution.   Detail was amazing.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now