Real time graph

**PlankSpank** · 03-23-2016, 12:36 PM

Ok, here's my first question for the forum.

I have live data coming into Excel and want to see live trending data on a line graph for x number of points. In other words, x number are individual lines, representing a single data point each. I have a graph working for single points that change in real time, but no history of where that point was previously. The need is so I can be sure that data stream has stabilized. As a limiting parameter, the live data graph can be limited to ten minutes.

Has anybody done this? I've searched the web for answers, but had no luck.

**PlankSpank** · 03-23-2016, 02:34 PM

I may have posted this in the wrong forum. Maybe this is better suited for the graphing forum?

**MrShorty** · 03-23-2016, 02:58 PM

Maybe, it probably depends on whether the real problem is in creating the chart or not.

Since you have not given a very specific explanation of what you are having a problem with, it is difficult to provide specific suggestions. To provide a broad overview of this, here's what I would expect to see in a final solution to this broad question:

1) Data is imported into Excel. You have provided no details of how this happens, but it sounds like this is working for you. Some description of what this step looks like would establish the "starting point" a solution needs to address.
1a) It sounds like old data is lost, but I can't be sure from your description. If new data is truly over-writing old data, then there will need to be a step here that will capture the old data and store it somewhere. Details will depend on what is happening in the import step.
1b) Or, reconfigure the import step so that it doesn't over-write the old data. Exactly how to do this will depend on what is happening during import and what changes/options are available to this step.

2) Data to be charted is arranged in the spreadsheet. The details of how to do this probably depend in large part on what is going on during step 1. I would expect that, at the end of step 2, the data to be charted will look something like

Please Login or Register  to view this content.

3) With the data so arranged, the chart should just about create itself (if the chart is a relatively simple example of a built in chart type or a combination of built in chart types). Insert and format the chart.

With that broad overview, what part do you need help with?

**PlankSpank** · 03-24-2016, 08:57 AM

arrg, typed out a response to this and hit the wrong button!

**PlankSpank** · 03-24-2016, 09:23 AM

Ok, you are absolutely correct, asking the right question and providing the right information is key to getting answers. KNOWING what questions to ask is sometimes the hardest part of getting the answer! Thanks for that!

This is how the system works:

Sensors are connected to Campbell CR9000. Campbell is connected to a network computer. Data Advisor interfaces Excel computer to network and enables data collection. My data stream in Excel is D2 to BP2, 60 channels of which I need to display about 20 in the chart.

The data is overwritten, and as you pointed out, needs to be collected to graph it! Duh...

So, I think this is what I need:

1) I need to collect data stream in row x, increment that row at at certain frequency to keep plot smooth, let's say every second for now.
2) Limit the data collection stream to ten minutes, clearing the chart and repeating from zero once ten minutes has been reached.
3) Figure out how to also have this data include a time stamp so that can be displayed on one axis.

How am I doing?

**PlankSpank** · 03-24-2016, 09:38 AM

Data stream is in Rows, to clarify for those in the stands.

Additional help, questions, comments or criticism always welcome!

**MrShorty** · 03-24-2016, 11:18 AM

I'm not familiar with the CR9000, nor this Data Advisor program you reference. My first thought is to explore these two components of the system and see what controls they provide for the data stream into Excel. One of the "easiest" approaches to this would be to find a simple option in the data logger program to "append" rather than "overwrite". Or, perhaps, if one understands how this Data Advisor program chooses where to put data in Excel, that option can be changed, or changed programmatically, so that it puts data in a new row each time it writes to Excel. Of course, it is unlikely that you will find someone on an Excel forum who is familiar with these data logger/data import programs. These are questions best put to someone familiar with these applications (if not yourself, since you probably know more about these applications than any of us do). That's my first thought -- control this on the data logger side, and Excel does not actually need to do all of this.

If you decide that it is best/easiest to control this from Excel's side of things, here's how I see it happening:

