Excel FTDI Serial Data Logger

BeanBean Posts: 8,046
edited 2019-11-12 - 01:15:05 in Microcontrollers
There seems to be some interest in logging serial data into excel.

This is an Excel sheet that will log serial data from an FTDI chip which is used in Parallax USB-To-Serial devices.

Any questions please ask.

I have posted version 1.1 of the data logger.
I have posted version 1.2 of the data logger.

Bean

Comments

  • Bean,
    Thanks very much.
    This is all that is needed to capture csv serial streams into Excel for the Prop user.
    No need to mess around with the unreliable Microsoft Excel Data Streamer!
    I look forward to seeing some really impressive data presentation graphs and data management using this tool with Excel.
  • Thanks, I am already working on the next version which will allow receiving binary data (don't have to convert to ASCII).

    CHR(1)CHR(x) = Log "x" as unsigned byte value
    CHR(2)CHR(xh)CHR(xl) = Log "x" as unsigned word
    CHR(3)CHR(xh)CHR(xl) = Log "x" as signed integer
    CHR(4)CHR(xmsb)CHR(xhl)CHR(xlh)CHR(xlsb) = Log "x" as signed long

    These binary values do not require a TAB CHR(9) after them, it is assumed.

    This is tricky because the binary data could contain a CHR(13).

    Bean
  • WBA ConsultingWBA Consulting Posts: 2,880
    edited 2019-11-10 - 23:15:38
    Excellent work Bean! Checking it out now and so far doing exactly what I need, PERFECTLY, every connection. No more hokey Excel or PLX-DAQ mess.

    Comments:

    The "=Now()" operator in Excel will update itself if the sheet recalculates (IE: opening the file later will show the later timestamp when it is opened)
    When I use that function in macros at work, I immediately select the same cell then do a "Copy" and "Paste Values" so that the timestamp becomes text instead of a formula, so I modified your VBA code this way:
    If fieldStr = "[TIME]" Then
              Cells(row, col) = Now()
              Cells(row, col).Select                                                              edit by WBA
              Selection.Copy                                                                       edit by WBA
              Selection.PasteSpecial Paste:=xlPasteValues                           edit by WBA
            Else
              Cells(row, col) = fieldStr
            End If ' fieldStr = "[TIME]"
    

    Also, if a knucklehead like me uses this incorrectly a few times, the timestamp changes the cell it lands in to a date format, so when you dump data in that cell on the next "start" run, a simple decimal "88" from the Prop will show up as 3/28/1900, LOL. Anyhow, that's my mistake obviously, but one way to prevent that is to change the Clear Contents portion of the Start sequence to a Delete Rows function, so any formatting from the previous data collection gets wiped out.

    Anyhow, beautiful job Bean and thanks so much for taking this on. Your VBA skills are impressive.
  • The code puts the constant value of "=now()" into the cell.
    It does not put the "=now()" function into the cell.
    The value should not change when recalculated, unless there is something I'm missing.

    Anyway, here the a new version that allows binary values.

    Bean
  • Just tried out the latest version and yes, it is inputting the value of Now() as opposed to the function as I stated. I am not sure why my first runs were planting the function in the column, but working fantastic otherwise. I will try out the binary functions this week as well.

    The only issue I have is that I cannot get it to work unless I program my board at least once after I plug it in. If I take my programmed board that works perfectly dumping into excel and unplug it, it will not work when plugged back in. I either get random garbage data in Excel every 15-20 seconds or Excel will hang until I unplug the board (and then one row will have a long stream of junk data). This occurs on both Win7 Enterprise at work and Windows 10 at home. I am currently using a Propeller ASC+ with an FT231XS. The FT231 is slightly different than the usual FT232 used by most, so I wonder if that has something to do with it. I will try out a different board that uses an FT232.
  • Is there sample software that outputs data, for either BS2 or Propeller, that I can try this with?
    San Mateo, CA
  • WBA ConsultingWBA Consulting Posts: 2,880
    edited 2019-11-11 - 23:20:40
    Well, I have just been playing around with chunks of sample code, but here is a super simple demo using FullDuplexSerial and the RealRandom object in the Propeller Library. It uses the defaults that Bean has set up in his file (IE: only 10 records). Load this into a Prop and then hit the Start button in his file within 5 seconds. You should get output like the first attached "000" picture. In the "002" picture, I changed cell B5 to have the same formatting as cell B6 so that the 27 displays correctly and columns were widened to 145 pixels to fit in the timestamp. In no way does it truly show the power of Bean's work, but it does show the simplicity of using it.

    Bean, cell B5 in your last revision is formatted as a date field, so my "27" shows up as 1/27/1900. If I simply copy the format from cell B6 before I hit start everything works perfectly.

    Full code archive attached, but here is the entire program:
    {{
          Quick and dirty demo of Bean's FTDI_Data_Logger_1_1.xlsm
          Uses the default settings in the spreadsheet
          Some columns may need to be widened in Excel to show the full data
          WBA Consulting, 11/11/2019          
    }}
    
    CON
    
      _clkmode = xtal1 + pll16x
      _xinfreq = 5_000_000              '5 MHz clock * 16x PLL = 80 MHz system clock speed
    
    VAR
      'Globally accessible variables
      
      
    OBJ
      serial        : "FullDuplexSerial"
      rr            : "RealRandom"
      
    PUB Main
    
      'start the FullDuplexSerial object
      serial.Start(31, 30, %0000, 9_600)                    'requires 1 cog for operation
    
      'start RealRandom
      rr.start
    
      waitcnt(cnt + (5 * clkfreq))                          'wait 5 seconds for the FTDI_Data_Logger_1_1.xlsm Start button to be clicked
            
     ' Send 10 sets of data spanning 5 columns
      Repeat 10
      
         serial.Str(STRING("[TIME]"))             ' put a timestamp send as ASCii string
         serial.Tx(9)                             ' send a tab CHR(9)
         serial.Str(STRING("27"))                 ' send the number 27
         serial.Tx(9)                             ' send a tab CHR(9)
         serial.Str(STRING("Propeller"))         ' send "Propeller" as text
         serial.Tx(9)                             ' send a tab CHR(9)
         serial.Dec(42)                           ' use serial.dec to send the number 42
         serial.Tx(9)                             ' send a tab CHR(9)     
         serial.hex(rr.random, 8)                 ' send a randomly generated number in hex
         
         serial.Tx($0D)                           'print a new line
    
          waitcnt(cnt + (1 * clkfreq))            'wait 1 second 
    
      serial.Stop                                 'Stop the object
    

  • I will fix cell B5 for the next upload.

    Also I don't know why you have to program the board before it will work ? Maybe the CTS or RTS line is not set properly ?
    I will put a "RESET" button on the next release, maybe that will fix it.

    Bean
  • I have posted version 1.2 that has a "RESET" button.

    Bean
  • Bean and WBA,
    Thank you both for your selfless continuing development of the rock-solid FTDI Excel csv serial data logger.
    Your contribution really is a breakthrough open application for Prop 1 users, and I am surprised there is not more interest.
    I would have thought the Prop1 education sector would be all over this! Students can capture their project data straight into Excel and use it for debugging, improvements, presentation and so on!
    Although the Microsoft Data Streamer is very unreliable, at least it has a useful data capture page, with the following features;
    - a row presenting the current captured serial stream.
    - a rolling "data window" consisting of a range of previously captured serial stream rows which scrolls with the last captured stream on the top row.
    This is very useful for visualizing, graphing and saving the entire scrolling "data window" using normal Excel functions.
    Do you think this is a useful development direction? Does it involve a lot of complex VBA coding?
    I was going to give it a try, but I am a real VBA novice and I suspect it will be too much for me.



  • I have added (for the next version not posted yet) the status line at the bottom of the sheet will show the current serial line being evaluated.
    Scrolling data is not easy unless it is a limited number of row (maybe 100) because each cell must be moved (unless there is a function I don't know about).

    Thanks for the suggestions.

    Bean
  • Bean,
    Thanks, your status line showing the current serial line being evaluated will be a useful improvement.
    For the scrolling "data window", I was going to use the same "max lines" cell variable you use in your latest version (say max lines = 100), then incorporate the simplified VBA bulk copy function within the same Sheet, something like;
    Range ("A5:J104"). Copy Range ("A6:J105")
    just before the current serial line is transferred into row 5.
    This assumes a scrolling range from cell A5 to cell J105, i.e. 100 rows and 10 columns.
    The columns would be fixed, or also set by cell variable, say "max data channels".
    Is this worth trying? I may be wrong about the simplified bulk copy - I saw it on a VBA training spruik webpage.
  • WBA ConsultingWBA Consulting Posts: 2,880
    edited 2019-11-12 - 17:45:02
    Bean, 1.2 with my ASC+ and a Quickstart do the same thing. I did find out that I only need to do a "Identify Hardware" with the Propeller Tool to get it to work, not actually program anything. That's really odd.
    Otherwise, 1.2 is working perfect. (and I don't think the issue is with your VBA, but rather something with what the FTDI DLL is doing)

    As for the scrolling data topic, you could have the VBA code insert a row at the top of the data and the newest data always goes on row 5. Then, if you want to graph the last 20 data points, you just have the graph reference rows 5-24. But....
    You would need to convince Excel to not update formulas when you insert the row, which is close to impossible. (IE: a reference to B5 would get updated to B6 after the insert, but you want your graph to be from the same rows always) To get past this, you can use the Indirect function within Excel to address that. It's a little more Excel work with the formulas, but you only need to set it up once for however you are using Bean's sheet.

    Also, with Bean's added status line at the bottom of the sheet, you could harvest that for the indirect function to grab the most recent group of rows. So there are some options.

    I didn't get to play with this much tonight, I spent my free time working on an RPi/ESP8266 MQTT setup. Surprisingly, when from nothing to a working setup all in one night. That never happens.... I may see about making some code that will provide data usable for a graph tomorrow on a new tab of the sheet to try out my above ideas.
  • WBA ConsultingWBA Consulting Posts: 2,880
    edited 2019-11-16 - 08:59:34
    Sorry for the delay, but here is a Graph demo using Bean's FTDI sheet. I added one line of VBA code to give me the current row number. This allows me to leverage the use of Indirect references so that the graph always shows the last 10 readings.

    The SPIN code creates a data set every 2 seconds that includes a Timestamp, the chart title, logging number (used for axis labels), series names, and two "readings" from the real random object just to give the chart something to show. There is a 5 second pause at the beginning to allow you time to hit start on the DTA tab then switch to the GRAPH tab to watch the magic happen.
    Leaving everything else as defaults, this code and Excel sheet will create a graph of the last 10 data points. The code and sheet attached is setup for only 10, but you can change the Repeat line in the code to 20 and cell B2 on the DATA tab to 20 to see how the graph follows the latest 10 records.

    FYI: The project archive includes the modified data logger Excel file with the added GRAPH tab.
  • This is one of those "DRAT! I wish I thought of that first scenarios.", and I know @Bean you are considering responding, "Because I did!". I'm going to try these examples over the course of the few weeks and come back with a good understanding of what works for me, and what does not. (Probably nothing.) And then post a good review.

    ---
    Mascot occupied. Please leave your messages with the Yak and then with the Yeti.
Sign In or Register to comment.