Real-Time Stock Quotes in Microsoft Excel. #dev #mydata #analytics

image

Do you know how to retrieve real-time stock market data into Microsoft Excel?  Yeah, me neither until now.  For years I’ve been doing cut and paste which is slow, error prone and a pain if you want to manage many stocks.

Since I’ve kind of gone “Back to Dev” in my career, I spent some time over Christmas to re-teach me some basic development skills so I started with something that is personal to me.

I have Microsoft Excel 2013 installed so I started with Power Query.  Power Query allows you to search for dataset out on the web.  I was searching for the S&P 500 and it came back with 400.  Well, that works for me.

image

I plopped the data into a spreadsheet and removed the key row.  Who needs a key right?  We have the stock ticker!   That is the key.

Now we are off to writing some code.  I’m not going to give you all the boring details, but utilizing Yahoo Finance, you can get a table CSV feed by passing over your stock ticker.  Then you take that table of data and break it down to the price column for your report.   Basically I’m writing a custom function for Excel in Visual Basic!!  I miss VB.

image

Now, I converted my Power Query into a Table of data that Excel can understand better.  Then I inserted a column for Price.  In that column, I type my custom formula I created and referenced the stockticker column and hit enter.  Since it is referenced as a table of data in Excel, the formula paints down the entire dataset retrieving the latest stock price for that stock.

image

Now I can take that data and put it into a chart and show the Top 10 stocks by price.  Wow, did you know that NVR has a share price of $1,040 today?  Crazy…
image

If you have any questions, go ahead and contact me and I would be happy to answer them.

Enjoy, Patrick