Importing Stock Market Data to Excel

Besides tracking my portfolio on StocksCafe, I also maintain my own Excel spreadsheet to track my investment portfolio and personal finance matters. I usually copy and paste the closing price from either Yahoo Finance or StocksCafe manually, but I recently found a method to automate this process. It may require a bit of time to set up, but it saves me the subsequent time and effort to update over 10 stocks that I own.

I was inspired by the video below which works for people who are using Windows.

If you’re a Mac user like me, fret not, because I found a way to achieve the same result.

Step One: Copy and paste the Yahoo Finance web link into Word

Let’s say we are interested in getting the stock price of DBS. We can use Yahoo Finance to arrive at the page below.

Copy and paste the web link into Word. You may have to press “Enter” a few times to ensure that the web link is working (the web link should turn blue).

Step Two: Save the document as a .txt file

Name the file as you wish and save it as a .txt file.

Step Three: Convert the file format from .txt to .iqy

Yes, this is troublesome. In order for Excel to read the web link, it has to be in .iqy format. To do this, right-click the file and select “Get Info”. Then, amend the “.txt” to “.iqy” under “Name & Extension”.

Step Four: Open your stock tracker in Excel

I have created a sample stock tracker in Excel below. The goal here is for the yellow cells to update automatically.

Step Five: Create a new sheet and select “Run Web Query”

Step Six: Select the DBS.iqy file and click “Get Data”

Give the file a couple of seconds to load and you should see the data appear.

Step Seven: Link the price data

The Ask price should be the information that links to the stock tracker in the first sheet. However, as the Ask price is “19.00 x 0”, linking it directly will result in an error.

To work around this, the formula should include “=LEFT(…” to pull the desired data. In this case, it is 5 characters from the left (i.e. “19.00” has 5 characters).

Step Eight: Click “Refresh All” to update the stock tracker

The stock tracker should update automatically, but you can also click the “Refresh All” button under the “Data” tab.

Voila! You now have a dynamic stock tracker

Hope you find the Excel trick valuable and useful. As always, please feel free to leave a comment below or write to me directly if you require any further clarification.