Microsoft Excel — The Possibilities With Power Query (#Investing)
The More I Learn, The More It Impresses
A few years ago, my professional life required staying on top of various industries, public company filings, monitoring peer companies, etc. The most important factor for me was building a process for quick, easy, and timely access to information.
A recent project took me back down this road. I’m not surprised, but just a few short years later, this process has become insanely simple.
How?
Using Excel, Power Query, Google Apps and the Internet, the data retrieval can be fully automated and setup in a dynamic / flexible dashboard.
In this post I will highlight examples of the dashboard. In future posts, I will delve into HOW this is done.
Here are a few examples:
Excel Dashboard Navigation
Examples — Variations of Stock Dashboard
Several examples are shown below which highlight the flexibility to modify the dashboard for different scenarios:
- Selection of individual stocks (say, a “portfolio” you want to track).
- Various indices (Dow 30, Nasdaq 100 and S&P 500 are highlighted).
- A peer group selection / one industry to follow.
Each dashboard has similar components:
- Hyperlinked to selected company detail.
- Graph of Sector participation.
- Conditional formatting to provide quick visuals on selected activity.
33 Selected Companies
- Custom list of companies I want to follow.
- Download PDF
Stock Indices Tracking
- Same idea, but now for the Dow 30, NASDAQ 100, and the S&P 500.
Easy modification to customize report:
- For NASDAQ, the overall chart on Page 1 shifts to Industry performance (individual stocks gets messy due to number).
- For S&P 500, the overall chart on Page 1 shifts to Sector performance.
Dow 30 Index
Nasdaq 100 Index
S&P 500 Index
Peer Group of Stocks
- In this example, I selected a group of companies from the Consumer Non-Durables Sector / Packaged Foods Industry.
- JM Smucker, one of my investments, is a member of this industry. This gives me a quick sense of how the Company is doing relative to their peers.
- Every investor relations function within a public company does this process in some fashion.
- Download PDF
Conclusion
Incredible flexibility in reporting, information access and insight can be quickly gained by leveraging Excel and the Power Query add-in.
Once you start “connecting the dots”, the opportunities start to present themselves on a number of fronts.
I hope this example helps you get your gears turning to explore your own possibilities!
Stay tuned for future posts on how this dashboard was developed!
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!
