Archives for category: NXT2excel

There is a new version of NXT2excel available. This version is improved on the following points:

  • Working with different versions of Excel.
  • Speed. Excel is now the limiting factor.
  • Recognition of NXT. Connecting to a printer is no longer possible.
  • Error handling.

It still only works with NXT environments that support full duplex and BT mailboxes. So, robotC yes, NXT-G and NXC no. But the good news is, I’m working on that.

Download NXT2excel from here.

This post explains how to use excel templates for use in NXT2excel.

When using NXT2excel for monitoring the NXT in real-time one can use an empty excel file to write the observations to. You can also use a template, this allows you to add some extra analyzing power. For example you can compute values from other values using excel formulas. You can add graphs that are updated real-time. Or you can format your results to make it look better. I’ll explain how to do this, but before I can do so you’ll need to understand how NXT2excel writes observations to your excel sheet.

The picture above shows the first five rows of an excel template for NXT2excel. Only these rows matter when making a template.
Row 2 is the most important row as this is the row NXT2excel writes its observations to. In this example it is assumed that 6 values are sent from the NXT to excel. Values will be written starting at column A and so on. The 6 green cells will be filled with data. Row 2 will always contain the most recent data, so you don’t have to scroll to see it.
Row 1, marked blue, is not used by NXT2excel. You can use it to add labels so you know what you are looking at. 
Row 3 is also not used and serves the same purpose.
Row 4 and later, marked purple, are used to store the complete history of all values received. Newer data is added below older data. These data rows are filled by copying row 2 and inserting its contents into the first empty row.

Adding formatting

To get a nice lay out for your data you can add formatting to row 2. There is no need to format the entire column as the formatting of row 2 will be copied with the data itself to row 4 and on once the data starts coming in.

Adding calculated values

If you want to add calculations to your template you can do so by adding formulas to row 2, marked in red. These formulas will be copied with the data to row 4 and on. This means that you don’t have to fill an entire column with your formula before hand. In this example the speed of motor A is calculated. When you use this example you’ll never see a valid result in row 2 because to calculate the speed you need two observations, the most recent one and the one before that. But, once the formula is copied together with the data to row 5 and later it will show the speed of the motor.

Adding graphs

You can add graphs that are updated real time. For this to work one has to put the graph on another sheet. You’ll have to  let your graphs point to row 4 to row 5 for data. This way they’ll grow when new data arrives. This is also why there are two rows that are marked purple. (NXT2excel will insert an empty line just before row 5 before copying data thus ensuring the data range in your graph grows every time new data is recieved.)

Some tips

  • I used background colors in the example. These are only used to point out the different regions. You do not have to use background colors  in your template.
  • One can add as many formulas as one likes. But remember all data columns are always to the left of formula columns.
  • When developing graphs it is advised to manually add data to rows 4 and 5. This will help you seeing what your graph is going to look like.
  • Calculations and graphs will slow down excel and NXT2excel. As a result you can lose data. If you do not want to lose data there are some options. Send data from the NXT less often, add graphs and formulas after you received the data, minimise excel when receiving data or limit the maximum number of observations in NXT2excel.
  • Make your templates read only so you can reuse them over and over again without having to clean them.

This post introduces NXT2excel, a pc utility that allows one to send data from a NXT program to excel over BlueTooth.

NXT2excel runs on the pc. It initiates a BlueTooth connection with a NXT and writes data it receives from the NXT to an excel spreadsheet. This allows the user to examine or process the data. Users can automate the processing of the data with the use of a previously built worksheet. This makes it possible to have treal time graphs.

Disclaimer: NXT2excel is in beta stage. It might not always work as expected, it sure is not fool-proof. Use it at your own risk.

NXT2Excel is not working with NXT-G! Currently it will only work with robotC.

Using NXT2excel

It is very important to terminate the NXT program before terminating the Bluetooth connection. If not, the Bluetooth connection cannot be terminated normally and the NXT will have to be shut down and started over before BlueTooth can be used again.

Port: The port number of BlueTooth. Please note that the PC and NXT must already have been paired.
Connect/disconnect: Open or close a connection with the NXT. Please open a connection before starting the NXT program and close it after terminating the NXT program.
Mailbox: The number of the mailbox the NXT is sending messages to.
Worksheet: The Excel worksheet that will be used for writing, read only. The worksheet has to be opened from Excel, after opening the worksheet one should press the reset button.
Reset: Press this button after opening a worksheet. The reset button will tell NXT2excel what worksheet to write to. It takes the worksheet that is active when pressing the reset button.
Limit observations: By default NXT2excel will add a new row in excel for every received message. The excel worksheet can grow very large as a result. By limiting the number of observations one tells the program to remove observations that are older than the limit. As a result Excel only contains the N most recent observations.

Sending data from the NXT

Data is sent from the NXT using messages. By design a NXT message can contain up to 58 bytes, 4 are used for overhead functions, leaving 54 bytes to be used. NXT2excel only handles integers, you can send up to 27 in each message. Look in the robotc documentation for detailed information about sending and constructing messages.
Alternatively you can construct and send messages using the robotc library that is provided with NXT2excel. To use this library include the file NXT2excel.c in your program. To store values in a messages you use the NXT2excelAddValue function. It takes two parameters, the first being the integer value to store, the second being the excel column number where the value should be written to. The first column is reserved for a timestamp that is automatically added to the message.
Once a message is filled it can be sent using the NXT2excelSendMessage function. This function takes no parameters.

Using excel sheets

By default NXT2excel uses a new and empty excel sheet to write data to. The data is dumped on the first sheet, see the first picture for an example. You can however specify an existing sheet as a template. This sheet will then be used to write data to. A template must not use the first sheet as this is used by NXT2excel. The second row of the first sheet always contains the most recent values. Row 4 and later contain the history of values. When referring to data in excel functions or graphs one should refer to the range A4:A5 and so on. This range will be expanded automatically by excel. This will make sure your template keeps working with increasing number of data rows. The second picture shows some real-time graphs using a template. 
Please notice that NXT2excel closes excel sheets without saving them. If you want to save your data you’ll have to do so manually.

Example

The utility comes with an example program for the NXT. It shows how to use the provided source library and robotC functions. It als has an excel template that shows you how to create a template with a graph that updates as more data is fetched from the NXT.

Download

You can download the program from here.