Excel makes it really simple to pull in latest stock information including its one-year price history, company description, even number of employees and more. In this post, I share a sample table with the latest data at the time of writing. The table below shows some of the information that are dynamic, real-time:
It shows current prices, day’s high/low, volume, 1-years high/low and even calcuates the % Change over previous day’s price. The About column shows a text description of the company as filed with SEC. There’s no employee data returned for NIKOLA at this time, hence the #FIELD! error. To make it even easier to read, I applied in-cell bars to the percentage change column….red bar means negative change over previous day.
Now that we have all the information, we can chart the prices, as well as Change in percentage. The following chart has 2 Y axes. The blue axis is for price scale, while the green is for Change % scale.
We can also compare the number of employees (they seem to include all types of employments including contractors) for these companies in a simple chart below:
It all starts with a list of company names we want to retrieve info for. With little or no other help, Excel will find those names as long as they’re traded in the US stock markets. However, in order to retrieve their attributes, we have to tell Excel that these are special types of data. To do that, we need to set the data types to Stocks type from Data menu.
Once the names are resolved and converted to Stock type, a market icon will appear next to them as shown below:
Once we have this, we can retrieve their attributes by referencing their cells direcly. e.g. if the above names are in column B, we can say B4.[Ticker symbol] to get the official symbol, or B4.[Previous close] to get previous day’s close price, etc. All this without having to explicitly making any web service calls, or having to even know the symbols, or scraping any data from NASDAQ or S&P for example! This is a really neat feature in Excel for stocks tracking.
I would have liked to share the file online, however, Excel does not enable the data refresh feature online unless the visitor has Excel on their machine that’s being used to view it. However, if you’d like to get more information on this, see the footer.
Hope you found it interesting.
▛ This post is not meant to be a formal tutorial, instead it is to offer key concepts, and approaches to problem-solving. Basic->Intermediate technical/coding knowledge is assumed. If you like more info or source file to learn/use, or need more basic assistance, you may contact me at tanman1129 at hotmail dot com. To support this voluntary effort, you can donate via Paypal from the button in my Home page, or become a Patron via my Patreon site. A supporter/patron gets direct answers on any of my blogs including code/data/source files whenever possible. At the very least, it’ll encourage me to share more posts/tips/knowledge in the future. ▟
