Microsoft Excel — #BitCoin and Power Query

A Simple Example that Highlights A Basic Power Query!

Image for post
Image for post
BitCoin Price History from Wikipedia Downloaded to Excel

Why Did I Do This?

BitCoin is a HOT topic these days.

I was having a conversation with an executive this morning about BitCoin activity over the last few years. We were curious about what has actually transpired.

So I put this together using Excel and Power Query.

It took me 20 minutes from start to finish. And every time I open the Excel file, a simple query “refresh” will update my report.

It will take longer to write this post…

Here’s How I Did It

First, do you want a PDF of the report? You can download it here.

Here are the steps to make this happen:

Source the data

  • Search for “Bitcoin price history” and found this Wikipedia page. It has the table with the data I want.
  • Open Excel, access the Power Query add-in, and select the web as my data source. Paste the URL.
  • Open query in Edit Mode.

Editing the DataSet

The Query, in edit mode, is shown below. The key steps were:

  • Duplicate the “DatePeriod” column so I can extract the Year value.
  • Split the newly created “DatePeriod” column and create, from that, a “Year” column.
  • Add an “Index” column, sort it in DESCENDING order, and then remove the column (I do this because I want the most recent records on top — and no other fields would sort correctly).
  • Load the query as a Table in Excel. Done with Power Query!
Image for post
Image for post
BitCoin Power Query in Edit Mode

Prepare the Excel Worksheet for easy use

Image for post
Image for post
Modified Excel Worksheet — Ready for Distribution
  • Include a Hyperlink back to the table source data
  • Macros added and assigned to buttons which 1) Refresh the Data Table and 2) automates printing and naming worksheet to a PDF formatted report (below).
Image for post
Image for post
PDF Report from Excel worksheet

If you would like the Excel workbook with the Power Query and the macros used to develop this, you can request it here.

Thanks for reading! I hope this demonstrates how a basic Power Query can be very helpful, with very little effort!

About Don

Image for post
Image for post

“It’s time for different”

Don is passionate about helping professionals and organizations keep up and adapt to the changing business world that we operate in.

“What Do You Do?”

I frequently get this question. My response (it’s not what you think!)? Check it out here!

Connect with Don!

LinkedIn, Flipboard, Twitter, Snapchat

Or, just Google me…I’m everywhere

It’s time for DIFFERENT— On a mission to challenge the status quo to a more productive and effective end… #digital #Excel #data #analytics

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store