Live Data to Excel From an Arduino Light Sensor

I started this little project the other day when I saw that Instructables had posted a competition for the use of Sensors, however its turning out to be a bit more interesting.

My original idea was to take a very simple project (examples in one form or another are available all over the web) add a sensor and reprogram it in a way that would allow the sensor (a Light Dependent Resistor) to control the lights.

After all a lot of the wiring on projects can be the same its the programming that changes the way it works.

Anyway - I put it all together, got it to work and realised that it was quite a sad effort. so I decided to try to link the serial port back to the computer to send real time data to a graphing package.

You could use this as a diagnostic tool to monitor and display results from your robotic sensors to ensure that they perform as required on your projects. Yes you can do this in the monitor window of the Arduino software, but you cannot save or trend the data in that application.

More interesting uses for this type of project would be to monitor how light levels change throughout the day comparing things like cloudy days to brilliantly sunny days.

Another option is as security sensor to track entrances into a room.

Once I had captured the data I used excel 2013 to chart it, but also later on you can see that I have had a go with Plotly as well and found it to be a really good tool for creating quick and simple representations

If this interests you then please read on - if not then please take a look at some of my other projects, but I hope you enjoy all of them.

Step 1: Parts List and Software

PARTS

one	arduino
Various	jumper wires
Ten	Light Emitting Diodes
Ten	Resistors
One	Potentiometer
One	Light Dependant Resistor

The Arduino is an UNO, the LED's are low values and the Pot is one I had laying around.

The LDR is one salvaged from my Cybot which was modified in a previous guide see

https://www.instructables.com/id/Scratch-4-Arduino-...

I found a good site for calculating the values for resistors - see the first picture on this guide, the link to which is just below here. I used 33 ohm resistors - these where considerably smaller than my first attempt using some a rescued from an old alarm system - they where way too big leaving me wondering why nothing worked when I switched the "finished thing on"?.

http://www.dannyg.com/examples/res2/resistor.htm

SOFTWARE

There are two bits of software and one addon required for this project to work:

The Arduino IDE is free to download from the following location and comes as either an install of zip file - I like the Zip (easier to use and and be run from a flash drive for portability)

http://arduino.cc/en/main/software

MICROSOFT EXCEL - unfortunately not free - I have office 2013 on a windows 7 computer

Finally the most important bit is the addon - this is for Excel and comes from a site called PRALAX

http://www.parallax.com/downloads/plx-daq

The file you want is PLX-DAQ and runs as a macro from inside Excel. This is a great little file as it can be used to monitor any serial port and gather the resultant data sent through it. All that is required is some specific lines of code in the Arduino sketch to tell it the send data to specific cells in Excel.

You also need to specify the port speed and ensure that the Arduino is set to the same speed - initially I tried 9600, but although this worked for the test spreadsheet that only sent text to excel from the Arduino, I needed to set it to 128000 for the final project as I would be monitoring the response times for the LED's dependent upon the resistance of the LDR at any given time.

Step 2: Wiring Up

If you follow the pictures above you will be able to replicate this layout.

The Resistors are in place to stop power surge from damaging the Light Emiting Diodes, The Potentiometer is used to adjust the rate of the LEDs change from Off to On. This is important as depending upon how strong the light will get you can flood the LDR resulting in the LEDS cycling so fast that the appear to be on all the time.

This means that nay data sent to Excel would the changing so quickly and the size of the change so small as to be worthless in terms of interpretation. So in essence the POT is used to calibrate the data rate to excel.

Step 3: Areduino Code

The Paralax install file creates a couple of example spreadsheets which are really all you need on the Excel side. Once installed all you need do is open the example file and accept the use of macros- at which point the PLX DAQ control will open. Set it up as per the previous image in step 1 making sure that you select your com port and not the one I have used (unless yours happened to be COM 3 as well)

Arduino

The following is the code I cobbled together - allows the Arduino to read the potential difference between the

Adruino pins 5 volt and A2. The potential value is firstly calibrated using the Potentiometer.

The calibration is done by completely covering the LDR so that its resistance drops to as close to nothing as possible (this makes the LEDS change very quickly) then you adjust the POT until the cycling light change from the LEDs is visible.