1) A change event (or another event if the logger's changes do not trigger the change event) to detect the changes made to the spreadsheet. https://msdn.microsoft.com/en-us/lib.../dn301178.aspx
2a) The change event either copies the data from the logger's input and puts it elsewhere. This will usually involve the .value property of the two range objects: https://msdn.microsoft.com/en-us/lib.../ff838238.aspx
2b) or it modifies the logger so that the logger's next input is in a different row. This will be done through the data logger's object model. We will probably have to assume that you know how to access and manipulate the logger's object model.
3) There will probably be a development stage to test how fast this can be done, so that you will know how frequently this can run. Every second might be too fast to consistently and reliably have VBA do what you ask it to do. Some of this will involve knowing how many different processes this computer is expected to run, how many different spreadsheets you can have open, how long those spreadsheets need to calculate, and other things.

One last thought, I will mention that, when I do this with my data loggers, I have them write the data to a text file, then import that text file into Excel. I can easily have Excel read from the text file every minute and update charts. This approach works very well for my purposes.

**PlankSpank** · 03-24-2016, 12:38 PM

The CR9000 is my ADC I/O. There are a few other steps in the chain, such as LoggerNet, but it's not that complex once you see it. Data Advisor is what allows me to get that data stream into Excel. The unfortunate reality is the system is from the mid-90's and I'm bound to the constraints of the system.

We get to dictate where Data Advisor puts data into Excel, and you may have hit on something there. Unfortunately I have to depend on someone else for Data Advisor advice as I don't have and can't find any documentation for it. I have such a person in-house, so hopefully I can tap that resource next week.

I am certainly aware the questions I've asked are probably more case specific, but in searching the web, I have had little luck finding any information that dives deep enough to actually make any headway. You've been a better resource in one day on this forum than a week of searching the net! I thank you for that! Maybe someone will find our discussion helpful in the future. I certainly hope so!

So continuing the stream of thought:

Provided I don't find a solution on the data logger side....

1) Look for changes to the row of streaming data in Excel and do something with it when detected. Makes sense if I'm following you. Do you use this process when writing data to your text file or are you using software for your data logger to act and an intermediary?

2a) Ahh, this might be answering my question above. Trying to follow this while still running tests and data collection. Multi-tasking is a super-power I'm still working on...

2b) Not sure I have this option. The system seems pretty dumb in this respect, from my limited knowledge of what goes on upstream from the data coming in. I'm working on correcting that, FWIW.

3) Absolutely necessary to determine what kind of load this will put on this old system. We are asking it to do something it wasn't originally designed to do. This is actually the first step towards automating the data collection process. I can tell you I currently only need one sheet open, Excel 2007 running. That computer is on a network, with HIBUN security software running, so it could get dicey if I ask too much of it. Good VBA coding technique is always a must. I try to keep things a simple as possible.

Are you viewing in real-time?

So I mentioned this was a first step in automating the data collection process. Next steps are:

1) Improve the primitive UI we have set-up in Excel, such as buttons on the sheet instead of them being in a pop-up window.

2) Allow scrolling on the sheet when scan is active, currently the VBA programming takes control and limits UI to the functions in the pop-up window.

3) Add a sub-routine to take over the button press for data collection after 10 minutes, take that data point, increment to the next row and restart the 10 minute timer. Repeat x times.

4) Show number of points acquired

5) Show ten minute timer

6) Save file after each data point

And finally, but not finished

7) Interface Excel with external AVR (like Arduino) to make servo motor changes, where the feedback voltage values are in Excel, Excel tells the AVR to move servo x and waits for servo x to reach proper position without going past the set-point by a certain amount. (trying to avoid hysteresis problems)

I thinks it's a great project and it's nice to have something to work on in between points while I wait for stabilization.

**MrShorty** · 03-24-2016, 01:24 PM

1) Look for changes to the row of streaming data in Excel and do something with it when detected. Makes sense if I'm following you. Do you use this process when writing data to your text file or are you using software for your data logger to act and an intermediary?

