Ok people. Let me tell you that this post is almost not about Excel. It is about how one Excel blogger’s (yours truly) dream of long distance cycling came true. So sit back, grab your favorite drink and read between sips.
PS: if you just want to see the dashboard, click here.
So what is this all about?
Last Sunday (27th July) & Monday (28th), I finished my first ever 200KM bicycle ride. I rode for a little more than 12 hours, burned 5,179 calories & rode 206 kilometers.
It is definitely one of the most memorable, tiresome & uplifting experiences in my life. So naturally, I want to share the story with you.
Where it all began
Around late last year, I read a book called Free Country by George Mahood. It is a story of two young British lads cycling from Lands End to John O’ Groats (from one end of Britain to another) without spending a penny of their own money. While reading the book, I kept thinking, ‘I should do something mildly similar one day’.
But I did not do much cycling in the next several months. Until…,
I read truly inspiring story of how adc cycled from Manali to Leh (500 km). The story is special because the terrain between Manali & Leh is filled with high altitude passes, dangerous mountain roads, harsh weather, almost no habitat & breath taking scenery.
That travelogue motivated me enough to get out my dusty cycle from garage and start riding it.
Soon I was riding 20 KM per day. I started doing a few long rides (~40km) in the weekends to build strength.
Once I felt confident, I discussed my plans for a longer ride with Jo (my wife). She was a little apprehensive as I have never done something like that and long rides could mean increased chance for injury or accident.
Finally we agreed that doing a round trip to Annavaram (a small temple town 118 km away from our home) would be reasonable. The roads to Annavaram are in excellent condition. There is ample help available all the way (plenty of food stops, villages & towns along the way). Plus in case of fatigue or injury, I could easily hitch a ride back home.
I fixed on the dates 27 & 28th of July for ride. Weather app on Jo’s iPad said there is 70% chance of rain. I kept my plans fluid & wanted to ride only if there is no rain in the morning when I started.
Route Map
Here is the route I took (link).
Day 1 – Vizag to Annavaram (118 km, 7:34 hrs riding)
I woke up at 4:45 AM and unlocked the doors immediately to see if there is any rain. No rain and the skies looked ok. I got ready, had a glass of milk & woke up Jo. She gave me a farewell hug and I left the house at 5:24.
The initial 20 km was a breeze to ride. The route so far is familiar as I bicycle on it almost everyday.
After riding 25 km, I took a short water break.
I barely rode for another 4 km and stopped again, this time to enjoy the view from top of a flyover. The rolling hills, green rice fields, railway track, chirping birds & fresh morning air provided perfect setting for a few pictures.
Soon I left the state highway & joined national highway 45 (and 16 too). This is a 4 lane highway with wide shoulder. Unlike in US (and many other western countries), in India bicycles, pedestrians and many other forms of moving objects are allowed on highways. The best part of riding on national highway is that the gradients are better. Next 15 km felt like one long flat ride.
I crossed Anakapalli (a small town on the outskirts of Vizag) and stopped for breakfast at 41 KM mark. 1/3rd distance completed!
After feasting on Idly, Dosa & a tea (traditional breakfast in south India), I resumed the journey at 8:10 AM.
Still no rains. So I rode on.
At around 50 KM mark, stopped for a quick drink of water & electral (oral re-hydration solution made with essential salts & minerals).
Around 60 km mark I started feeling a little thirsty and wanted to stop for some tea. Soon (after 6km) found a road side tea stall. My original plan is to ride until 80k mark and stop for lunch.
But seeing that I finished 66km by 9:56AM, I have decided to ride until Annavaram (my destination for the day) before lunch.
After the tea stop, I rode for another hour to reach Nakkapalli (a small village on the highway). There I saw a fruit juice stall. Immediately stopped to have a full glass of banana juice. The shop owner told me that Annavaram is another 36 km from there. It started drizzling now. I am a little worried that it may rain heavily.
But my fears were unfounded. Soon the skies cleared and I was on a climb. After riding just another 10 km, I had to stop again to quench my thirst and relax a bit. As I was sitting by the road side, a mini truck stopped next to me. the truck driver was curious to know where I am heading & my story. After chatting for a while they left me.
At 95KM mark, I stopped again for a quick snack (snickers bar). Soon 2 kids on a cycle approached me. They are Siva & Siva Ganesh. Siva Ganesh (eldest one, studying 9th class) wanted to test ride my cycle. He was curious to know how the gears work. He took the cycle for a short spin and returned. They spend another 5 minutes with me asking several questions.
Once again I found myself on a steady climb. And then there are strong head winds. My speeds dropped to the range of 10-15kmph. After a lot of pedaling & sweat, I crossed Tuni at 12:30PM.
Selfie @ 100km mark
It took me another 30 minutes & 6 km of biking to reach a small temple village called Lova Gudi. By now my water bottles are emptied. So I stopped here to purchase a bottle of water. From here Annavaram (my destination) is 15 km. Normally I would have finished this distance in 40-50 mins. But after riding for 6 hrs, I find myself tired & sweaty. Plus the winds have picked up again. And to top it, almost all of this stretch is a climb.
I took a deep breath and resumed cycling.
The wind & gradient made my task all the more difficult. But I pressed on.
See how harsh the wind is. The coconut leaves are all bent.
About 5 km away from Annavaram (around 110 km mark), someone in a car was waiting for me. As soon as he saw me, he waved his hands and stopped me. Turns out he is a biker from Vizag. He got curious upon seeing the site of a lone biker and wanted to know about me. We chatted for a while and exchanged phone numbers.
Finally at 2:50 PM, I reached Annavaram village gate. Yay!. It took me more than 9 hours & 30 minutes to reach here. Out of this riding time is 7:30 (it might have been slightly less than this as my tracking app – MapMyRide was not set to autopause for the first 65 km).
Finally, at Annavaram village gate after 9:30 hours of riding.
Day 1 – At Annavaram
I went to the first hotel I can find and ordered lunch. While waiting for lunch, I called Jo & my mom to tell that I reached safely.
After lunch I asked them if they have any rooms to stay. They have vacancies and I immediately took a room. I quickly showered and took a nap. But I could hardly sleep as my thighs and wrists were aching. It looked like I could not ride back and may have to take a bus to my home.
Originally I planned to visit the famous Annavaram temple in the evening. But my legs & back felt too sore to do anything. So instead I went for a short walk to explore the village in the evening.
Later I ordered dinner and watched some football on TV.
Around this time, I have decided to skip the return ride & take a bus instead. I told the same to Jo when I called her to say good night.
I kept waking up in the night due to one pain or other. I turned off the alarm at 6:15 AM and went back to sleep again.
Finally when I woke up at 8:00 I surprisingly felt rejuvenated and ready.
At that moment I decided to ride back home. My plan is to ride as far as I can and then take a bus.
Day 2 – Annavaram to Anakapalli (88 km, 4:30 hrs riding)
After a heavy breakfast & some coffee, I checked out of the hotel and started my return journey at 8:43 AM.
Since I have climbed & gained elevation yesterday, the first 40 km ride from Annavaram was almost downhill mixed with few flats. So I could maintain speeds between 20-30 kmph. At one point I even reached 43.5 kmph, although barely for a minute.
I did not stop anywhere for first 25 km. But then the views by the road are too breathtaking to ignore. So I stopped for a few pictures at 25k mark & again at 27k mark.
At the later stop, I found several open billed storks in the rice fields. It was a pleasure watching them forage for food & fly elegantly.
Pretty soon I reached the toll plaza. And yay!, no toll for me. Another reason to bike.
Had to stop again at 10:55AM to appreciate the beautiful green & blue views. Took a few pictures and resumed ride. Reached 50 KM by now.
At this point, I changed my plan to ride for 83 km. This will make the total distance as 200km.
By 11:30, I reached Elamanchali (a small village 20 km away from Anakapalli, my destination for the day). By now it started raining quite a bit. My bike accumulated quite a bit of dirt & soot.
By 1:00 PM, I reached outskirts of Anakapalli and spotted a nice road side restaurant. Immediate stopped there for lunch. As soon as I stopped it started raining heavily. So I had a leisurely lunch spending close to an hour there. Finally the skies cleared at 2 PM and I resumed my ride.
When I was 3 km outside Anakapalli, a gentleman on motorcycle slowed down and rode with me for a few minutes. He asked me several questions about where I am riding, what I do, why I am riding etc. He even invited me to have lunch with him. But I had to decline as I already finished my lunch.
Finally, at 3:06 PM, I finished 88km and stopped my bicycle ride.
An empty mini truck going towards Sabbavaram (half way between where I stopped & my home) offered me a ride.
From Sabbavaram I took an Auto (a 3 wheeled taxi, also called as tuk-tuk) to my home.
And that marked the end of my first ever 200 km bike ride.
My impressions:
Growing up, I used to be the least fit kid in my school. I sucked at all sorts of sports. I got kicked out of football, hockey, badminton, cricket and all other sporting teams in school.
So naturally I assumed that I am not good for athletic activities.
Once I completed my studies and got a job, I realized the importance of living an active life. So I started jogging, cycling and playing sports.
Ever since I quit my job to start my own business, I became even more fitness conscious. Every year (since 2010), I have challenged myself to break personal barriers. When I first considered biking, I could not imaging anything beyond 50km per day. But with practice and motivation, I could finish this ride.
Few thoughts:
- Do a few practice rides: I have been riding 20-30km per day for almost a month before taking up this challenge.
- We underestimate our body: We can take on big challenges, endure pain & perform at our peak so much more than we estimate.
- Drink & Eat well: I call my diet really healthy. We eat home cooked food 90% of the time. Most of our diet is veggies, fruits, milk, nuts, rice & wheat. Almost half of the vegetables & fruits we eat are grown in our back yard. We rarely drink and eat meat once a week.
- Enjoy the ride: Stop and enjoy the scenery. Talk to fellow travelers and take ample breaks. Any distance can be covered easily.
Thanks to
- Jo: for supporting & encouraging me.
- My mom: for not freaking out when she learned about the ride.
- Free Country & ADC: for the motivation
- You: for supporting Chandoo.org so that I can chase my passions.
Now for an Excel Dashboard visualizing the ride
Soon after I got back, I started thinking about analyzing all the ride data. I did what I do best. I created an Excel dashboard to tell the 200km ride story.
Here is a snap shot of the dashboard (click to expand).
Click here to download the Excel Workbook.
It is unlocked. So go ahead and examine the file. Break it apart to understand the logic & formulas. Learn something new & be awesome.
How is this dashboard made?
Here I am going provide only the highlights. Please visit Excel Dashboards page for fantastic tutorials & explanations on how to make dashboards.
- Tables: for structuring ride data. All the ride data came from MapMyRide. I used Doogal.co.uk to get elevation profile of the route.
- Column & line charts: for the splits, speed & elevation.
- Form controls: to make the charts dynamic & to control the Ride pics slideshow.
- Picture links: to show one of the many ride pictures thru scroll bar form control.
- Ample dose of formulas: Mainly INDEX, MATCH, SUMIFS & COUNTIFS.
More personal stories
If you are in the mood to know Chandoo.org & me a little more, check out these:
- Story of Chandoo.org startup
- Thank you, we have a home
- Thank you, we have a car (and another one too)
- Travelogues from my visits to Maldives & Australia
- Confessions of a dad
See you again with more awesome Excel stuff.
One Response to “SQL vs. Power Query – The Ultimate Comparison”
Enjoyed your SQL / Power Query podcast (A LOT). I've used SQL a little longer than Chandoo. Power Query not so much.
Today I still use SQL & VBA for my "go to" applications. While I don't pull billions of rows, I do pull millions. I agree with Chandoo about Power Query (PQ) lack of performance. I've tried to benchmark PQ to SQL and I find that a well written SQL will work much faster. Like mentioned in the podcast, my similar conclusion is that SQL is doing the filtering on the server while PQ is pulling data into the local computer and then filtering the data. I've heard about PQ query folding but I still prefer SQL.
My typical excel application will use SQL to pull data from an Enterprise DB. I load data into Structured Tables and/or Excel Power Pivot (especially if there's lot of data).
I like to have a Control Worksheet to enter parameters, display error messages and have user buttons to execute VBA. I use VBA to build/edit parameters used in the SQL. Sometimes I use parameter-based SQL. Sometimes I create a custom SQL String in a hidden worksheet that I then pull into VBA code (these may build a string of comma separated values that's used with a SQL include). Another SQL trick I like to do is tag my data with a YY-MM, YY-QTR, or YY-Week field constructed form a Transaction Date.
In an application, I like to create a dashboard(s) that may contain hyperlinks that allow the end-user to drill into data. Sometimes the hyperlink will point to worksheet and sometimes to a supporting workbook. In some cases, I use a double click VBA Macro that will pull additional data and direct the user to a supplemental worksheet or pivot table.
In recent years I like Dynamic Formulas & Lambda Functions. I find this preferable to pivot tales and slicers. I like to use a Lambda in conjunction with a cube formula to pull data from a power pivot data model. I.E. a Lambda using a cube formula to aggregate Accounting Data by a general ledger account and financial period. Rather than present info in a power pivot table, you can use this combination to easily build financial reports in a format that's familiar to Accounting Professionals.
One thing that PQ does very well is consolidating data from separate files. In the old days this was always a pain.
I've found that using SQL can be very trying (even for someone with experience). It's largely an iterative process. Start simple then use Xlookup (old days Match/Index). Once you get the relationships correct you can then use SQL joins to construct a well behaved SQL statement.
Most professional enterprise systems offer a schema that's very valuable for constructing SQL statements. For any given enterprise system there's often a community of users that will share SQL. I.E. MS Great Plains was a great source (but I haven't used them in years).
Hope this long reply has value - keep up the good work.