The more you adjust the POT the great you can make the time changes registered at Excel, but be careful not to go too far or you will not see data at the other end of the scale (i.e. when you expose the LDR to light again)

//Declarations - set up the variables to be used in the application
byte ledPin[] = {4, 5, 6, 7, 8, 9, 10,11,12,13}; // Create array for LED pins
int potPin = 2; // select the input pin for the potentiometer
int ledDelay; // Set a delay period LED Change
int delayData = 80; // Set a delay period for the data transferred to Excel and attempt to reduce data volumes
int direction = 1;
int currentLED = 0;
int x = 0; //Set the initial counter for LED OUTPUT
int row = 0; // Set the starting Excel row
unsigned long changeTime;
//---------------------------------------------------------------------------------------------------------
// Set up the Arduino Serial port rate, clear all previous data and send the headings for the data transfer to excel
void setup() {
Serial.begin(128000); // opens serial port, sets data rate128000 bps
Serial.println("CLEARDATA"); //clears any residual data
Serial.println("LABEL,Time,Pin,Light Level");

// Reset all pints for the LEDs to digital output

for (int x=0; x<10; x++) {
pinMode(ledPin[x], OUTPUT); }
changeTime = millis();
}

void loop() {
ledDelay = analogRead(potPin); // read the value from the pot
if ((millis() - changeTime) > ledDelay) { // if it has been ledDelay ms since last change
changeLED();
changeTime = millis();
}
}
void changeLED() {
for (int x=0; x<10; x++) { // turn off all LED's
digitalWrite(ledPin[x], LOW);
}
digitalWrite(ledPin[currentLED], HIGH); // turn on the current LED
currentLED += direction; // increment by the direction value
// change direction if we reach the end
if (currentLED == 9) {direction = -1;}
if (currentLED == 0) {direction = 1;}

// Send the data to excel and delay the next step for teh set period of time

Serial.print("DATA,TIME,"); Serial.print(currentLED); Serial.print(","); Serial.println(ledDelay);
row++;
x++;
delay (delayData);

}

Step 4: Shielding the Sensor

When I carried out the initial testing the LDR worked well by itself, but I found that the chart produced in excel was very noisy - this was due to too much light getting to the sensor - it was picking up not only the light being measured but also the ambient light in the room.

The solution was to place the LDR in a tube, I also mounted it on a little base. This shielded the sensor from the light in the room and made it more directional.

Now that you have it all put together its time to have a play - I have only set up two experiments for this setup as follows.

Step 5: Experiment 1 : Measuring Light Changes From Day to Night

Set up the circuit so that the LDR is pointing towards a window (See the first picture in this section).

When ready Open the Excel file and then connect the USB cable - once the Arduino has been recognised the LEDs will start to cycle then to start collecting data in Excel select the Connect button on the PLX-DAQ panel (second picture)

Leave the whole thing running for however long you wish (keep in mind that the longer its left the more data will be collected) however due to the nature of this experiment it will need to be a long time as a small amount of data will not tell you anything.

Test conditions:

1. Readings where taken from 18:59:04 until 20:51:37 approximately 2 hours
2. During the collection period 15,164 readings where taken
3. Weather fine - no rain
4. The data plot is the blue line on the chart
5. The Orange line is the plot of difference between the current and preceding value (differential)

Observations :

There are four areas on the chart that show points of change. as follows

Point 1 :-

This is a test section to make sure that the sensor was working - I put my hand over the end of the tube

Point 2 :-

The blue line starts to decrease as light levels drop - from the differential plot you can see that the line starts to get more noisy - I think this is due to the fact that while the sun is up the sensor is still getting flooded with light and as the levels drop the LDR is able to pick up more changes in light levels - possible causes stars/side lights from neighbors houses etc.

Point 3 :-

There is a definite increase in light levels at 20:43:19 where the response time of the sensor increases from 59 milliseconds to 110 milliseconds this does not seem much, but if you look at the Differential change it goes from -2 to 41 an increase of 43 miliseconds and marks the start of the light increase.

Point 4 :-

