Stream Data from Arduino into Excel

Stream Data from Arduino into Excel © MIT

Stream and analyze live data from your Arduino microcontroller into Excel with the Data Streamer add-in!

  • 7,745 views
  • 3 comments
  • 24 respects

Components and supplies

Apps and online services

About this project

Intro

Microsoft Excel is a helpful tool to visualize and analyze data. The Data Streamer add-in allows you to import, view, and analyze live data from external devices like your Arduino microcontroller. This makes gathering data from sensors accessible, affordable, and user-friendly for a wide variety of applications.

Data Streamer, free w/ O365, reads values printed to your computer's serial port, which is the same method data is printed to the Arduino IDE Serial Monitor.

For this tutorial, you'll need to have Microsoft Excel O365 with Data Streamer enabled. For instructions on how to do this, visit the Data Streamer website.

Let's get started!

1. Connect your sensor to the Arduino microcontroller.

Use your own sensor or follow one of our tutorials. Here are some examples: Electroconductivity Sensor, Pressure Sensor, Electromagnetic Sensor.

2. Write and flash a sketch that reads in the sensor and prints the value to the Serial Monitor with a new line at the end.

Write your own or use the sample code in the attachments section.

Note: if you use the sample code, be sure to connect your sensor to Arduino Analog Pin A0.

3. Open Excel and navigate to the Data Streamer tab. Click Connect a Device to connect Excel to the microcontroller.

This will create 3 new sheets:

  • Data In: Live data is printed on this sheet.
  • Data Out: Send data and/or commands to the Arduino board using this sheet.
  • Settings: Change basic settings for Data Streamer, including the amount of live data and number of data columns.

4. Click Start Data to begin streaming data into Excel.

