3

I've been working on a mock stock portfolio in Excel, and I've been looking for ways to automatically update the data, eg. stock price and P/E ratio.

I have tried using a web query to MSN Money, but that just brings up the whole stock quote across multiple cells, I want data to be updated in individual cells only. The only web query solution I can think of is if someone hosted a website where each value in the stock quote was saved on a different HTML file. I could then WebQuery to that file for each cell requiring that value. However, no website offers this.

So in essence, is there any tool on Excel 2011 Mac that will let me pull individual values from a stock quote and assign them to a single cell?

  • See this link, which explains how you can create a "web query" in Excel for Mac. I recommend the web query browser under Windows, which lets you select the table you want (instead of the entire web page). One you have your query defined in an Excel sheet under Windows, you can continue in Excel for Mac. To my personal frustration, time and time again I find the Excel for Mac is only half of the Excel for Windows... :-( – agtoever Jan 13 '15 at 14:39
3

For Office 2011 on Mac, create a file (e.g., GoogleQuote) in your /Applications/Microsoft Office 2011/Office/Queries directory with the following contents:

WEB
1
http://download.finance.yahoo.com/d/quotes.csv?s=goog&f=l1

Then, similar to the instructions above:

  1. Go to the cell you want the quote value to be inserted into
  2. Menu bar "Data" > "Get External Data" > "Run Saved Query"
  3. Select the file you created above (e.g., "GoogleQuote")

You can check the "Properties" portion of the window that pops up for additional options (like refreshing as soon as the spreadsheet is opened). Then just select "OK".

You should see a message pop up that gives you a status and then viola, the quote should appear.

2

Since you're on a Mac, there are some command line tools that can make this a straightforward effort.

curl is a command line tool that allows you to retrieve web pages from the command line. In the Mac terminal, type "curl http://www.yahoo.com" and it will return the HTML for Yahoo's home page.

Yahoo makes it possible to retrieve a single stock quote via HTTP. Using the curl example below, we can retrieve a single stock price from Yahoo Finance. In this case we are retrieving the stock price for Google, whose symbol is "goog".

curl -s "http://download.finance.yahoo.com/d/quotes.csv?s=goog&f=l1"

Yahoo makes it easy for us to retrieve several quotes at once. In the example below we are retrieving quotes for Google, Yahoo and General Mills whose symbols are, "goog", "yhoo" and "gis". We simply separate them with a comma.

curl -s "http://download.finance.yahoo.com/d/quotes.csv?s=goog,yhoo,gis&f=l1"

Now that we can retrieve multiple stock quotes, we need to get them into Excel. The simplest way I have found is to save the results of the curl script in a text file and then import that file into Excel.

To make curl save the results to a file we use the "-o" option as in the example below.

curl -o quotes.txt -s "http://download.finance.yahoo.com/d/quotes.csv?s=goog,yhoo,gis&f=l1"

This creates a file in the local directory called "quotes.txt" which you can then import into Excel.

Note: You can also simply click on the link and Safari will download a file called quotes.csv with the prices.

Hope this helps!

Adding how to import into Excel:

After you have your file of stock prices, you can easily import them into Excel.

On the menu bar, choose Data -> Get External Data -> Import Text File

enter image description here

Select your text file with the stock prices in it

enter image description here

On the next screen you have the options, but just click on Finish

enter image description here

You have to select the cell to place the data and then it appears!

enter image description here

  • Thanks for the answer, it explains everything except for the last part, how do I import those values into excel? And is there any way to automate the process? – Vedant Chandra Nov 10 '13 at 4:44
  • I added the steps to import... I don't know of an easy way to automate in Mac. I've looked at Automator and launchd but I don't have much experience with either. I made a simple crontab entry to run every minute, `1-59 * * * * curl -o quotes.txt -s "download.finance.yahoo.com/d/… – ScottAndrewRogers Nov 11 '13 at 16:02
0

If you don't need backwards compatibility with Yahoo, and just want a simple price quote, this VBA snippet adds an Excel Function to return the price of a ticker symbol.

It's not polished code, but should serve as a simple example of how to use the powerful IEX API. (Other stock properties are available as well.)

Use the VBA editor to put this into a Module:

Public Function tickerPrice(ticker As String)

Dim htmlCmd As String
Dim curlCmd As String
Dim shellCmd As String
Dim sResult As String

htmlCmd = "https://api.iextrading.com/1.0/stock/" & ticker & "/quote/delayedPrice"
curlCmd = "curl \""" & htmlCmd & "\"""
shellCmd = "do shell script "" " & curlCmd & " "" "

sResult = MacScript(shellCmd)

tickerPrice = Val(sResult)

End Function

Be sure to enable Macros when opening the workbook.

This was tested with Mac Excel 2011, in High Sierra, during late 2017.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.