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
268 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
ID: 36530874
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
ID: 36530895
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
 
LVL 1

Author Comment

by:dma70
ID: 36531294
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 41

Expert Comment

by:dlmille
ID: 36531356
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
 
LVL 1

Author Comment

by:dma70
ID: 36531655
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
ID: 36532029
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
 
LVL 1

Author Closing Comment

by:dma70
ID: 36552206
Comprehensive, easy to use solution.   Detail was amazing.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql server query from excel 3 57
Excel:  Find text between 2nd hyphen and next space 10 30
Access Excel export not behaving 2 27
NEED TO UPDATE DATA IN EXCEL 18 32
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

867 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

24 Experts available now in Live!

Get 1:1 Help Now