At 20:44:33 the readings go from 134 to 79 a differential drop of 58 milliseconds and marks a return to the normal light levels reduction seen in the rest of the data.

Conclusion:

1. Light levels drop evenly over time, but it is possible to pick up smaller changes as it becomes darker due to increased sensitivity of the sensor.

2. The increase in levels over a 1 minute 14 second period starting at 20:43:19 I have think this is due to a security light at the end of our garden a distance of 20 m away. The light is set to come on and stay on for approximately 1 minute which fits with the duration on the chart.

3. It might be possible to increase sensitivity by making the inside of the shielding black instead of the white plastic currently used.

Summary

I think the sensor works great, it detects light changes far better than I expected, and was able to pick up changes at quite a distance, It would be good to try other types of sensor possible a Diode Array (basically LDR set to detect different frequencies of light)

Step 6: Experiment 2 : Trip Wire Laser Alarm

For this I enlisted my son as he has one of those SPY kids laser light alarms sets and at eight years old he thought it would be impressive to do.

Together we set up the laser and bounced it down the corridor using the set mirrors that came with the toy,The only bit we did not use was the toys alarm pickup replacing this with the Arduino Sensor.

Because of the shield around the LDR we where able to ensure that the laser hit the surface of the sensor. This time I think the white tube helped as it it reflected some of the beam inside and made it easier to get the most light onto the LDR as possible. The hope was that as someone broke the beam we would be able to :

• Firstly tell that the beam had been broken, and
• Secondly see if we could tell how long it had been broken for.

My Daughter had a friend around so we made it a game in two parts.

Game 1 - Avoidance (Image : Trip Wire Game Test 1)

The first was to see if they could move up the corridor without breaking the beam:

1. You can see on the chart that person 1 was the most hesitant going though the beams from the length of time between reading changes, but got quicker as she moved along. (this was My daughters friend)
2. My daughter took the longest time getting through and seems to take the least time breaking the beams when she did.
3. Tom was the the fastest and did not break as many of the beams as the girls did, but he did have the advantage of having helped to set the mirrors up in the first place.

Game 2 - Race (Image : Trip Wire Game Test 2)

Who could do the journey quickest by first breaking the initial beam (marking the start) and then making it to the end and back without breaking any more then finally breaking the same beam as before to mark the end of the run.

1. You can see a difference in the scale of the two charts the Game one chart spans 60 milliseconds while the Game 2 chart only 14 milliseconds - I have put this down to a possibility that the mirrors may have moved slightly during the first game (quite possible as its a laminate floor that has a little flex in it due to the insulation under it. This resulted in a lower response from the sensor, but still readable. I have not included the differential this time as it hides the run data.
2. Tom went first and did better than last time not breaking any beams except the initial bean at start and finish of his run, However he did take the longest
3. My Daughters friend went next and completed faster than Tom - I think the variation in her run was due to the reducing sensitivity as she bounced past the mirrors.
4. My daughter won the race she seems to have been more carful than her friend with the line being more stable she did not break any beams other than the ones she was supposed to, however she did spend a fair amount of time in the last beam, but as I marked the end as being the first change in levels she was the quickest.

All three enjoyed doing this, but it was tea time so we had to stop at that point.

Step 7: Using Plotly

I know Ive used Excel for my collection and initial charting and if that's all you want to do then fine stop there. But if you want to share your data or just generate a chart then what I originally thought would be a convoluted tool Plotly has turned out to be incredibly simple and user friendly..

You do need to sign up for an account at https://plot.ly/ but its free and you get a small amount of storage. To create your charts all you need do is select new grid and upload your data.

For the data in this guide the excel files have macros in which seems to make the upload take a while (but it does get there (walk away and get a coffee). A quicker way is the save the data to a CSV file without the extras and the upload that. From there simply select the type of chart you want and set the X/Y scales Plotly does the rest see the images in this section and compare them to the excel versions earlier.

Thank you for reading this instructable
If you enjoyed it or found it helpfull please take the time to vote in the competitions.

and if you would like to see more then please take a look at my other guides https://www.instructables.com/id/Direction-display...

or

https://www.instructables.com/id/Scratch-4-Arduino-...

or at my web site at

http://handycrafted.jimdo.com/