For cyclists who ride with a Polar Heart Rate Monitor and use the Polar Flow App to track your rides. This video shows how you can export a ride from Polar Flow and import that data into Excel. The visualization in this analysis shows speed and heart rate on a map.
Table of Contents
(0:00) Welcome
(2:15) Demo of finished visualization
(8:36) Exporting Data from Polar Flow
(10:41) Importing & Cleaning Data using Power Query
(18:14) Mapping the Data
(23:41) 3D Map navigation tips
Excel Virtually Global Conference: 50+ Hours of Excel Training for US $24 With Proceeds to Charity
Table of Contents
(0:00) Welcome
(2:15) Demo of finished visualization
(8:36) Exporting Data from Polar Flow
(10:41) Importing & Cleaning Data using Power Query
(18:14) Mapping the Data
(23:41) 3D Map navigation tips
Excel Virtually Global Conference: 50+ Hours of Excel Training for US $24 With Proceeds to Charity
Transcript of the video:
Learn Excel from MrExcel Podcast, episode 2337.
Analyze Polar Flow Cycling data in Excel. Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
There are two audiences for this video. The first audience is going to be completely new to me.
This first audience is for cyclists who are using a Polar heart rate monitor and the Polar Flow app. You go out ride your bike.
It monitors all that data. It gives you a cool dashboard.
I can't complain about the dashboard.
But you may want to take a look at your data in Excel.
This video is going to show you how to take that data from Polar Flow, Download it to your computer, and then a fairly deep dive into two rarely used Excel functions.
Even if you use Excel all the time, the odds are that you haven't used Get and Transform Data. It was new in Excel 2016.
And then the 3D Map feature. I am going to show you both of those.
I will go through it very nice and slow. Now, this requires Excel for Windows.
Excel 2016 or newer.
If you're using Excel on an iPhone, Excel on Android, Excel on a Mac, Excel on your Commodore-64, sorry - it's not going to work for you.Now, the other audience, my regular Excel viewers: Hey, how's it going? Great to see you again.
A couple of Excel things that are new here for you.
This is the first time that I've ever loaded XML data into Power Query.
I had to Expand Table repeatedly to find the actual data.
It didn't look like it was going to work, but it worked.
Also new: Using two layers in 3D Map (Power Map).
Depending on which audience you are, You might want to open the Table of Contents down there in the video description to jump to the right spot for you.
By the way, for my Excel friends.
This was going to be part of my Excel Virtually Global 2020.
My one hour session on mapping data in Excel. But it was too long. I ran out of time.
If you happen to be watching this in July of 2020.
July 21st through 23rd - there are over 50 Excel gurus each doing a seminar. The whole course is only US$24 or Australian 33 dollars.
All proceeds to charity.
Check out the link down there in the YouTube description. There are going to be some some great things.
I can't wait to learn from all these other Excel gurus around the world.
All right so now for those Polar Flow cyclists who use the heart rate monitor. Let me show you what my dashboard is going to give you.
I don't think that you're actually going to be able to get in the dashboard in Polar or even if you take your data over to Strava.
So, we're going to switch over here to another workbook that I've already built and that I've been using for a couple of weeks with my rides.
So here's the workbook that I built a few weeks ago. I said, "oh this is cool. I should do a video about it".
It has the time, latitude, longitude distance in meters, BPM (that is heart rate).
And then miles per hour.
Yes, here I am in the United States - we are one of the last countries that are still using imperial units.
If you are anywhere else in the world, you're going to convert this to metric.
What's really amazing, other than just being able to get this data in Excel and look for various trends, is this feature called 3D Maps.
It was originally introduced as an Add-In after Excel 2013 came out.
It was an add-in. Then part of Excel 2016.
It lets you take that data in Excel, and visualize it on a map.
Or, in this case an actual satellite image of my neighborhood. So here's a ride that I did when i i first built the workbook.
There is a time slider down here.
I can actually animate the whole ride.
Here is me taking off from the house.
You can watch this whole thing unfold. Very Fast. Yes, Strava has a replay feature.
But what is different, with mine is that i can go to any particular scene and I can zoom in.
I am going to hold down the alt key. Drag the mouse up.
What i have here is the green is miles per hour.
So, right there i was going 12 miles per hour. Red is heart rate. So 107 at that point.
So I can see as i'm increasing or decreasing my speed does my heart rate go up or down.
This video - admittedly a long video - I hope that the cyclists stick with me i'm going to try and go really slow and show you how to build this the awesome tool.
The important thing about using Power Query and 3D maps: Let's say tomorrow you go on a different ride.
You download a new ride from Polar Flow.
We're going to put it in the same folder with the same name. I call mine Polar.TCX.
Once you've taken the time to follow this video.
And build this then we can go to the Data tab and click Refresh All.
And the 10 or 15 minutes you spend to analyze this data the first day will automatically update and you see the new data. This for me is a longer ride.
Let's see if I need to fill in some Miles per Hour.
That's one thing i couldn't do in Power Query.
Some of my regular Excel viewers are going to say, "Oh you can do that in Power Query". I'd love to hear about it in the comments below.
It was something that i could not get to work.
So there's my data in Excel and then we go into 3D Map.
Insert, 3D Map. Open that and then a second Refresh.
We have to refresh twice.
Alright and so now I have a completely different ride. This is actually a ride from a couple of weeks later.
I'll rotate this around. We'll go back to the very beginning.
So this was one of my longest rides where I kind of rode around Merritt Island here.
For me, 13 miles which I realize for most of you cyclists is nothing.
But trust me, when I moved here two months ago, I could barely ride a mile and a half, so for me that was a huge victory to be able to do this ride.
We are living here in Florida.
The funny thing about Florida is when I see my friends on Strava, and your elevation rise where you gain a thousand meters in elevation. Yeah, we can't do that.
My house: the elevation of my house is 8.
Literally eight feet above sea level.
I remember on this particular ride one thing that was funny to me is i was had to go up over the Sea Ray Drive bridge.
and that that was going to be challenging for a couple of reasons.
First off, Sea Ray Drive has been closed since Hurricane Matthew, so no car traffic is allowed to go there.
First I asked one of my cycling buddies - "hey can you get through?" He said, "Oh yes.
There are some barriers, but they moved one of the barriers away so the cycle can get through.
A lot of cyclers ride that area," I remember that just going up over Sea Ray Drive, Here - Up over that bridge.
I don't know - it's probably an elevation gain of maybe 20 feet or something like that.
i remember that that for me it felt kind of brutal.
So let's take a look at this data.
Just as i get up to the bridge. So that little stretch, there.
Where i go from completely flat up to the top of the bridge.
I will just grab this slider here. Right at the approach.
I am going 13.67 miles per hour and by the time i get about halfway up the bridge, right there.
That 13 MPH, or 15 MPH - I was actually going 15 MPH and then it falls off to 9.2.
For me, that little hill was the first time really I had encountered a hill.
All of Florida, at least here in Merritt Island, It is flat. I might go up a curb or a speed bump and that it the most elevation that I ride.
Check out my heart rate. Up in a zone that i generally don't get to.
It was 126 to 127.
Usually I am down in the 115 to 120 range. So this was good.
I was able to see the speed go down and the heart rate increase.
Something that i remember from the road, I'm able to come back and actually analyze that data in Excel.
So having both the speed and the heart rate here together point by point is interesting.
Then once I hit the peak of the bridge, all of a sudden then I am going downhill for that little bit of the bridge.
And back up to 11 miles an hour.
You know you can go back and see.
Here I hit some sort of a max of 17 miles an hour on that nice straight-away after the bridge.
So, that's that's the goal here.
For the cyclists who are not regulars on the MrExcel podcast. That's our goal.
That's what we're trying to build. Here now i wrote out all the steps.
31 31 steps and trust me, if you go through this video, use the pause button.
You will be able to follow these steps and build this very cool dashboard yourself.
So the first thing we're going to do is we're going to use the Polar Flow Polar.com website. Go to your dashboard.
Find some ride that looks interesting and choose that ride.
All the way down here at the bottom is something called Export Session. They offer you three different file types: TCX, CSV.
As Excel people - CSV that's the way to go?
No, it actually turns out that the TCX has more information that we need.
It comes down here as a TCX file with my name and the date.
Because we want to be able to build this once and reuse it again and again, you are going to go and rename that file.What I've been calling it is Polar.TCX.
This is important. You'll see in Power Query .We are going to teach Power Query that we are looking for a file called polar.tcx.
Take this old file and rename it it PolarBigLoop.
Then the file that i just downloaded.
I will right-click and rename that to just be called Polar. So Polar.TCX.
if we take a look at this file.
I am able to see that it had the latitude, time, the heart rate bpm.
Sensor state is Present which is not something I need.
I just kind of looked through the XML here and i was able to find that it's all tucked inside of...
Right before the Time is something called Trackpoint.
Which seems to be the element.
So, we are looking for an element called Trackpoint in the data.
And the trackpoint is contained (where's the very first TrackPoint?) It is contained in something called Track.
That's in something called Lap.
So we are going to drill down through Lap, then Track and then Trackpoint in order to build this.
So we have a file called Polar.tcx. I am going to put it in my Downloads folder.
for you of course it's gonna be a different path. It's not going to be Bill J.
It's going to be something else.
We come to a blank worksheet in Excel like this. Under Data, Get and Transform Data.
In Office 365 is the leftmost group.
Back in Excel 2016 it was the third group. It was over here. So look for Get and Transformed Data.
I am going to say Get Data, From File.
Not from a workbook but from XML.
They start out here in this folder.
I am going to paste the downloads folder.
They are looking for XML files but what Polar is giving us is a dot TCX file.
So Iam going to go to change that to All Files. There is my polar.tcx.
Click import. Again you want to be careful.
If you're going to take the time to do the next 20 steps make sure that you have renamed this to be Polar.TCX.
That way, tomorrow, you're going to be able to redo all of this in two clicks.
Instead of having to go through 20 steps. Click Import.
They are establishing a connection to Polar.TCX which means that they're analyzing it.
They have here something called Author and Activities. Activities is expandable.
So expand Activities and then something called activity.
Now, I have got to tell you this does not look really encouraging.
There is only one row that they can see. But, there is something called Lap.
We are going to be able to expand that Lap.
So we choose Activity. Do NOT choose Load. Instead choose Transform Data.
Hey, for the cyclists: You use Excel at work all the time, right? I get it.
You've probably never seen this screen. This is called Power Query.
It's the most amazing thing for taking ugly data and cleaning it.
You clean it once and then it'll remember those steps and help you clean it again.
Next week. Next Month. Tomorrow. Next Hour.
Okay, so the segment of the XML that we are looking for is inside this field here called Lap.
So when I look at Lap, it has all kinds of great information.
How many calories, average heart rate, and so on.
But really, the only thing that I want from here is the thing called Track.
In other words, the tracker. The track point.
So i choose that and click ok. Now I have Lap.Track.
When i expand that, it has something called Trackpoint. That is great. Click OK.
All right. And then, finally, here, once we get to TrackPoint.
That's where I, get Time, Position, Distance in meters, heart rate BPM.
Sensor State is just saying whether the sensor was sending data. I don't need that.
I'm going to uncheck that part.
I don't need this to say Lap.Ttrack.Trackpoint.Time. I just want it to say time.
So in this case, since this is the actual important one, I am going to uncheck the box for "Use Original Column Name As Prefix".
So now I get ID, Time, Position is still a table.
We are going to expand that in a minute. Distance in meters, Heart Rate BPM.
Training, Creator, and the sport in this case was Biking.
I don't need these columns out here.
So i'll right click and say remove those columns.
For heart rate bpm when i expand that, there is only one option. It's just called Value.
Distance in Meters we will keep. Then Position.
This is what's going to allow us to map the data because it has Latitude and Longitude. All right.
So right now it all looks pretty good. We don't need this column on the right.
So right click and Remove. See.
Power Query is just a little bit different than Excel. In Excel, you would right-click and Delete.
But this was written by the SQL Server Analysis Services team.
All right, now. We need to fix some of these up.
You see they're all left aligned. That means that they're Text. That's really bad.
First, you choose this Time column. Go to Transform.
Change it to a time. Oh, that's bad. I screwed that data up. Where is Ctrl+Z?
If you want to undo, you come over here to the right hand side Applied Steps and uncheck Change Type. Alright, so I choose Time.
and we'll try Time/Date/Timezone. That works beautifully.
Then these four. These are all text.
I am going to click here and shift click there.
Change this to a decimal number.
Alright, Time LatitudeDegrees, LongitudeDegrees. Distance in Meters. That's really bpm.
It is annoying, but i am going to rename these four.
I am going to take the time to rename these four. So right click the heading and Rename.
This is one of those things that I have to rename these once and while it takes me a minute to rename them I'm going to be looking at it every bike ride for the rest of of the summer. So Rename.
We will just call this Longitude. And Latitude. Right-click Rename.
All right.
So these are the fields we have: Time, Latitude, Longitude, Distance in Meters, and BPM.
You say, "wait a second, where is Speed?" i know that my Polar App is telling me what my speed is! They are actually calculating that on the fly.
And this is the one i'm hoping that Bill Szysz or some of my Power Query friends show me how to do this.
I don't know how to do this in Power Query.
So i'm going to do it in Excel. Shame on me.
But you know, I'm just trying to get it done and it's you know this is just for us, right?
It's just for fun. Alright, so on the Home tab. Close and Load.
It goes out and it brings that data from Polar.TCX into Excel.
Now, we're going to add a new column out here.
Called miles per hour (MPH) and what we're seeing here is the data is coming in. Let's see if i can reformat this to show seconds.
the date is coming in and we're getting one data point every second. Beautiful that's how the heart rate tracker must be working.
It gives us the Latitude and Longitude and how far we've traveled since the beginning of the ride.
So, I didn't really trust that from second to second.
this would really be that accurate.
Right here, in those two seconds it said we didn't move at all. How is that possible? How is the bike standing still?
What I've chosen to do is to take the distance that i traveled in the last 10 seconds.
I did some math so the first thing i'm going to do is i'm just going to accept the fact that the first 10 data points here are all going to say zero MPH. So I type 0 and Control+Enter.
Then my first formula is going to be equal the distance at this point.
(But i don't want to use the Table nomenclature.) So I'm actually going to type equal D12 minus d2 is how far did I travel in those 10 seconds?
Times a number of 0.223741454.
That's a crazy number.
I will put a little graphic up there on the screen that shows you how I came up with that number.
So i was going nine miles per hour which sounds about right for the beginning of a ride.
Double-click and copy that down.
You see i'm kind of there at the beginning going eight nine or ten miles an hour. That sounds about right.
i try and shoot for 10 to 12 miles an hour on my bike. The heart rate looks about right. It started out in the 90s.
Hopefully should get up to ...Oh this was this was a slow ride today.
Oh well, all right maybe tomorrow will be better.
So now i have that data and the beautiful thing is I can do anything I want with it. I can sort it. I can filter it.
If you just want to get the data in Excel, here you are.
But i think the awesome thing is to be able to take this and visualize this data by creating a pivot table, in essence, on a map. So 4579 rows loaded.
That means it was 4579 seconds of data.
We are going to analyze that data on a map. Choose any one cell.
On the Insert tab, select 3D Map.
A lot of people have never used this feature.
It's a great way to visualize data provided you have either street address, city, state, zip.
Or latitude and longitude, which is what we have here.
Verify that they chose latitude and longitude as the location type. if you hadn't renamed these.
If this still said DegreesLatitude or LatitudeDegrees, then there is a good chance it's not going to automatically select the field. You would have to choose these fields.
Choose Latitude and Longitude.
For the height on the first one I am going to do miles per hour.
Then, I am going to take the Time field and drag it down to the Time drop zone. Right now, it doesn't look that exciting.
You have to scroll your mouse wheel in.
Scroll in and you will get this view. Navigating in 3D Map is a little weird.
You can use these buttons to tip the data to look at it from above or look at it from the ground. Your call what you want to do.
You can rotate the map left and right.
This down here is called the Scrubber.
You can use that to animate the data over time. So back and forth there.
And then back home.
But what's really weird is somehow here it says that i was going 134 miles an hour.
That clearly is not true. There's no way I was doing that on the bike.
And with all due respect to the Polar company just means that there was a screw up and they happened to give me a couple of data points at the same second.
So over here where Excel has automatically decided to Sum the miles per hour.
We want to create something called an Average.
Now now it kind of peaks out at 18 miles an hour and that's possible if i managed to find a nice little hill.
I could have been going 18 miles an hour.
At least that seems to make sense. Let's customize this a little bit.
First thing i want to do is i want to come up here to Themes and change from the White theme to the Satellite Image.
I can see what's going on.
As we actually zoom in you'll see that these columns are way too big.
So over here on the right hand side (and by the way, if you're back in Excel 2013, you didn't have the layer options.) The Height and Thickness start out at 100 percent.
I want to take those down to about 10% or so.
i guess you can just type in there. That would be even easier.
Ten percent and the thickness is ten percent.
All right so now you can start to see that and this was Speed.
For me, speed means green.
So I choose miles per hour as green like that. Choose whatever green you would like.
All right and now if we watch this data animate over time.
i click the play button. I took off from the house.
Oh this was the day.
This is funny, when my sons were in football they used to do this drill where they would start from the goal line and run out to the 10 yard line. And then back to the goal line and then out to the 20.
Back to the goal line. Out to the 30, Back to the goal line.
Out to the 40. Back to the goal line.
Today, if i think it's going to rain and I don't want to get that far away from the house, I will do that same thing where i always start at the house and then do this loop and then back to the house.
This loop then back to the house. This loop back to the house.
So this was a day where i was doing those kind of short runs and then always going back to the house.
Just to pile on miles without having to get too far away. So so far so good. It's looking great .
Let's add in the heart rate data. So up here we'll click Add Layer.
In the new layer, choose latitude and longitude.
This time it's going to be beats per minute as the height.
And again change that to an average.
Then take the time field and drag it to the Time box like that.
It is funny that they somehow decide to go back out and change the zoom.
We have to zoom in again. Then Layer Options.
Here, we'll change the color for beats per minute to red.
This time for the thickness I am going to go really narrow for the thickness like two percent. And, you see, think about it.
Miles per Hour? I am going 10 to 15 miles per hour.
but heart rate is 90 to 130 so it's just way way too tall.
Here i am going to take that ten percent, and move it back to maybe three percent. Let's see how that looks.
Yes so that way it is just enough that the heart rate sticks out above the miles per hour.
I can still see them both.
And I can see how they are syncing with each other.
Do I need the legend down here to show me that red is heart rate? No.
I can probably remember that.
Do I need this legend to show me that green is miles per hour? No, I can probably remember that.
All right, so check this out.
A new way to analyze your bike ride using the the Polar heart rate monitor.
And the Polar Flow app. And then downloading it to Excel.
And you can animate that data in Excel.
You can go look at any point of the ride and see where your heart rate was.
Are you going up a hill or down a hill?
For those of you new to 3D Map, Click on the map and then scroll away from you to zoom in.
Scroll towards you to zoom out. Using the Alt key is the magic thing here.
Alt and drag up to look from the ground.
Or Alt+ drag down to look from the sky.
and then if you want to go look at it from a different neighborhood, Use Alt left or right.
This will spin the map so you can see the data from there. Isn't this wild?
People use Excel. There are 750 million installs of Excel.
Most people have no clue that this awesome little feature is back there in excel. It was originally called Power Map.
It is now called 3D Map.
Anytime you have geographic data in Excel, you can you can create a map.
So, now, to get back to Excel, use File, Close.
The next time that i open this workbook, there is going to be a little annoying text box here.
It says This worksheet has 3D Maps i just moved that text box out of the way okay.
Let's show you how tomorrow, when we get a new file. And we want to map that file.
I'm going to take this workbook, Save it and Close it.
all right it's overnight. we're going to go back to our downloads folder.
and i'll just take another tcx file here so rename it to Polar.tcx.
i'll take this one right click rename call it Polar.
All right, so now i have a new file called Polar.tcx.
I am going to go back to Excel. Reopen.
it says External Data Connections Have Been Disabled.
You choose enable content and then on the Data tab. Let's see.
We have 4580 rows i'm going to click Refresh All.
Wait until the blue spinner goes away. And now we have 2,568 rows.
The last time there let's see if we can copy MPH down and make that work.
Miles per Hour. Sometimes the miles per hour needs to be extended.
Always go check that field. That was one refresh.
Then back into Insert, 3D Map.
It will still show the last ride. We can do Refresh again.
And so there's a completely different ride from a month ago. June 19th a much shorter ride that day. I am sure.
So there you go.
Using 3D Map and Power Query to extract the Polar heart rate data from your Polar heart rate monitor.
You can show your progress along a cycling route.
I'm sure that this would work for walking for running.
Really, for anything where it's monitoring the latitude and longitude data.
All right before we finish up.
For my Excel friends don't forget Liam Batick's amazing course Excel Virtually Global.
50 hours of Excel content for a ridiculously low price.
All the money going to Doctors without Borders.
If you like these tips please Subscribe, and ring that bell.
Feel free to post any questions or comments down in the comments below.
My new book - MrExcel 2020 - Seeing Excel Clearly.
Click that "i" in the top right hand corner for more information.
i know this was a long video i want to thank all of those folks who are cyclists who are brand new to the podcast.
If you use Excel a lot check us out.
For my regular Excel fans: so i really went a little bit slower than usual today.
Hopefully you picked up a couple of tips or tricks along the way.
Also hey for those of you who are an Ofice Insider and you have a Home subscription.
Go out and check the Data tab and see if you have a whole bunch of new things here.
I have been dying to show you this but all of my computers apparently for another two weeks are stuck It's driving me crazy there's all kinds of great new stuff that I can't show you.
So watch the podcast.
i want to thank you for stopping by we'll see you next time for another netcast from MrExcel.
Analyze Polar Flow Cycling data in Excel. Hey, welcome back to the MrExcel netcast.
I am Bill Jelen.
There are two audiences for this video. The first audience is going to be completely new to me.
This first audience is for cyclists who are using a Polar heart rate monitor and the Polar Flow app. You go out ride your bike.
It monitors all that data. It gives you a cool dashboard.
I can't complain about the dashboard.
But you may want to take a look at your data in Excel.
This video is going to show you how to take that data from Polar Flow, Download it to your computer, and then a fairly deep dive into two rarely used Excel functions.
Even if you use Excel all the time, the odds are that you haven't used Get and Transform Data. It was new in Excel 2016.
And then the 3D Map feature. I am going to show you both of those.
I will go through it very nice and slow. Now, this requires Excel for Windows.
Excel 2016 or newer.
If you're using Excel on an iPhone, Excel on Android, Excel on a Mac, Excel on your Commodore-64, sorry - it's not going to work for you.Now, the other audience, my regular Excel viewers: Hey, how's it going? Great to see you again.
A couple of Excel things that are new here for you.
This is the first time that I've ever loaded XML data into Power Query.
I had to Expand Table repeatedly to find the actual data.
It didn't look like it was going to work, but it worked.
Also new: Using two layers in 3D Map (Power Map).
Depending on which audience you are, You might want to open the Table of Contents down there in the video description to jump to the right spot for you.
By the way, for my Excel friends.
This was going to be part of my Excel Virtually Global 2020.
My one hour session on mapping data in Excel. But it was too long. I ran out of time.
If you happen to be watching this in July of 2020.
July 21st through 23rd - there are over 50 Excel gurus each doing a seminar. The whole course is only US$24 or Australian 33 dollars.
All proceeds to charity.
Check out the link down there in the YouTube description. There are going to be some some great things.
I can't wait to learn from all these other Excel gurus around the world.
All right so now for those Polar Flow cyclists who use the heart rate monitor. Let me show you what my dashboard is going to give you.
I don't think that you're actually going to be able to get in the dashboard in Polar or even if you take your data over to Strava.
So, we're going to switch over here to another workbook that I've already built and that I've been using for a couple of weeks with my rides.
So here's the workbook that I built a few weeks ago. I said, "oh this is cool. I should do a video about it".
It has the time, latitude, longitude distance in meters, BPM (that is heart rate).
And then miles per hour.
Yes, here I am in the United States - we are one of the last countries that are still using imperial units.
If you are anywhere else in the world, you're going to convert this to metric.
What's really amazing, other than just being able to get this data in Excel and look for various trends, is this feature called 3D Maps.
It was originally introduced as an Add-In after Excel 2013 came out.
It was an add-in. Then part of Excel 2016.
It lets you take that data in Excel, and visualize it on a map.
Or, in this case an actual satellite image of my neighborhood. So here's a ride that I did when i i first built the workbook.
There is a time slider down here.
I can actually animate the whole ride.
Here is me taking off from the house.
You can watch this whole thing unfold. Very Fast. Yes, Strava has a replay feature.
But what is different, with mine is that i can go to any particular scene and I can zoom in.
I am going to hold down the alt key. Drag the mouse up.
What i have here is the green is miles per hour.
So, right there i was going 12 miles per hour. Red is heart rate. So 107 at that point.
So I can see as i'm increasing or decreasing my speed does my heart rate go up or down.
This video - admittedly a long video - I hope that the cyclists stick with me i'm going to try and go really slow and show you how to build this the awesome tool.
The important thing about using Power Query and 3D maps: Let's say tomorrow you go on a different ride.
You download a new ride from Polar Flow.
We're going to put it in the same folder with the same name. I call mine Polar.TCX.
Once you've taken the time to follow this video.
And build this then we can go to the Data tab and click Refresh All.
And the 10 or 15 minutes you spend to analyze this data the first day will automatically update and you see the new data. This for me is a longer ride.
Let's see if I need to fill in some Miles per Hour.
That's one thing i couldn't do in Power Query.
Some of my regular Excel viewers are going to say, "Oh you can do that in Power Query". I'd love to hear about it in the comments below.
It was something that i could not get to work.
So there's my data in Excel and then we go into 3D Map.
Insert, 3D Map. Open that and then a second Refresh.
We have to refresh twice.
Alright and so now I have a completely different ride. This is actually a ride from a couple of weeks later.
I'll rotate this around. We'll go back to the very beginning.
So this was one of my longest rides where I kind of rode around Merritt Island here.
For me, 13 miles which I realize for most of you cyclists is nothing.
But trust me, when I moved here two months ago, I could barely ride a mile and a half, so for me that was a huge victory to be able to do this ride.
We are living here in Florida.
The funny thing about Florida is when I see my friends on Strava, and your elevation rise where you gain a thousand meters in elevation. Yeah, we can't do that.
My house: the elevation of my house is 8.
Literally eight feet above sea level.
I remember on this particular ride one thing that was funny to me is i was had to go up over the Sea Ray Drive bridge.
and that that was going to be challenging for a couple of reasons.
First off, Sea Ray Drive has been closed since Hurricane Matthew, so no car traffic is allowed to go there.
First I asked one of my cycling buddies - "hey can you get through?" He said, "Oh yes.
There are some barriers, but they moved one of the barriers away so the cycle can get through.
A lot of cyclers ride that area," I remember that just going up over Sea Ray Drive, Here - Up over that bridge.
I don't know - it's probably an elevation gain of maybe 20 feet or something like that.
i remember that that for me it felt kind of brutal.
So let's take a look at this data.
Just as i get up to the bridge. So that little stretch, there.
Where i go from completely flat up to the top of the bridge.
I will just grab this slider here. Right at the approach.
I am going 13.67 miles per hour and by the time i get about halfway up the bridge, right there.
That 13 MPH, or 15 MPH - I was actually going 15 MPH and then it falls off to 9.2.
For me, that little hill was the first time really I had encountered a hill.
All of Florida, at least here in Merritt Island, It is flat. I might go up a curb or a speed bump and that it the most elevation that I ride.
Check out my heart rate. Up in a zone that i generally don't get to.
It was 126 to 127.
Usually I am down in the 115 to 120 range. So this was good.
I was able to see the speed go down and the heart rate increase.
Something that i remember from the road, I'm able to come back and actually analyze that data in Excel.
So having both the speed and the heart rate here together point by point is interesting.
Then once I hit the peak of the bridge, all of a sudden then I am going downhill for that little bit of the bridge.
And back up to 11 miles an hour.
You know you can go back and see.
Here I hit some sort of a max of 17 miles an hour on that nice straight-away after the bridge.
So, that's that's the goal here.
For the cyclists who are not regulars on the MrExcel podcast. That's our goal.
That's what we're trying to build. Here now i wrote out all the steps.
31 31 steps and trust me, if you go through this video, use the pause button.
You will be able to follow these steps and build this very cool dashboard yourself.
So the first thing we're going to do is we're going to use the Polar Flow Polar.com website. Go to your dashboard.
Find some ride that looks interesting and choose that ride.
All the way down here at the bottom is something called Export Session. They offer you three different file types: TCX, CSV.
As Excel people - CSV that's the way to go?
No, it actually turns out that the TCX has more information that we need.
It comes down here as a TCX file with my name and the date.
Because we want to be able to build this once and reuse it again and again, you are going to go and rename that file.What I've been calling it is Polar.TCX.
This is important. You'll see in Power Query .We are going to teach Power Query that we are looking for a file called polar.tcx.
Take this old file and rename it it PolarBigLoop.
Then the file that i just downloaded.
I will right-click and rename that to just be called Polar. So Polar.TCX.
if we take a look at this file.
I am able to see that it had the latitude, time, the heart rate bpm.
Sensor state is Present which is not something I need.
I just kind of looked through the XML here and i was able to find that it's all tucked inside of...
Right before the Time is something called Trackpoint.
Which seems to be the element.
So, we are looking for an element called Trackpoint in the data.
And the trackpoint is contained (where's the very first TrackPoint?) It is contained in something called Track.
That's in something called Lap.
So we are going to drill down through Lap, then Track and then Trackpoint in order to build this.
So we have a file called Polar.tcx. I am going to put it in my Downloads folder.
for you of course it's gonna be a different path. It's not going to be Bill J.
It's going to be something else.
We come to a blank worksheet in Excel like this. Under Data, Get and Transform Data.
In Office 365 is the leftmost group.
Back in Excel 2016 it was the third group. It was over here. So look for Get and Transformed Data.
I am going to say Get Data, From File.
Not from a workbook but from XML.
They start out here in this folder.
I am going to paste the downloads folder.
They are looking for XML files but what Polar is giving us is a dot TCX file.
So Iam going to go to change that to All Files. There is my polar.tcx.
Click import. Again you want to be careful.
If you're going to take the time to do the next 20 steps make sure that you have renamed this to be Polar.TCX.
That way, tomorrow, you're going to be able to redo all of this in two clicks.
Instead of having to go through 20 steps. Click Import.
They are establishing a connection to Polar.TCX which means that they're analyzing it.
They have here something called Author and Activities. Activities is expandable.
So expand Activities and then something called activity.
Now, I have got to tell you this does not look really encouraging.
There is only one row that they can see. But, there is something called Lap.
We are going to be able to expand that Lap.
So we choose Activity. Do NOT choose Load. Instead choose Transform Data.
Hey, for the cyclists: You use Excel at work all the time, right? I get it.
You've probably never seen this screen. This is called Power Query.
It's the most amazing thing for taking ugly data and cleaning it.
You clean it once and then it'll remember those steps and help you clean it again.
Next week. Next Month. Tomorrow. Next Hour.
Okay, so the segment of the XML that we are looking for is inside this field here called Lap.
So when I look at Lap, it has all kinds of great information.
How many calories, average heart rate, and so on.
But really, the only thing that I want from here is the thing called Track.
In other words, the tracker. The track point.
So i choose that and click ok. Now I have Lap.Track.
When i expand that, it has something called Trackpoint. That is great. Click OK.
All right. And then, finally, here, once we get to TrackPoint.
That's where I, get Time, Position, Distance in meters, heart rate BPM.
Sensor State is just saying whether the sensor was sending data. I don't need that.
I'm going to uncheck that part.
I don't need this to say Lap.Ttrack.Trackpoint.Time. I just want it to say time.
So in this case, since this is the actual important one, I am going to uncheck the box for "Use Original Column Name As Prefix".
So now I get ID, Time, Position is still a table.
We are going to expand that in a minute. Distance in meters, Heart Rate BPM.
Training, Creator, and the sport in this case was Biking.
I don't need these columns out here.
So i'll right click and say remove those columns.
For heart rate bpm when i expand that, there is only one option. It's just called Value.
Distance in Meters we will keep. Then Position.
This is what's going to allow us to map the data because it has Latitude and Longitude. All right.
So right now it all looks pretty good. We don't need this column on the right.
So right click and Remove. See.
Power Query is just a little bit different than Excel. In Excel, you would right-click and Delete.
But this was written by the SQL Server Analysis Services team.
All right, now. We need to fix some of these up.
You see they're all left aligned. That means that they're Text. That's really bad.
First, you choose this Time column. Go to Transform.
Change it to a time. Oh, that's bad. I screwed that data up. Where is Ctrl+Z?
If you want to undo, you come over here to the right hand side Applied Steps and uncheck Change Type. Alright, so I choose Time.
and we'll try Time/Date/Timezone. That works beautifully.
Then these four. These are all text.
I am going to click here and shift click there.
Change this to a decimal number.
Alright, Time LatitudeDegrees, LongitudeDegrees. Distance in Meters. That's really bpm.
It is annoying, but i am going to rename these four.
I am going to take the time to rename these four. So right click the heading and Rename.
This is one of those things that I have to rename these once and while it takes me a minute to rename them I'm going to be looking at it every bike ride for the rest of of the summer. So Rename.
We will just call this Longitude. And Latitude. Right-click Rename.
All right.
So these are the fields we have: Time, Latitude, Longitude, Distance in Meters, and BPM.
You say, "wait a second, where is Speed?" i know that my Polar App is telling me what my speed is! They are actually calculating that on the fly.
And this is the one i'm hoping that Bill Szysz or some of my Power Query friends show me how to do this.
I don't know how to do this in Power Query.
So i'm going to do it in Excel. Shame on me.
But you know, I'm just trying to get it done and it's you know this is just for us, right?
It's just for fun. Alright, so on the Home tab. Close and Load.
It goes out and it brings that data from Polar.TCX into Excel.
Now, we're going to add a new column out here.
Called miles per hour (MPH) and what we're seeing here is the data is coming in. Let's see if i can reformat this to show seconds.
the date is coming in and we're getting one data point every second. Beautiful that's how the heart rate tracker must be working.
It gives us the Latitude and Longitude and how far we've traveled since the beginning of the ride.
So, I didn't really trust that from second to second.
this would really be that accurate.
Right here, in those two seconds it said we didn't move at all. How is that possible? How is the bike standing still?
What I've chosen to do is to take the distance that i traveled in the last 10 seconds.
I did some math so the first thing i'm going to do is i'm just going to accept the fact that the first 10 data points here are all going to say zero MPH. So I type 0 and Control+Enter.
Then my first formula is going to be equal the distance at this point.
(But i don't want to use the Table nomenclature.) So I'm actually going to type equal D12 minus d2 is how far did I travel in those 10 seconds?
Times a number of 0.223741454.
That's a crazy number.
I will put a little graphic up there on the screen that shows you how I came up with that number.
So i was going nine miles per hour which sounds about right for the beginning of a ride.
Double-click and copy that down.
You see i'm kind of there at the beginning going eight nine or ten miles an hour. That sounds about right.
i try and shoot for 10 to 12 miles an hour on my bike. The heart rate looks about right. It started out in the 90s.
Hopefully should get up to ...Oh this was this was a slow ride today.
Oh well, all right maybe tomorrow will be better.
So now i have that data and the beautiful thing is I can do anything I want with it. I can sort it. I can filter it.
If you just want to get the data in Excel, here you are.
But i think the awesome thing is to be able to take this and visualize this data by creating a pivot table, in essence, on a map. So 4579 rows loaded.
That means it was 4579 seconds of data.
We are going to analyze that data on a map. Choose any one cell.
On the Insert tab, select 3D Map.
A lot of people have never used this feature.
It's a great way to visualize data provided you have either street address, city, state, zip.
Or latitude and longitude, which is what we have here.
Verify that they chose latitude and longitude as the location type. if you hadn't renamed these.
If this still said DegreesLatitude or LatitudeDegrees, then there is a good chance it's not going to automatically select the field. You would have to choose these fields.
Choose Latitude and Longitude.
For the height on the first one I am going to do miles per hour.
Then, I am going to take the Time field and drag it down to the Time drop zone. Right now, it doesn't look that exciting.
You have to scroll your mouse wheel in.
Scroll in and you will get this view. Navigating in 3D Map is a little weird.
You can use these buttons to tip the data to look at it from above or look at it from the ground. Your call what you want to do.
You can rotate the map left and right.
This down here is called the Scrubber.
You can use that to animate the data over time. So back and forth there.
And then back home.
But what's really weird is somehow here it says that i was going 134 miles an hour.
That clearly is not true. There's no way I was doing that on the bike.
And with all due respect to the Polar company just means that there was a screw up and they happened to give me a couple of data points at the same second.
So over here where Excel has automatically decided to Sum the miles per hour.
We want to create something called an Average.
Now now it kind of peaks out at 18 miles an hour and that's possible if i managed to find a nice little hill.
I could have been going 18 miles an hour.
At least that seems to make sense. Let's customize this a little bit.
First thing i want to do is i want to come up here to Themes and change from the White theme to the Satellite Image.
I can see what's going on.
As we actually zoom in you'll see that these columns are way too big.
So over here on the right hand side (and by the way, if you're back in Excel 2013, you didn't have the layer options.) The Height and Thickness start out at 100 percent.
I want to take those down to about 10% or so.
i guess you can just type in there. That would be even easier.
Ten percent and the thickness is ten percent.
All right so now you can start to see that and this was Speed.
For me, speed means green.
So I choose miles per hour as green like that. Choose whatever green you would like.
All right and now if we watch this data animate over time.
i click the play button. I took off from the house.
Oh this was the day.
This is funny, when my sons were in football they used to do this drill where they would start from the goal line and run out to the 10 yard line. And then back to the goal line and then out to the 20.
Back to the goal line. Out to the 30, Back to the goal line.
Out to the 40. Back to the goal line.
Today, if i think it's going to rain and I don't want to get that far away from the house, I will do that same thing where i always start at the house and then do this loop and then back to the house.
This loop then back to the house. This loop back to the house.
So this was a day where i was doing those kind of short runs and then always going back to the house.
Just to pile on miles without having to get too far away. So so far so good. It's looking great .
Let's add in the heart rate data. So up here we'll click Add Layer.
In the new layer, choose latitude and longitude.
This time it's going to be beats per minute as the height.
And again change that to an average.
Then take the time field and drag it to the Time box like that.
It is funny that they somehow decide to go back out and change the zoom.
We have to zoom in again. Then Layer Options.
Here, we'll change the color for beats per minute to red.
This time for the thickness I am going to go really narrow for the thickness like two percent. And, you see, think about it.
Miles per Hour? I am going 10 to 15 miles per hour.
but heart rate is 90 to 130 so it's just way way too tall.
Here i am going to take that ten percent, and move it back to maybe three percent. Let's see how that looks.
Yes so that way it is just enough that the heart rate sticks out above the miles per hour.
I can still see them both.
And I can see how they are syncing with each other.
Do I need the legend down here to show me that red is heart rate? No.
I can probably remember that.
Do I need this legend to show me that green is miles per hour? No, I can probably remember that.
All right, so check this out.
A new way to analyze your bike ride using the the Polar heart rate monitor.
And the Polar Flow app. And then downloading it to Excel.
And you can animate that data in Excel.
You can go look at any point of the ride and see where your heart rate was.
Are you going up a hill or down a hill?
For those of you new to 3D Map, Click on the map and then scroll away from you to zoom in.
Scroll towards you to zoom out. Using the Alt key is the magic thing here.
Alt and drag up to look from the ground.
Or Alt+ drag down to look from the sky.
and then if you want to go look at it from a different neighborhood, Use Alt left or right.
This will spin the map so you can see the data from there. Isn't this wild?
People use Excel. There are 750 million installs of Excel.
Most people have no clue that this awesome little feature is back there in excel. It was originally called Power Map.
It is now called 3D Map.
Anytime you have geographic data in Excel, you can you can create a map.
So, now, to get back to Excel, use File, Close.
The next time that i open this workbook, there is going to be a little annoying text box here.
It says This worksheet has 3D Maps i just moved that text box out of the way okay.
Let's show you how tomorrow, when we get a new file. And we want to map that file.
I'm going to take this workbook, Save it and Close it.
all right it's overnight. we're going to go back to our downloads folder.
and i'll just take another tcx file here so rename it to Polar.tcx.
i'll take this one right click rename call it Polar.
All right, so now i have a new file called Polar.tcx.
I am going to go back to Excel. Reopen.
it says External Data Connections Have Been Disabled.
You choose enable content and then on the Data tab. Let's see.
We have 4580 rows i'm going to click Refresh All.
Wait until the blue spinner goes away. And now we have 2,568 rows.
The last time there let's see if we can copy MPH down and make that work.
Miles per Hour. Sometimes the miles per hour needs to be extended.
Always go check that field. That was one refresh.
Then back into Insert, 3D Map.
It will still show the last ride. We can do Refresh again.
And so there's a completely different ride from a month ago. June 19th a much shorter ride that day. I am sure.
So there you go.
Using 3D Map and Power Query to extract the Polar heart rate data from your Polar heart rate monitor.
You can show your progress along a cycling route.
I'm sure that this would work for walking for running.
Really, for anything where it's monitoring the latitude and longitude data.
All right before we finish up.
For my Excel friends don't forget Liam Batick's amazing course Excel Virtually Global.
50 hours of Excel content for a ridiculously low price.
All the money going to Doctors without Borders.
If you like these tips please Subscribe, and ring that bell.
Feel free to post any questions or comments down in the comments below.
My new book - MrExcel 2020 - Seeing Excel Clearly.
Click that "i" in the top right hand corner for more information.
i know this was a long video i want to thank all of those folks who are cyclists who are brand new to the podcast.
If you use Excel a lot check us out.
For my regular Excel fans: so i really went a little bit slower than usual today.
Hopefully you picked up a couple of tips or tricks along the way.
Also hey for those of you who are an Ofice Insider and you have a Home subscription.
Go out and check the Data tab and see if you have a whole bunch of new things here.
I have been dying to show you this but all of my computers apparently for another two weeks are stuck It's driving me crazy there's all kinds of great new stuff that I can't show you.
So watch the podcast.
i want to thank you for stopping by we'll see you next time for another netcast from MrExcel.