Our data collection uses a program built in Labview to read data from the input sensors, and send control information to any controls. This program generates the text file from current data. It can be setup to "append" data to the text file, so that it writes a new row or "record" each time it writes. I then have Excel "import" that file as a data connection (https://support.office.com/en-us/art..._file_by_conne ).

One thing you may have missed, in talking about Excel watching for changes, I am specifically referring to what is called the Change event (another tutorial file: http://www.cpearson.com/Excel/Events.aspx ).

Are you viewing in real-time?

It depends on exactly how you define real-time. If you look at the Properties dialog mentioned in step 4 of the help file, you will find that Excel can refresh the data from the text file as frequently as once every minute. For my purposes, that is usually often enough. When I want to refresh in between the scheduled refresh interval, I can simply invode the Data-Refresh-Refresh command and the data range will update. It's probably not "real-time" but it is often enough for what I need to do with it. Of course, this refresh command could be automated through VBA to occur more frequently (http://www.cpearson.com/excel/OnTime.aspx ).

**PlankSpank** · 03-26-2016, 06:51 AM

Ahh yes, LabView, we use that a lot for test control in other areas.

As far as "real-time" is concerned, in my application it's enough to say real-time is as I view and perceive data. Timing is not at all critical, say when I move a K-type TC and view results. I'm not concerned about propagation delays as much as I am having a smooth update and not bogging down the CPU or display refresh. If I can achieve this along with the additional automation tasks, it's a win for the project.

A little additional background about me:

Before landing in this R & D environment, I had never used Excel, LabView or done any thermal analysis. I had spent almost 30 years designing, modifying and repairing electronics, 16 of which was my own business. It gets expensive to fund projects and I worked in a niche market that was hit extremely hard by the economic downturn and still hasn't recovered. I landed this gig almost a year ago and this old dog is relying on old knowledge and grit to learn new tricks!

I like the challenge and with help from like minded folks like you, there isn't anything that can't be done in my mind. I thank you for your kind way in which you assist. You remind me of one of the lead engineers I worked with in the chemistry department while I was at university. He would provide guidance and another way to approach a problem without actually providing the complete solution, forcing me to think and arrive at my own conclusion.

All this understanding without a complete picture of what I'm trying to do and knowing the syntax of the software I'm using outside excel, it would be very difficult to provide such a solution anyway.

Thanks for the support. I'll keep you posted on the progress and I appreciate any future assistance!

**PlankSpank** · 03-29-2016, 06:06 AM

Ok, so moving forward there is no data/instruction/owners manual for DataAdvisor, so attempting that option is a last resort and may not be possible in any event.

Back to VBA, I'd like to attempt this, and could use some additional expert advice.

I have data coming in like this:

worksheet is Input 9V and fills a Row, updating in real time about once a second.

I would like to "capture" this data as it comes in and place in on another sheet "Data Stream" in row 600. If we look at that sheet like an empty glass, it should fill with data, with row 600 clearing, row 599 filled with previous data and so on.

600 represents about 10 minutes of data. I think I should be able to take the data on this sheet and populate a scatter graph on Input 9V to update in real time with historical data to show where my data has been.

Does this make sense and does anyone have the coding skills to assist? My VBA understanding is pretty poor at this time, but I'm working on improving it as time permits.

**MrShorty** · 03-29-2016, 04:20 PM

I don't do very much event code, so I'm not very familiar with it. I would probably start this project by looking at the event code links I have already listed. Work through some of the "Worksheet_Change" event examples that are given. That would give you a feel for how the Change event works. It would also provide some simple examples to test if your data feed triggers the change event when data enters the worksheet.

Once you have a feel for the change event, the heart of your actual procedure should be a simple Range(destinationrange).value=range(sourcerange).value statement. If you are not familiar with Excel's object model, you should familiarize yourself with these objects: https://msdn.microsoft.com/en-us/lib.../ff194068.aspx The Range object will probably be the big on in this project.

Here's how I would envision it looking

Please Login or Register  to view this content.

It was quick and untested, but I would expect those to be the basic steps to doing this.

**PlankSpank** · 03-30-2016, 07:21 AM

Thanks for the continued assistance! I might just have some time today to work on this as I'm in between tests! I'm currently working on one bug and will let you know how this progresses. I hope once we get a solution, this will help others!

**PlankSpank** · 03-31-2016, 05:32 AM

Occam's Razor!

I got the graph to populate in real time. This is what I did:

1) I created a Macro to copy the data row with the live stream data coming in (on graph sheet) and pasted that data into a different sheet dedicated to that data (data sheet). Since the data is updated once every second, the paste row I used is 600 to represent 10 minutes of history. (If your application has different, some simple math will allow you to get the approximate timing you need)
2) I created a second Macro to copy rows 2:600 and paste range into A1
3) Set up scatter plot chart with each channel pulling its data from the appropriate column on the data sheet.

