Microsoft Excel — #BitCoin and Power Query
A Simple Example that Highlights A Basic Power Query!
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!
Prepare the Excel Worksheet for easy use
- 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).
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
“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!
