Excel FTDI Serial Data Logger
Bean
Posts: 8,129
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
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
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.
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
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.
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
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.
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 objectAlso 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
Bean
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.
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
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.
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.
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.
---
Mascot occupied. Please leave your messages with the Yak and then with the Yeti.
In fact, I first ran the Spin code in Ram on the QS that I selected for the job. Worked fine until I pressed the reset button on it, and found it was running the regular example when the thing is first connected. So I sent the Spin code to it, and chose the run from EPROM one, and off it went.
Both of you have a great idea there. But a suggestion, add one option that can be enabled at the time it is loaded into the editor, the option is that of the user could enable the board to accept digital data from the user's external hardware examples. Of course given that the Prop uses 3V3 logic and all of mine is TTL stuff at 5V, so I would need to insert a level shifting tool between them. Just a thought, and I do not expect it to be offered.
The demo I created is just a demo and can be modified for all sorts of things very easily, but since you are asking, I am led to believe that something may need to change within the VBA code to get what you are after. Unfortunately, I am not quite following your question, so I could use some clarification. Do you mean that a demo that simply logs the state of some input pins that are being fed from an external device would be beneficial?
Yes as matter of fact. That is indeed what I was thinking of. I believe I did frame it rather confusingly. I did try to do that with the Stamp and the plotting thing that was written back in the day for Windows from last century. Which never came off the right way around, the BASIC code worked, but the stuff for Excel did not seem to understand it.
--
Mascot offers, "Life needs more sleep. Wake me later.", said as he goes off for a nap.
Hope this comments fits in with this topic.
I have 2 or more RFID readers, all identical. All USB on the sing HID to place data in Excel 2016. However all come in to the same column of spreadsheet with no identification as to which USB device it came from. Only way to id is to know which was the originating port on the tree.
As each read comes thru it would be tagged (prefix or suffix or separate column) with the device number position or such. From there can be manipulated in Excel.
Can anyone head me in the right direction using PLX latest 32 bit.
Thankyou
Charles Harris
Hi @Bean,
I am currently using the ESP32-Camera for some maker projects as part of the course work too. However, I am currently faced with a "Run-time Error 53: ftd2xx.dll not found", and while I have tried finding means to change the file path of the Lib Clause to locate where my "ftd2xx.dll" file is in my laptop, as I am unfamiliar with Microsoft Visual Basic language, it has not been very fruitful.
Hence, may I kindly request any advice on how I might be able to resolve this error? Thank you very much for your time in advance. J!
Additionally, I was able to locate the exact file path of the physical dll file under "C:\Windows\System32\DriverStore\FileRepository\ftdibus.inf_amd64_49b3e24305b20ada\i386". Therefore, I am just interested in editing the file path on Microsoft Visual Basic's debugging window.
Justin