Google Apps: Creating A Dynamic Stock Research Dashboard Using Google Docs
Overview
Over the last couple years, I have noticed more of my spreadsheet work shifting to Google Docs and away from Excel. Of course, not the heavy duty use, but for much of the simpler analysis, I tend to think Google first. Why? Simply because it has the ability to be accessed from anywhere, on any device. Additionally, some of the capabilities for accessing information elsewhere on the Internet and integrating it into my spreadsheets is far easier than the effort I go through with Excel.
I recently completed a project that opened my eyes to just how powerful Google Docs can be. The purpose of this overview is to provide insight into what drove this and how I used Google Docs (spreadsheet) to accomplish what was required. The project started by identifying three “Had to..” objectives:
- Allow for easy access from anywhere and on any device,
- Be a dynamic resource tool. This means the content has to update as changes occur, with no effort on the user’s part, and
- Be easy to use! Without ease of use, it would not be used by the executives it was being designed for.
Project Summary
Information Request
“I need ready access to overall stock market updates, trending top news, business news. and access to certain company specific data and trends.”
Development of Concept
Based on this request, a tabbed spreadsheet was developed that would serve as the “Dashboards” for the end user. Here is a summary of each:
Market Snapshot and Current News
This page includes market data for Dow Jones Industrials, S&P 500 Index and the Russell 2000 Index. This information updates automatically based on changes to the underlying data source (Google Finance).
It also includes the top news items from the Reuter’s News Feeds for Business News and Top World News. These update automatically as well — so the top stories are a continually changing stream.
Company Trading Summary
This page provides the current status of stock trading activity based on Yahoo Finance. It provides an instant means to see what is activity and key information for five securities we wanted to track (in this case it was a specific industry).
Quick links to the companies financial statements (on Yahoo and Google Finance) is included as well, and a link to the Company’s recent earnings transcripts (through Seeking Alpha)
Company News and Related
For each Company, the most recent headlines coming through the Yahoo finance news feed are included. Each article is linked to the source article and a tap (or a click) opens up the article for quick review.
Recent SEC Filings
If further in-depth analysis is desired, the SEC filings page provides the access needed. All recent filings for each Company are included and, again, are dynamic. The list updates as the underlying resource changes (SEC.gov). The key filings — 8-K’s, 10-Q’s, 10-K’s and Proxies are conditionally formatted, for easy identification. Easy access to the SEC site is provided through a hyperlink for each company.
Quicklinks
These are links from the major financial information providers — Yahoo, Google, Reuters, MSN, Bloomberg and Seeking Alpha. The user can select their preferred resource and access the Company information with one click/tap.
For each site, the main links are provided — non specific to a particular company. The Home Page, Business News, Market Updates, Industry resource and the Economic Calendar are included.
Building the Backend
I will not delve into this in detail, but each of the “tabbed” pages mentioned above are pulling from a “Resource” page that uses Google functions to pull the information from the Internet. This information is updated approximately every 5 minutes.
Suffice it to say that some significant knowledge gathering needs to be done prior to undertaking this effort. A great resource to learn about extracting information from websites, etc. can be found in “The ImportXML Guide for Google Docs”. It is intimidating to read, but trial and error will get you started working before too long.
The primary functions used for this dashboard are listed below:
Imports data from any of various structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds.
Used to extract required information from web pages and provide for continual updating.
Imports a RSS or ATOM feed.
This is the method to incorporate news feeds and provide for automatic updating.
Creates an active hyperlink in a cell.
This is the way single click access to other resources is managed.
Publishing the Dashboard
Once the spreadsheet is finalized, you simply “Publish” it to the web and share the link. Anyone with the link can access the dashboard by signing into their Google account.
Here is how you publish a spreadsheet to the web:
Click here for YouTube Video (provides an excellent walkthrough of the process) — “Publishing a Google Spreadsheet to the Web”
Google overview “Publishing your documents, spreadsheets, presentations, and drawings”
Once it is published, simply share the link and you are DONE!
Access The Sample Dashboard
If you want to play around with the dashboard and see how it works, please visit this Google Form (4 easy questions). Once you submit it, you will see a link to access the Dashboard. Thank you!
About Don
Don Tomoff is a “recovering CPA”, who is passionate about helping organizations adapt to the changing business world that we operate in.
One lesson learned over the years is that all of us, regardless of organization type or size, struggle with similar issues — primarily information management and presentation, and effective use of our time. Let’s change that…one person at a time!
Connect with Don!
LinkedIn, Flipboard, Twitter, Snapchat