One of the key concepts here for getting into VBA programming is to use the Macro Record function to generate the code you want. This didn't provide me with exactly what I wanted, but gave me the framework to modify the code into something that worked the way I needed it to. Additionally, by not providing a complete solution, I made some real progress in my VBA understanding. I didn't provide any code for two reasons. One, I believe the roadmap I provided will allow you to reach the same conclusion and two, you'll learn by doing. If you have trouble, post on this thread and I'll help walk you through it.

It works great and thanks for the assistance from MrShorty! I hope this helps someone else! If it does, add to the thread and let us know!

**PlankSpank** · 03-31-2016, 05:53 AM

Originally Posted by PlankSpank

So I mentioned this was a first step in automating the data collection process. Next steps are:

1) Improve the primitive UI we have set-up in Excel, such as buttons on the sheet instead of them being in a pop-up window.

2) Allow scrolling on the sheet when scan is active, currently the VBA programming takes control and limits UI to the functions in the pop-up window.

3) Add a sub-routine to take over the button press for data collection after 10 minutes, take that data point, increment to the next row and restart the 10 minute timer. Repeat x times.

4) Show number of points acquired

5) Show ten minute timer

6) Save file after each data point

And finally, but not finished

7) Interface Excel with external AVR (like Arduino) to make servo motor changes, where the feedback voltage values are in Excel, Excel tells the AVR to move servo x and waits for servo x to reach proper position without going past the set-point by a certain amount. (trying to avoid hysteresis problems)

I also made headway on this incomplete list of todo's:

1) I was able to get the pop-up window to dock in the windows default position and sized it to cover the ribbon area of excel. It's out of the way and functional

2) Using VBA, I added some buttons to my pop-up window that allow scrolling and hiding for the different data blocks. An additional button was added to go back to the start row on the graph. Very happy with this!

3) Still working on this line item

4) No longer required since implementing item 2

5) Added a button to the pop-up window to clear the graph. Since the graph is set for time = 10 minutes, once the graph is filled left to right, no need for a ten minute timer!

6) Used a VBA save command in the take data macro. Data saved after each data point acquisition!

Once I got serious working on these, they fell right into place. It was helpful to break the bigger task into smaller chunks and tackle each small task. I saved every time I made progress, testing the new code often. I always work with a copy of the file, since I have a tendency to try things that don't work on first attempt. I've tried a lot of code snippets from the internet that just don't seem to work, I suspect because they refer to specific applications or are incomplete for some reason.

I think the key is to keep at it. As frustrating as programming can be sometimes, walking away and getting a cup of coffee and coming back to it after you get the blood flowing again often helps me! LOL

Good luck if you are trying to do similar things!

Real time graph

LinkBack

Thread Tools

Rate This Thread

Display

Real time graph

Re: Real time graph

Re: Real time graph

Re: Real time graph

Re: Real time graph

Re: Real time graph

Re: Real time graph

Re: Real time graph

Re: Real time graph

Re: Real time graph

Re: Real time graph

Re: Real time graph

Re: Real time graph

Re: Real time graph

Re: Real time graph

Thread Information

Users Browsing this Thread

Similar Threads

Can excel draw a graph in real time ?

[SOLVED] How do you plot real time data into a line graph?

[SOLVED] Time & a half calulated on real time (not after x many hours worked)

Formula for Calculating Paid Time Off for fiscal anniversary date real time

Excel 2007 : Notification time Plus - real time tracking PAST midnight with Conditional formatting

Documenting Static Real Time [Absolute Time] in a Cell

Excel 2007 : Graph does not show real data

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions