Go Down

Topic: PLX-DAQ graph arduino in excel (Read 25176 times) previous topic - next topic

graphing

Jan 07, 2014, 09:30 am Last Edit: Jan 07, 2014, 10:21 am by graphing Reason: 1
I posted this in a thread with similar information so all the info could be found in one post buuut...
  so here's a link to that post:
http://forum.arduino.cc/index.php?topic=132123.0

Alot more going on there than sending data to excel right?
The LCD stuff doesn't have anything to do with graphing in excel right?

I'm having the same problem as the original poster C is green, R flashes, solid green T no red transmit pulse.

According to the info linked to here:
Commands that talk to excel are always in CAPITAL letters.
DATE, TIME that allows the serial port to send data to Excel. The first field is always TIME, then the fields of interest (val). The command format is:  Serial. print ("DATE, TIME,");  Serial.println (val)

It needs a line that says DATE TIME probably a mispprint that was supposed to say DATA, TIME because all the sample programs use that.
 I have that. Somehow it allows the serial port to send data to excel, it can't send data without that line.

LABEL  sends the column headings.
I have that but I never get any labels to show up, even though it says setting labels in the connected/disconnected box in the spreadsheet after I hit connect.
Here's my code:
Code: [Select]
/*-----( Import needed libraries )-----*/
#include <OneWire.h>
#include <DallasTemperature.h>

/*-----( Declare Constants )-----*/
#define ONE_WIRE_BUS 2 /*-(Connect to Pin 2 )-*/

/*-----( Declare objects )-----*/
/* Set up a oneWire instance to communicate with any OneWire device*/
OneWire ourWire(ONE_WIRE_BUS);

/* Tell Dallas Temperature Library to use oneWire Library */
DallasTemperature sensors(&ourWire);

/*-----( Declare Variables )-----*/
int row = 0;
int x;

void setup() /*----( SETUP: RUNS ONCE )----*/
{
Serial.begin(9600);
Serial.println("CLEARDATA");
 Serial.println("LABEL, Temp");


/*-( Start up the DallasTemperature library )-*/
sensors.begin();
}/*--(end setup )---*/


void loop() /*----( LOOP: RUNS CONSTANTLY )----*/
{
Serial.print("DATA,TIME,"); Serial.print(sensors.getTempFByIndex(0));
 sensors.requestTemperatures(); // Send the command to get temperatures
 row++;
 x++;
 if (row > 360)
  {
    Serial.print(sensors.getTempFByIndex(0));
   Serial.println("ROW,SET,row");
  }

Serial.print(sensors.getTempCByIndex(0));
Serial.print(sensors.getTempFByIndex(0));

delay(10000);
}

/* --(end main loop )-- */

/* ( THE END ) */


Sooo what am I doing wrong?

graphing

Soo did some more searching and found this tutorial:
https://code.google.com/p/my-arduino-lessons/wiki/Excel_Interface
It has some simple code.

Code: [Select]
int val = 0;
int val1 = 0;
void setup() {
Serial.begin(9600); // opens serial port, sets data rate to 9600 bps
// int val = 0;
Serial.println("CLEARDATA"); // clear data in excel
}
void loop()
{
Serial.print("DATA,TIME,"); // send comand to excel
Serial.println(val1); // send data
val1 = random(0, 100); // random bet 0 and 100
delay(3000);
}


About as simple as it gets.
Pretty much confirms the commands
I verify then download and in the Arduino serial monitor I can see CLEARDATA and DATA, TIME along with the random numbers generated.

In the excel/ PLX spreadsheet controller messages it says Accepting data for row 1, Accepting data for Row 2, etc.
The letters are doing the same thing.
but no data shows up in the excel boxes.

Doing the same thing my program is doing wrong.

... OK I opened a new "book" without the graph.
Clicked file in the upper left and clicked new.
A window with "workbook" showed up. I clicked OK and a new window/sheet showed up.
It has tabs for sheet 1, sheet 2, sheet 3 at the lower left.
I can see data loading into the squares now.

The PLX-DAQ spreadsheet I've been having problems with has "simple data",  simple data with plots, and interactive bar graph tabs at the bottom left.

Trying my program on the fresh workbook and it doesn't work until I change this line:
Code: [Select]
Serial.print(sensors.getTempFByIndex(0));
to:
Code: [Select]
Serial.println(sensors.getTempFByIndex(0));

Now data shows up in column B, while the LABEL temp sits over column A which contains a bunch of####### in each box.
sooo some progress, I'll figure out more and post it.
Any tips appreciated.

graphing

Sooo it's possble the PLX-DAQ spreadsheet is just a sample or something. I couldn't do much with it.
Opening a new spreadsheet got it graphing.
The T square never does flash red.

I'm still not sure why there are ###### filling column A or how to change it.

In excel I was having problems getting the x axis to show more than 45 points,
until I changed the source data to accept more:
right click on the chart area and left on format.
Change the data range,
then right click the x-axis, left on format gridline, then change the value.

If you can't tell by my username; figuring how to graph what's happening in the arduino is a big part of this project.
You might also notice it took me a year, ha. Most of that was spent learning programing and figuring out the hardware. Not much help with this post but I have learned alot from people on here, thanks.

I still have to figure out how to plot the x-axis with RPM and y-axis with BTDC or give excel numbers and let it do some of the math.
It will need 2 inputs.
  I'll keep updating as I figure it out. .

graphing

Code: [Select]

/*-----( Import needed libraries )-----*/
#include <OneWire.h>
#include <DallasTemperature.h>

/*-----( Declare Constants )-----*/
#define ONE_WIRE_BUS 2 /*-(Connect to Pin 2 )-*/

/*-----( Declare objects )-----*/
/* Set up a oneWire instance to communicate with any OneWire device*/
OneWire ourWire(ONE_WIRE_BUS);

/* Tell Dallas Temperature Library to use oneWire Library */
DallasTemperature sensors(&ourWire);

/*-----( Declare Variables )-----*/
int fan = 3;
int fanOn = 85;
int fanOff = 80;

void setup() /*----( SETUP: RUNS ONCE )----*/
{
/*-(start serial port to see results )-*/
delay(1000);
Serial.begin(9600);
Serial.println("CLEARDATA");
  Serial.println("LABEL, Temp");
delay(1000);
pinMode(fan, OUTPUT);

/*-( Start up the DallasTemperature library )-*/
sensors.begin();
}/*--(end setup )---*/


void loop() /*----( LOOP: RUNS CONSTANTLY )----*/
{
 
Serial.print("DATA,TIME,");
sensors.requestTemperatures(); // Send the command to get temperatures
Serial.println(sensors.getTempFByIndex(0));
if (sensors.getTempFByIndex(0)>=fanOn)
{digitalWrite(fan, LOW);}
else if (sensors.getTempFByIndex(0)<=fanOff)
{digitalWrite (fan, HIGH);
}

delay(60000);
}

/* --(end main loop )-- */

/* ( THE END ) */

From what I'm reading the x axis gets it's data from column A.
All I see in that column is ######.
Anyone know how to put data in the A column with Arduino?
I'll need RPM on that axis.

graphing

#4
Feb 01, 2014, 06:42 am Last Edit: Feb 01, 2014, 06:44 am by graphing Reason: 1
I once read a post suggesting moving the tab at the bottom to see data being entered into the cells on the  PLX-DAQ practice sheets.
None said what tab or how.
Apparently the data will only go into the leftmost sheet.
So at the bottom left click the sheet where you want the data to go into the cells,  Simple Data, Simple Data With Plots or Interactive Bar Graph, then drag it to the leftmost spot.

I did get the time to show up in column A. Had something to do with the DATA,TIME statement.
Temp shows up in column C. I was trying to get the RPM to show up in column B but if I put Rpm in the DATA,TIME, Rpm, etc. statement the work Rpm showed up in the cell.
I have gotten the value to show up in column C but messed with the program some more and lost it, forgot what I did right, ha.
Here's my current program:

Code: [Select]

/*-----( Import needed libraries )-----*/
#include <OneWire.h>
#include <DallasTemperature.h>

/*-----( Declare Constants )-----*/
#define ONE_WIRE_BUS 2 /*-(Connect to Pin 2 )-*/

/*-----( Declare objects )-----*/
/* Set up a oneWire instance to communicate with any OneWire device*/
OneWire ourWire(ONE_WIRE_BUS);

/* Tell Dallas Temperature Library to use oneWire Library */
DallasTemperature sensors(&ourWire);

/*-----( Declare Variables )-----*/
int fan = 3;
int fanOn = 85;
int fanOff = 80;
int Rpm;
void setup() /*----( SETUP: RUNS ONCE )----*/
{
/*-(start serial port to see results )-*/
delay(1000);
Serial.begin(9600);
Serial.println("CLEARDATA");
 Serial.println("LABEL, Time, Rpm, Temp,");
delay(1000);
pinMode(fan, OUTPUT);
Rpm = 0;
/*-( Start up the DallasTemperature library )-*/
sensors.begin();
}/*--(end setup )---*/


void loop() /*----( LOOP: RUNS CONSTANTLY )----*/
{
   Rpm++;  
Serial.print("DATA, TIME,,");
sensors.requestTemperatures(); // Send the command to get temperatures
Serial.println(sensors.getTempFByIndex(0));Serial.println(",");
Serial.println("Rpm");Serial.println(",");        
if (sensors.getTempFByIndex(0)>=fanOn)
{digitalWrite(fan, LOW);}
else if (sensors.getTempFByIndex(0)<=fanOff)
{digitalWrite (fan, HIGH);
}

delay(60000);
}

/* --(end main loop )-- */

/* ( THE END ) */



Using Serial.println instead of Serial.print for output still matters in some places but I haven't figured out why yet.
Serial.println the comma to seperate data matters sometimes, ha.
I still haven't found an example that didn't use time for the x-axis and have had a hard time getting a point to represent x and y coordinates.

graphing

#5
Feb 01, 2014, 08:45 am Last Edit: Feb 01, 2014, 08:55 am by graphing Reason: 1
OK I got it working.
Here's my code:
Code: [Select]

/*-----( Import needed libraries )-----*/
#include <OneWire.h>
#include <DallasTemperature.h>

/*-----( Declare Constants )-----*/
#define ONE_WIRE_BUS 2 /*-(Connect to Pin 2 )-*/

/*-----( Declare objects )-----*/
/* Set up a oneWire instance to communicate with any OneWire device*/
OneWire ourWire(ONE_WIRE_BUS);

/* Tell Dallas Temperature Library to use oneWire Library */
DallasTemperature sensors(&ourWire);

/*-----( Declare Variables )-----*/
int fan = 3;
int fanOn = 85;
int fanOff = 80;
int Rpm;
void setup() /*----( SETUP: RUNS ONCE )----*/
{
/*-(start serial port to see results )-*/
delay(1000);
Serial.begin(9600);
Serial.println("CLEARDATA");
 Serial.println("LABEL, Time, Rpm, Temp,");
delay(1000);
pinMode(fan, OUTPUT);
Rpm = 0;
/*-( Start up the DallasTemperature library )-*/
sensors.begin();
}/*--(end setup )---*/


void loop() /*----( LOOP: RUNS CONSTANTLY )----*/
{
   Rpm=Rpm+3;  
Serial.print("DATA, TIME,,");
sensors.requestTemperatures(); // Send the command to get temperatures
Serial.print(sensors.getTempFByIndex(0));Serial.print(",");
Serial.println(Rpm);                                                                             // This last output needs the println not just print
if (sensors.getTempFByIndex(0)>=fanOn)
{digitalWrite(fan, LOW);}
else if (sensors.getTempFByIndex(0)<=fanOff)
{digitalWrite (fan, HIGH);
}

delay(60000);
}

/* --(end main loop )-- */

/* ( THE END ) */


In excel:
right clicking on the chart area will get you to source data, click on the series tab to tell what cells you want the x-axis data to come from.
Click on the x-axis box then click on the letter at the top of the column you want the data to come from.
It'll probably look something like this: =Sheet1!$D:$D
I changed it to =Sheet1!$D$2:$D$500 to make it work. Starts at row 2 and goes to 500 in column D.
The data points now represent the x and y coordinates.
Thanks for all the links and data I found on here.
I couldn't just do the "I found the problem" then leave without any info as to how they did it, like so many others have.
I plan to let excel do most of the math.
All that's left is to figure out how to do that.

Oh yeah, Someone gave me an old windows 98 laptop. PLX-DAQ works on these old laptops. Well it's supposed to but I haven't tried it yet. It will be great to get some more use out of this old laptop as a dedicated engine analiser screen.

Nick_Pyner


OK I got it working.
I changed it to =Sheet1!$D$2:$D$500 to make it work. Starts at row 2 and goes to 500 in column D.
The data points now represent the x and y coordinates.


I think column D sends datapoints to Y and the x coordinate is just the sequence of data sent, i.e time. That time is regular, and it seems a better way to do it than include time as data. 

Quote

Oh yeah, Someone gave me an old windows 98 laptop. PLX-DAQ works on these old laptops. Well it's supposed to but I haven't tried it yet.


It should be fine. I use it with Office 2000 under XP. I don't think Excel has changed much over the years.  I haven't gone ove rall this, "OK I got it working." is what counts.  I believe the only problems you might see are all to do with graphs. Arduino, PLX-DAQ and the worksheets are all reliable.

graphing

Using DATA, TIME is kind of a waste on this one because I want x to be the RPM not time,.. but it doesn't hurt to see the time in the A column.
Quote
I think column D sends datapoints to Y and the x coordinate is just the sequence of data sent, i.e time. That time is regular, and it seems a better way to do it than include time as data.


I'm not sure what you meant by this.
My graph uses y to display the time. The data comes from column C.
And data for the x axis comes from column D.
This is how I set it up in excel.
Quote
right clicking on the chart area will get you to source data, click on the series tab to tell what cells you want the x-axis data to come from.
Click on the x-axis box then click on the letter at the top of the column you want the data to come from.
It'll probably look something like this: =Sheet1!$D:$D
I changed it to =Sheet1!$D$2:$D$500 to make it work. Starts at row 2 and goes to 500 in column D.
The data points now represent the x and y coordinates.

Nick_Pyner

#8
Feb 05, 2014, 03:09 pm Last Edit: Feb 06, 2014, 12:33 am by Nick_Pyner Reason: 1

The T square never does flash red.


I think that's because you aren't transmitting anything. Don't ask how you would do that.

Quote

Quote
I think column D sends datapoints to Y and the x coordinate is just the sequence of data sent, i.e time. That time is regular, and it seems a better way to do it than include time as data.


I'm not sure what you meant by this.


I'm feeling my way with this too but have had some success recently. I think col A defaults to x-axis. What I was trying to say is that col A is time by default, the unit being Arduino's loop cycle.

Quote

My graph uses y to display the time. The data comes from column C.
And data for the x axis comes from column D.


I think it makes more sense to use X-axis for time, if only because it is a convention, and I believe that is what Excel wants to do anyway. I guess this is unimportant if the time periods are short i.e. not many entries. I was despairing of getting a result with multi-line graphs, but it coming good. This was not done with PLX but the graphing principles are the same.  It really pays to only send data you want to graph, and to properly plan the titles in the spreadsheet......

graphing

Doh, I mistyped.
Quote
My graph uses y to display the time. The data comes from column C.


I meant to type TEMP  instead of time being displayed on the Y-axis.
  Should have read:
Quote
My graph uses Y to display the temp. Data comes from column C.


  Column A is where time is displayed by default, you are right that is normally used for the x-axis by default.
  Column B is blank
  Each column is filled in order after that.
In my case using this code:
Code: [Select]
Serial.print(sensors.getTempFByIndex(0));Serial.print(",");
Serial.println(Rpm);

Each variable seperated by commas and the last one using println

Very nice looking graph.
How did you get 8 lines of data on the chart, what did you do in excel?
I ran across it once by accident, then forgot how I got there, ha.

Nick_Pyner


Very nice looking graph.
How did you get 8 lines of data on the chart, what did you do in excel?
I ran across it once by accident, then forgot how I got there, ha.


What I did was a hell of a lot of blundering about. I even re-installed MSWorks because twenty years ago I was the only person in the yacht club who owned an A2 printer and I got to do the weekly race leader charts. No problem then, but I couldn't get Excel to do it now.  I finally found that the Excel wizard actually works quite well - you just need to know their language and then get used to it. The big secret is to start with the basic "Line" example, not multi-line stacked.

Note that a lot of detail cannot be done in the Wizard, you have to click on things like lines and scales to tweak them afetr the wizard is finished.

The chart shown was derived from a spreadsheet that was not planned with the chart in mind. Big Mistake. The sheet has over forty columns and sorting which ones I wanted was really messy. It makes far more sense to have nothing on the sheet that is not intended for the graph. If you are working with PLX-DAQ, this is probably the case anyway but, if not, at least keep the required columns consecutive.

Note particularly that the line tags, TempIn, TempOut etc., cannot be edited in the chart. If you are using PLX-DAQ, these names are edited in the Arduino.

graphing

By line tags on your chart for example you mean HXU kwh/day?
Are they tied to the column LABELs which are determined in Arduino sketch?

I have been planning to use several columns that won't be graphed, Variables for excel to work with, to come up with the columns I will use in the graph. Good tip about keeping those off to the right columns and use consecutive left columns for the graph lines.

Nick_Pyner


By line tags on your chart for example you mean HXU kwh/day?


Yes
[/quote]
Are they tied to the column LABELs which are determined in Arduino sketch?[/quote]
[/quote]

In this case the data is a manually operated spreadsheet, so I have to edit that in order to change the lables.  If you are running PLX-DAQ the labels come from Arduino and thus need to be edited therein. It took me a while to work that out.

Quote

I have been planning to use several columns that won't be graphed, Variables for excel to work with, to come up with the columns I will use in the graph. Good tip about keeping those off to the right columns and use consecutive left columns for the graph lines.


What I'm looking at doing with the manual worksheet is have the data for graphing simply duplicated into new consecutive columns. I have also inserted a new row at the top to take the line labels. This means all the graph stuff is in a simple continuous block and the original columns can stay as they are.  Something similar would apply to a PLX-DAQ job where not all the data goes into a graph.

graphing

Messing around with it a little more:
Changing this line alone,
Code: [Select]
Serial.print("DATA, TIME,,,");
by adding commas.
Each comma will move all the data (except time) one column over.
The line above made temp and rpm show up in columns D&E

Nick_Pyner

V interesting new formatting development. You are creating blank columns that could be subsequently used by Excel. There could be some real value in that.  I'm a firm believer in the view that, if Arduino and Excel are working together, Arduino should be the data creator, and Excel the manipulator. The only exception I can think of is where on-board maths is done so the result can go to local display, and even then there is no actual advantage in sending that result to Excel.   

Go Up