Creating Plots in MS Excel Using Visual Basic Code

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.

To begin…

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:

Columns of data in a simple MS Excel worksheet, showing three dependent columns (y1(t), y2(t), y3(t)) and one independent column, time.

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 draws upon four columns of data: three dependent variables and one independent variable.

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 chart

With 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 axis

With c.Axes(xlCategory)
 .MajorUnit = Worksheets("test").Cells(8, 10)
 End With

'adjust major unit on y axis

With c.Axes(xlValue)
 .MajorUnit = Worksheets("test").Cells(5, 10)
 End With

' find maximum y value

Dim maxY As Integer
 maxY = Worksheets("test").Cells(3, 10)

' find minimum y value

Dim minY As Integer
 minY = Worksheets("test").Cells(4, 10)

' find maximum x value

Dim maxX As Integer
 maxX = Worksheets("test").Cells(6, 10)

' find minimum x value

Dim 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 With

End 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.

Rowing Data Analytics: Reducing and Studying the Rowing Workout

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).

Figure 1: Downloaded rowing worksheet from Garmin Connect

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:

2017-07-08T14:09:31.000Z

The next entry is:

2017-07-08T14:09:34.000Z

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:

=MID($A$2,12,2)*60*60+MID($A$2,15,2)*60+MID($A$2,18,2)

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:

ns1:Time Absolute Time (seconds) Relative Time (seconds)
2017-07-08T14:09:31.000Z 50971 0
2017-07-08T14:09:34.000Z 50974 3
2017-07-08T14:09:35.000Z 50975 4

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.

Figure 2: Modified rowing spreadsheet with derived time, range and longitude-latitude calculations.

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.

Figure 3: Workout heart rate (pulse) versus time.

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.

Figure 4: Workout range versus time. Note linear behavior, indicating relatively constant speed.

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.

Figure 5: Workout measured speed versus time. Average is 1.85 meters per second.

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.

Figure 6: relative longitude and altitude of the workout.

In my next post I will focus on the athletic aspects of the workout related to training.

Rowing Data: Accessing Heart Rate, Distance and GPS Location from Garmin Vivoactive HR

An Introduction to Garmin Connect

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.

Garmin Connect: Accessing workout data within your Garmin Vivoactive HR.

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.

Detailed view of workout within Garmin Connect.

The drop-down box from the arrow shows a number of export options. Select “Export to TCX”, shown by red arrow in Figure 3.

Accessing export function within Garmin Connect.

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.

File download in .TCX format from Garmin Connect.

Once the file is downloaded, go to the file directory and locate the file you just downloaded, as shown in Figure 5.

Locating .TCX file in downloads directory on Windows platform

Then, change the suffix from .TCX to .XML, as shown in Figure 6. Accept the change when prompted.

Renaming .TCX file to .XML.

Now, open Microsoft Excel. Select the Data tab, as shown in Figure 7.

Microsoft Excel: selecting Data tab in preparation for .XML file import.

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.

Importing the .XML file into Microsoft Excel using the import
XML file option.

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.

Viewing available columns of data within Microsoft Excel.

In my next post on the subject I will describe how to manipulate these data for further analysis.

Read and Write Color Bitmaps Using Visual Basic inside of Microsoft Excel

Developers working with graphics frequently use applications that can read and transform image files. Whether they are transforming bitmaps into other file formats, or just reading the images in order to create new images from the pixel data, developers in this field regularly work with routines that access the base pixel data. Software methods written in C, C++ and Java are often used to read and write these files. The white paper attached below demonstrates the reading and writing of bitmap files using  Microsoft Excel using Visual Basic. A benefit of this approach is the Visual Basic interpreter inside the Macro editor is used, making the method extremely powerful and simple to code and use — no formal compilers or knowledge of C, C++ or Java is required to implement this set of Visual Basic methods.

The  simple Visual Basic method to read and write bitmaps runs in an Excel spreadsheet, which reads a .bmp file. This method opens, reads, and extracts the pixels of a 24-bit color bitmap, in accordance with a user-specified color. This method then writes the extracted pixels to a new bitmap containing only the selected color specified by the user. This straightforward method requires no compiler.

The original Microsoft Visual Basic bitmap reader/writer method was written in 2002: Read and Write Bitmaps Using Excel.