By default we only get 15 rows of data, but you can gather up to 500 rows of live data (limit is due to Excel bandwidth -- there's a lot happening in the background!).

5. To save data into a text (.CSV) file, click the Record Data button.

When you click Stop Record, you will be prompted to select a file name and location.

6. Add a Plot of your data! Do some data analysis!

Scatter plots show you how the sensor readings change over time. Add a Scatter Plot by going to Insert -> Charts -> Scatter.

When the plot pops up, right click on the empty chart and choose "Select Data," then Add a Series.

Add a title to your data series. Next, you'll want to display your data on the y-axis, with "time"* on the x-axis. To do this, click the arrow under the "Series Y-Values."

Go to the Data In sheet, and select all of the incoming sensor data.

Now you have a plot of your live data! This makes it much easier to visualize and understand changes in the sensor readings.

Youcan also do calculations and comparisons in Excel! To write a formula, click on an empty cell and type an equals sign ("="), then the calculation you want to do. There are lots of built-in commands like average, maximum, and minimum.

To use a command, type the equals ("=") sign, the command name, and an open parenthesis, then select the data you're analyzing and close the parentheses.

*If you want the actual time to be on the x-axis, select the timestamp in Column A on the Data In sheet for the x-axis values in your Scatter Plot. Either way, we'll see our data as it changes over time.

7. To send more than one column of data (AKA more than one sensor), print the values on the same line separated by a comma, with a final blank new line, like this:

Serial.print(sensorReading1); 
Serial.print(",");
Serial.print(sensorReading2);
Serial.print(",");
Serial.println();

That's it! Go forth and measure all the things!

Use this as a foundation to start exploring sensors, Arduino coding, and data analysis to tackle your questions, curiosities, and fav mysteries in this big, beautiful world.

Visit the Data Streamer website for instructions for simple sensor projects, or check out the Microsoft Hacking STEM website for more project ideas and full curriculum!

And of course, please leave a comment if you have any questions or need any assistance!

Happy making!

Code

Arduino Analog Sensor Sample CodeArduino
Use this code to read an analog sensor from Arduino analog pin 0 and print to the serial port (Data Streamer). This sample code can be modified for more and different types of sensors as well as to get data from Excel and send commands to the Arduino microcontroller.
// ----------------------------------------------------------------------------
// Sample Sensor Code for use with Data Streamer Excel add-in
// more info available from Microsoft Education Workshop at 
// http://aka.ms/hackingSTEM 
// 
// This project uses an Arduino UNO microcontroller board. More information can
// be found by visiting the Arduino website: 
// https://www.arduino.cc/en/main/arduinoBoardUno 
//  
// This code reads in a generic analog sensor on Arduino pin 0 and prints 
// it to serial.
// 
// Comments, contributions, suggestions, bug reports, and feature requests 
// are welcome! For source code and bug reports see: 
// http://github.com/[TODO github path to Hacking STEM] 
// 
// Copyright 2019, Jen Fox Microsoft EDU Workshop - HackingSTEM 
// 
// Permission is hereby granted, free of charge, to any person obtaining a copy
// of this software and associated documentation files (the "Software"), to deal
// in the Software without restriction, including without limitation the rights to
// use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies
// of the Software, and to permit persons to whom the Software is furnished to do
// so, subject to the following conditions:
//
// The above copyright notice and this permission notice shall be included in all
// copies or substantial portions of the Software.
//
// THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR 
// IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
// FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
// AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER 
// LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
// OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE 
// SOFTWARE. 
// ----------------------------------------------------------------------------

// Program variables ----------------------------------------------------------
int exampleVariable = 0;
int sensorPin = A0;

// Serial data variables ------------------------------------------------------
//Incoming Serial Data Array
const byte kNumberOfChannelsFromExcel = 6; 

// Comma delimiter to separate consecutive data if using more than 1 sensor
const char kDelimiter = ',';    
// Interval between serial writes
const int kSerialInterval = 50;   
// Timestamp to track serial interval
unsigned long serialPreviousTime; 

char* arr[kNumberOfChannelsFromExcel];

// SETUP ----------------------------------------------------------------------
void setup() {
  // Initialize Serial Communication
  Serial.begin(9600);  
}

// START OF MAIN LOOP --------------------------------------------------------- 
void loop()
{
  // Gather and process sensor data
  processSensors();

  // Read Excel variables from serial port (Data Streamer)
  processIncomingSerial();

  // Process and send data to Excel via serial port (Data Streamer)
  processOutgoingSerial();

// Compares STR1 to STR2 returns 0 if true.
//   if ( strcmp ("Apple", arr[0]) == 0){ 
//       Serial.println("working");
//   }
}

// SENSOR INPUT CODE-----------------------------------------------------------
void processSensors() 
{
  // Read sensor pin and store to a variable
  exampleVariable = analogRead( sensorPin );
  
  // Add any additional raw data analysis below (e.g. unit conversions)
  
}

// Add any specialized methods and processing code below


// OUTGOING SERIAL DATA PROCESSING CODE----------------------------------------
void sendDataToSerial()
{
  // Send data out separated by a comma (kDelimiter)
  // Repeat next 2 lines of code for each variable sent:

  Serial.print(exampleVariable);
  Serial.print(kDelimiter);
  
  Serial.println(); // Add final line ending character only once
}

//-----------------------------------------------------------------------------
// DO NOT EDIT ANYTHING BELOW THIS LINE
//-----------------------------------------------------------------------------

// OUTGOING SERIAL DATA PROCESSING CODE----------------------------------------
void processOutgoingSerial()
{
   // Enter into this only when serial interval has elapsed
  if((millis() - serialPreviousTime) > kSerialInterval) 
  {
    // Reset serial interval timestamp
    serialPreviousTime = millis(); 
    sendDataToSerial(); 
  }
}

// INCOMING SERIAL DATA PROCESSING CODE----------------------------------------
void processIncomingSerial()
{
  if(Serial.available()){
    parseData(GetSerialData());
  }
}

// Gathers bytes from serial port to build inputString
char* GetSerialData()
{
  static char inputString[64]; // Create a char array to store incoming data
  memset(inputString, 0, sizeof(inputString)); // Clear the memory from a pervious reading
  while (Serial.available()){
    Serial.readBytesUntil('\n', inputString, 64); //Read every byte in Serial buffer until line end or 64 bytes
  }
  return inputString;
}

// Seperate the data at each delimeter
void parseData(char data[])
{
    char *token = strtok(data, ","); // Find the first delimeter and return the token before it
    int index = 0; // Index to track storage in the array
    while (token != NULL){ // Char* strings terminate w/ a Null character. We'll keep running the command until we hit it
      arr[index] = token; // Assign the token to an array
      token = strtok(NULL, ","); // Conintue to the next delimeter
      index++; // incremenet index to store next value
    }
}

Schematics

Arduino Sensor Schematic
Generic schematic for an analog input device.
Uploads2ftmp2fde4ee081 aa2e 4d7d 9b98 e641ddfc0d3a2farduinoschematic k2vk4c89mw

Comments

Similar projects you might like

Hacking STEM Electroconductivity Meter

by Jen Fox and Hacking STEM

  • 3,284 views
  • 4 comments
  • 29 respects

Hacking STEM Electromagnetic Sensor

by Jen Fox and Hacking STEM

  • 2,111 views
  • 0 comments
  • 9 respects

Export Data from Arduino to Excel Sheet

Project showcase by ahmed ismail

  • 21,278 views
  • 1 comment
  • 14 respects

Hacking STEM Heat Shield Simulation

by Jen Fox and Hacking STEM

  • 1,319 views
  • 0 comments
  • 7 respects

Hacking STEM Flex/Pressure Sensor

Project tutorial by Jen Fox and Hacking STEM

  • 2,820 views
  • 0 comments
  • 11 respects

HackingSTEM Joystick

by Jen Fox and Hacking STEM

  • 853 views
  • 0 comments
  • 3 respects
Add projectSign up / Login