' This macro reads in all CSV files in a directory and
' writes them to a single new worksheet ("Sheet1")
' in a new workbook (that is, the workbook in which this
' Macro is defined).
' The data from the separate CSV files are column-listed in' the Sheet1.
' Found at this web site: https://www.extendoffice.com/documents/excel/3388-excel-import-multiple-text-csv-xml-files.html
' Developed in this Macro 2018-01-08
' John Zaleski
' What follows is directly from the web site:
' UpdatebyKutoolsforExcel 2015-12-14
Dim xSht As Worksheet
Dim xWb As Workbook
Dim xStrPath As String
Dim xFileDialog As FileDialog
Dim xFile As String
On Error GoTo ErrHandler
Set xFileDialog = Application.FileDialog(msoFileDialogFolderPicker)
xFileDialog.AllowMultiSelect = False
xFileDialog.Title = "Select a folder"
If xFileDialog.Show = -1 Then
xStrPath = xFileDialog.SelectedItems(1)
If xStrPath = "" Then Exit Sub
Set xSht = ThisWorkbook.ActiveSheet
If MsgBox("Clear the existing sheet before importing?", vbYesNo, "Kutools for Excel") = vbYes Then xSht.UsedRange.Clear
Application.ScreenUpdating = False
xFile = Dir(xStrPath & "\" & "*.csv")
Do While xFile <> ""
Set xWb = Workbooks.Open(xStrPath & "\" & xFile)
Columns(1).SpecialCells(xlBlanks).Value = _
ActiveSheet.UsedRange.Copy xSht.Range("A" _
xFile = Dir
Application.ScreenUpdating = True
MsgBox "no files csv", , "Kutools for Excel"
I am frequently in need of generating scatter and line plots of measured physiological signals. To this end, I like to use many different types of software tools. The one I like the most, however, for rapid manipulation and visualization of data is Microsoft Excel.
Oftentimes I need to create charts from columnar data within Excel. Creating and formatting line plots, particularly if creating many, can be a time-consuming and tedious process. Therefore, I had decided on searching for approaches for creating automated chart plotting routines of the format I was looking for. Naturally, I turned to the Internet and to my various MS Excel textbooks.
To my disappointment, however, after spending what was perhaps a week of evenings searching, I honestly could not find anything that fit the bill on the open Internet. That is to say, it was not as if I could not find any help… simply that I could not find anything that already provided me with exactly what I was looking for or event a close template that I could pilfer and customize.
Thus, it became necessary for me to slog through the process myself. The purpose of this entry is to simply communicate what I found and did so that another wayward traveler might be saved from some time and effort… although, I can imagine that what I provide below is not precisely what someone else is looking for, either.
Nevertheless, and once you review my code, you may conclude “that really was not anything special… why did it take him so long to create that, and why so difficult to find comparable models that he could reuse?”
The answer to these questions is that oftentimes those pursuing the process need to educate themselves. Thus, it was not merely me taking another individual’s sample code…it was about me understanding that sample code. The understanding is often the hardest part of the challenge.
The objective is to create and X-Y plot, wherein the X-data and Y-data are columns in an MS Excel Worksheet, as shown in the figure below:
These data can be plotted either one dependent variable at a time or any combination of dependent columns versus the independent column. Accomplishing this manually using MS Excel is a straightforward task. Yet, if one wishes to develop a standardized template or create a common format, particularly if the plotting is to be repeated many times, performing this manually becomes overwhelmingly tedious.
The routine I developed creates a chart with multiple data series displayed versus the common independent axis. The plot generated by the Visual Basic code from these data is as shown below:
The code follows:
Sub plotData()' Purpose: Plots 3 functions versus time in Excel using
' visual basic programming.' 2017-12-31.' J. Zaleski' how many rows? Dim rCount As Long rCount = ActiveSheet.Cells(9, 10)' axis dimensions Dim xaxis As Range Dim yaxis As Range Dim yaxis2 As Range Dim yaxis3 As Range Set xaxis = Range("$a$3", "$a" & rCount) Set yaxis = Range("$b$3", "$b" & rCount) Set yaxis2 = Range("$c$3", "$c" & rCount) Set yaxis3 = Range("$d$3", "$d" & rCount) ' dimension chart Dim c As Chart Set c = ActiveWorkbook.Charts.Add Set c = c.Location(Where:=xlLocationAsObject, Name:="test")With c .ChartType = xlXYScatterLines 'A scatter plot, not a line chart! ' set other chart properties End With' add data series to chartWith c ' assign x and y value ranges to series 1.SeriesCollection.NewSeries .SeriesCollection(1).Name = Worksheets("test").Cells(2, 2) .SeriesCollection(1).Values = yaxis .SeriesCollection(1).XValues = xaxis .SeriesCollection(1).MarkerBackgroundColor = RGB(0, 0, 0) .SeriesCollection(1).MarkerForegroundColor = RGB(0, 0, 0) .SeriesCollection(1).MarkerSize = 2 .SeriesCollection(1).MarkerStyle = 3 .SeriesCollection(1).Format.Line.Weight = 1# .SeriesCollection(1).Format.Line.ForeColor.RGB = RGB(0, 0, 0) .SeriesCollection(1).Format.Line.BackColor.RGB = RGB(0, 0, 0)' assign x and y value ranges to series 2.SeriesCollection.NewSeries .SeriesCollection(2).Name = Worksheets("test").Cells(2, 3) .SeriesCollection(2).Values = yaxis2 .SeriesCollection(2).XValues = xaxis .SeriesCollection(2).MarkerBackgroundColor = RGB(128, 0, 0) .SeriesCollection(2).MarkerForegroundColor = RGB(128, 0, 0) .SeriesCollection(2).MarkerSize = 2 .SeriesCollection(2).MarkerStyle = 4 .SeriesCollection(2).Format.Line.Weight = 1# .SeriesCollection(2).Format.Line.ForeColor.RGB = RGB(128, 0, 0) .SeriesCollection(2).Format.Line.BackColor.RGB = RGB(128, 0, 0)' assign x and y value ranges to series 3.SeriesCollection.NewSeries .SeriesCollection(3).Name = Worksheets("test").Cells(2, 4) .SeriesCollection(3).Values = yaxis3 .SeriesCollection(3).XValues = xaxis .SeriesCollection(3).MarkerBackgroundColor = RGB(128, 128, 0) .SeriesCollection(3).MarkerForegroundColor = RGB(128, 128, 0) .SeriesCollection(3).MarkerStyle = 5 .SeriesCollection(3).MarkerSize = 2 .SeriesCollection(3).Format.Line.Weight = 1# .SeriesCollection(3).Format.Line.ForeColor.RGB = RGB(128, 128, 0) .SeriesCollection(3).Format.Line.BackColor.RGB = RGB(128, 128, 0)End With'adjust major unit on x axisWith c.Axes(xlCategory) .MajorUnit = Worksheets("test").Cells(8, 10) End With'adjust major unit on y axisWith c.Axes(xlValue) .MajorUnit = Worksheets("test").Cells(5, 10) End With' find maximum y valueDim maxY As Integer maxY = Worksheets("test").Cells(3, 10)' find minimum y valueDim minY As Integer minY = Worksheets("test").Cells(4, 10)' find maximum x valueDim maxX As Integer maxX = Worksheets("test").Cells(6, 10)' find minimum x valueDim minX As Integer minX = Worksheets("test").Cells(7, 10)With c 'locate chart.ChartArea.Top = 50 .ChartArea.Left = 400'adjust width and height.ChartArea.Width = 400 .ChartArea.Height = 200'adjust y-axis Scale.Axes(xlValue).MinimumScale = minY .Axes(xlValue).MaximumScale = maxY'adjust x-axis Scale.Axes(xlCategory).MinimumScale = minX .Axes(xlCategory).MaximumScale = maxX'adjust font.ChartArea.Format.TextFrame2.TextRange.Font.Size = 8 .ChartArea.Format.TextFrame2.TextRange.Font.Name = "Arial Narrow"'adjust font color.Axes(xlCategory).TickLabels.Font.Color = RGB(0, 0, 0) .Axes(xlValue).TickLabels.Font.Color = RGB(0, 0, 0)'adjust decimal places on x-axis.Axes(xlCategory).TickLabels.NumberFormat = "0.00" .Axes(xlValue).TickLabels.NumberFormat = "0.00"'adjust plot labels.Axes(xlCategory, xlPrimary).HasTitle = True .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = Worksheets("test").Cells(2, 1) .Axes(xlValue, xlPrimary).HasTitle = True .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Three Functions versus Time" .HasLegend = True End WithEnd Sub
The code is written as a macro and the data are maintained in a worksheet called “test”. The macro is associated with a button placed on the worksheet which, when pressed, creates a plot of the three dependent variables. The routine is not intended to be all-encompassing in terms of capability. But, to the uninitiated, the code serves as a building block for tailoring and customization. For instance, the routine requires three specific dependent columns of data. The .SeriesCollection field is specified with three indices. This can be generalized to any number of plots using a for loop indexing variable. Furthermore, the color schemes, tick labels, size, location, etc. are all customizable.
Completed the Concept 2 Holiday Rowing Challenge with 201,580 meters. Thus far this season have logged 482,862 total meters… and the year is not done yet. Targeting 500,000 at least by year end 2017. Rowing the erg in winter primarily and water rowing Spring-Summer-Early Fall on the Chesapeake Bay is for the most part how the split goes. Goal is to target exceeding these goals by 20% in 2018.
A summary of how smart alarms could have assisted patients monitored while in intensive care is provided at this Bernoulli Health link. This relates my experience many years ago and references the use of real-time data to better identify the onset of potentially adverse events.
Heart Rate Measurement Using Garmin & Polar Wearables
A study was made of the Garmin Vivoactive HR and Polar H10 chest strap in terms of comparative heart rate assessments. The units are shown in Figure 1 below. The two units involved included a wrist-based sensor (Garmin Vivoactive HR) and a chest strap (Polar H10).
This follow-up focuses on 20 minutes of water rowing using both units in an effort to assess the heart rate measurement consistency and reliability. Both watch and chest strap were properly attached with no movement between these devices and the skin. Data were collected and then downloaded and processed through a Microsoft Excel spreadsheet. The data were time-synchronized so that corresponding data points from each device were associated in time. A summary of the analysis is provided here.
Time-Based Plots of Heart Rate
Overlay scatter plots of heart rate measurements versus time were made and are as shown in Figure 2.
A general observation from the data is that the heart rate measurements from the two devices seem to overlap reasonably well as viewed by the naked eye. But there are key drops in measurements, particularly with the wrist-based heart rate sensor, that show as deviations in the overlap of the two signals. This can be seen more readily via the correlation curve shown in Figure 3. The correlation coefficient of 0.91 was determined between the two sets of measurements. It should be noted that the wrist-based sensor was snug with no movement on the wrist. Ambient temperature was approximately 80F.
As I showed in a previous post, there was a serious issue with the wrist-based sensor in which there were data dropouts with some significant time lags between measurements. In the case of the wrist-based sensor for the associated measurements here, this was also experienced. For comparison, I show histogram plots of the time intervals between measurements for both the wrist-based sensor (Figure 4) and the chest strap (Figure 5). The wrist-based sensor experiences a significant number of events in which the time between actual measurements are greater than one second. Indeed, from the figure, only 83 measurements during this interval were obtained within one-second of one another! There were a significant number of measurements in which the interval was > 1 second, with one as high as 40 seconds. The overall quantity of measurements was thus reduced to approximately 430 during the workout. On the other hand, the chest strap consistently measured at one-second intervals for a total of approximately 1320 measurements.
Chest straps are much more reliable for heart rate measurement versus wrist-based sensors. Users of wrist-based sensors for heart rate measurement should be advised that measurements can be in question, as results illustrate here. This is not to say that chest straps are the gold-standard. Clearly, ECG measurement similar to those obtained through stress-testing are of diagnostic quality. Yet, for rate measurement chest straps are quite adequate and seemingly reliable.
In my last post on the Designer’s Choice, I received a comment on the placement and view of the boom clew car. Am providing some photographs here to illustrate the placement of the clew car within the track on the boom and the view of the car itself.
Heart Rate Measurement Using Garmin & Polar Wearables
A study was made of the Garmin Vivoactive HR and Polar H10 chest strap in terms of comparative heart rate assessments. Three different types of tests were conducted while the author wore these devices. The units are shown in Figure 1. The Garmin unit is able to be used with a number of sports, including rowing, and provides measurements of heart rate, stroke rate, distance per stroke, split times, and also provides for location tracking during the workout. Data can be uploaded to http://connect.garmin.com/and are also available for download in TCX (an XML format) as well as splits downloads in CSV format. The Polar H10 is strapped around the chest just below the level of the breast bone. This unit, too, can upload data to the http://flow.polar.com/web site, where data can be downloaded in TCX format, as well. In order to provide some variety, I considered three different activities:
General workout, involving weight lifting, sit-ups, squats;
Walking for 1 mile; and,
Indoor rowing for 15 minutes.
In all cases, both the Vivoactive HR and the H10 were attached, with the Vivoactive HR snuggly affixed to the left wrist. Both watch and chest strap were properly attached with no movement between these devices and the skin. Data were collected and then downloaded and processed through a Microsoft Excel spreadsheet. The data were time-synchronized so that corresponding data points from each device were associated in time. Plots of the measurements were made.
Time-Based Plots of Heart Rate
Overlay scatter plots of heart rate measurements versus time were made of all three activities, shown in Figure 2 through Figure 4. Data were downloaded from the Garmin & Polar cloud sites and were uploaded into MS Excel. The data were then time synchronized using visual basic to align the measurements.
Heart Rate Comparison: Walking
Measurements of heart rate were taken during a one mile walk. The heart rates were plotted against one another and the correlation coefficient was computed between the two sets of measurements. In the case of the comparison shown in Figure 5, the correlation among measurements was rather poor: the correlation coefficient was determined to be -0.54. Perfect correlation is given by the diagonal line in the figure. Interesting to note is that the data points taken from the Vivoactive HR time variance. In the case of the Vivoactive HR, in some instances, the time between measurements was as high as 47 seconds with 62 measurements in the 12-14 second interval range, whereas in the case of the Polar H10, all measurements were 1 second interval. Thus, the number (quantity) of measurements taken by the Polar H10 were far denser than those of the Vivoactive HR.
Heart Rate Comparison: General Activity
In the case of general activity, which included some weight lifting, sit-ups, leg raises and standing exercises, the heart rate comparison is as shown in Figure 6. The correlation coefficient among these measurements is a bit higher at 0.60. The variation in measurement collection time associated with the Garmin HR was even higher here, with one measurement interval as high as 88 seconds!
I have hypothesized that the wide variation in data collection time may be due to arm motion that is not experienced to the degree in walking. I also have hypothesized that the improved correlation may be due to the higher heart rate, which is more easily detected by the Vivoactive HR. We will see some supporting evidence of this in the final section on indoor rowing.
Heart Rate Comparison: Indoor Rowing
Rowing on the Concept 2 PM5 unit while wearing both the Vivoactive HR and the Polar H10 produced the results as illustrated in Figure 7. The correlation between the Vivoactive HR and the Polar H10 is much higher here, with a correlation coefficient of 0.95. Several items of note: the variation in measurements with the Vivoactive HR is much lower, with only two measurements 19 seconds apart and most measurements having 1-2 second intervals. This complies much more closely with the 1-second measurement intervals of the Polar H10. Furthermore, heart rate measurements are much higher here: some measurements as high as 165 beats/minute (during sprints). In general, corroboration between the two units is better as heart rate measurement is higher. This could be due to more accurate peripheral measurement.
Based on the limited sampling and workouts thus far, the general conclusion regarding heart rate measurement “trust” is that the Polar H10 is more reliable based on several observations: (1) data collection time variation remains consistent at 1 second; and, (2) data density remains high with no dropouts in any of the workouts. This is not a surprise in general as the conventional wisdom is that chest straps are much more reliable. Yet, I wanted to quantify this reliability using some objective measures. It should be noted that while heart rate remains somewhat questionable with the Vivoactive HR, I have found that stroke rate measurement in comparison with the Concept 2 PM5 measurement is dead on accurate (at least based on the data I have observed).
In my last post (“Rowing Data…”) I discussed the steps associated with downloading the Garmin Vivoactive HR data from Garmin Connect to an Excel spreadsheet. In this post, I’m going to take the reader through the analysis of the data as a tutorial and guide for assessing certain elements of these data.
Raw data in Excel format are shown in Figure 1. I am going to focus on distance (column M), speed (column N), and heart rate (column O).
I normally like to study discrete, time-based data by translating the time component from the Zulu time (column L) into a relative time from the start of the workout. Furthermore, I like to translate these into units of seconds as the base unit.
To do so, we can take advantage of some powerful capabilities contained within formulas inside of Microsoft Excel. For example, the start time listed in column L begins with the entry:
The next entry is:
These are “Zulu” time or absolute time references. We wish all future times to be keyed or made in reference to the first time. In order to do so, we need to translate this entry into a time in seconds. We can do so by parsing each element of the entry. These entries are listed sequentially in column L2 and L3, respectively.
Each element is translated into seconds by parsing the hours, minutes and seconds using the following formula:
The first component extracts the time in hours and translates into seconds. The second component extracts the “minutes” and translates into seconds. The third component extracts the “seconds” element by itself. The total time is the superposition of all three individual components.
Thus, what I normally do is to copy the contents of the initial spreadsheet into a new sheet adjacent to the original and then begin working on the data. Presently, I am in the process of developing an application that will perform this function automatically. Yet, here I am “walking the track” associated with analyzing the data in order to chronicle the mathematics surrounding the process.
The hour, minute and second can be extracted as separate columns. Let us copy the contents of column L in the original spreadsheet into a new sheet within the existing workbook and place the time in column A of that new sheet. Thus, the entries in this sheet would appear as follows:
Absolute Time (seconds)
Relative Time (seconds)
The Absolute time in the middle column is the time in seconds represented by the left-hand column relative to Midnight Zulu time. The right-hand column is the time relative to the first cell entry in the middle column. Thus, zero corresponds to 50971-50971. The entry for three seconds corresponds to the difference between 50974 (second entry) and 50971 (first entry), and so on.
I also created some columns to validate parameter entries. For instance, the reported total distance and speed (in units of meters and meters per second, respectively), in column M and N and the heart rate, in column O, are referred to next. I created a new column O in the new spreadsheet to provide a derived estimate of total distance, which I computed as the integral of speed over time. The incremental distance, dS, is equal to the speed at that time, dV, multiplied by the time differential between the current time and the previous time stamp, dt. Then, the total distance is the integral, or the summation of this incremental distance and all prior distances. I reflect this as column G in the new worksheet, shown in Figure 2.
What follows now are plots of the raw and derived data. First, the heart rate measurement over time is shown in Figure 3. Note that the resting rate is shown at first. Once the workout intensifies, heart rate increases and remains relatively high throughout the duration of the workout.
The total distance covered over time is shown in Figure 4. This tends to imply a relatively constant speed during the workout due to the linear behavior over the 8700+ meters.
The reported speed, as measured via GPS, shows variability but is typically centered about 1.85 meters per second. The speed over time is shown in Figure 5.
The GPS coordinates are also available through the Excel data. I have subtracted out the starting location in order to provide a relative longitude-latitude plot of the workout, shown in Figure 6.
In my next post I will focus on the athletic aspects of the workout related to training.
For those who use the Garmin Connect Dashboard (https://connect.garmin.com), to synchronize their Garmin fitness devices, there is a fairly straightforward method for downloading higher-frequency data from the workout relative to heart rate, distance and GPS location that can be directly imported into Microsoft Excel for further analysis.
Getting Started with the Download
From inside of Garmin Connect (Figure 1), select a specific activity. In this example, I am picking my latest rowing workout, shown by the red arrow.
Once you have selected the specific workout, click on it and this will take you to the details of that workout. Once there, navigate over to the gear on the right-hand side, as shown by the red arrow.
The drop-down box from the arrow shows a number of export options. Select “Export to TCX”, shown by red arrow in Figure 3.
Upon selection, the file will be downloaded, as shown in Figure 4. On a Windows platform, this will be downloaded by default to the user’s downloads folder.
Once the file is downloaded, go to the file directory and locate the file you just downloaded, as shown in Figure 5.
Then, change the suffix from .TCX to .XML, as shown in Figure 6. Accept the change when prompted.
Now, open Microsoft Excel. Select the Data tab, as shown in Figure 7.
On the left-hand side, select Get External Data “From Other Sources”, and scroll down to “From XML Data Import”, as shown in Figure 8.
A dialog box will open. Navigate to your newly-created XML file. Select it, and click the series of “OK” buttons in the dialogs that come up, including the one placing the location of the start in cell $A$1. Once completed, the contents of the file will be imported into your spreadsheet. Heart rate data will be contained in column O, as shown in Figure 9. Distance & speed are contained in columns M & N, respectively. GPS latitude & longitude are contained in columns P & Q, respectively. Average speed is contained in column R.
In my next post on the subject I will describe how to manipulate these data for further analysis.