Using Live Stock Prices in Numbers

A new feature in Numbers allows you to use live stock prices and other information in your spreadsheets. You can get the current price, volume, change and other data. You can also grab historical data.

Video Transcript
A new ability in Numbers 4.1 is a function that gives you current stock prices. Let's take a look at that. I'll go into a cell right here. I'll type equals and stock, which is the function. I'll also search for it over here so I can see the information about the stock function.

It takes two parameters. The first one is going to be a symbol so for instance the stock symbol. The second one is going to be another value where you can ask for a certain thing like price name, amount change, open price. That kind of thing. Let's start off simply here. I'll do a quote AAPL which is the Apple stock price, close quote there and I will get the value for Apple stock right now. So that's the simplest thing you can do with it. You can do it with anything else you want. So, for instance, stock and if I wanted say Goggle I can do that.

So let's make things a little bit more interesting. Let's delete those and let's put some stock names on the left here. So start with AAPL, and we'll have GOOG as well. Let's use the stock function and use this as the value right there, comma, and then I'll use one of these second parameter things like price. So let's do price, price like that, and it'll give us the price. The cool thing is that if I copy this and paste it here it's of course going to pickup the Goggle stock name rather than Apple and I get that. So now I can add whatever I want here to the left. So I can do Tesla for instance. If I copy and paste that, I get that.

You can use this for other things as well. So, for instance I can do stock and click there of course, comma, and let's use one of these other things I can do; open, close, change, market cap, volume, anything I want. Even there's one here for name, for instance. So let's do that. You can see it gives me the official name for the company. If I copy and paste it here and here then the formula will move accordingly and I get Apple, Inc, Alphabet, Inc which is the official name for Goggle, and Tesla, Inc. You can do that for other things as well. If I wanted to have the volume I can do the volume and I'll copy and paste it for those. So you can kind of keep track of your stocks very easily here. Just add a new row here, just copy and paste this row there, and I can get Amazon's price really easily.

Now you may have noticed that there's another stock related function. It's called Stock H and if I look for the Help for that I can see it gives me the historic information for it. So I can do price for Apple there, and I can say I want the, let's see I have to pick here something a little different. So I can say the closing price or the opening price, or the high. So let's do the closing price and then you have to pick a date. So the date can be in a special format, you know, just like this, so that's January 3, 2017 and that should work there. There we go and I copy and paste that here I'm going to get the closing price for January 3rd for each one of those.

You can do all sorts of other things with the history there. You can also pull from another field what the date is rather than actually typing it there in quotes. One note of caution there is you have to have a date that works. So if I did January 1st, the stock market was closed that day, so there was no closing price and I would get an error.

So it's useful and the stock and stock history functions can be a great addition to your spreadsheets if you use them to track Wall Street.

Comments: 29 Responses to “Using Live Stock Prices in Numbers”

    Jeff Laidlaw
    4/6/17 @ 5:16 pm

    How do I get this “STOCK” function to work with the Australian Stock Exchange

    Bob Joubert
    4/6/17 @ 5:45 pm

    Jeff, type “.AX” after the stock symbol on the ASX (e.g. ANZ.AX) – don’t forget to type the double quote marks – so the function looks like =STOCK”ANZ.AX” it’s the same as using the STOCKS function in Notifications (top right of your Mac’s screen). There’s a note there that stock prices are supplied by Yahoo. There’s also a new ‘Currency’ function as well – see https://support.apple.com/en-au/HT207109

    StockMarketEye
    4/9/17 @ 3:03 pm

    Thanks for the video. Those are great new functions for Numbers and useful for keeping track of a small amount of stocks or doing custom analysis. On the other hand, if you don’t have the time to build your own spreadsheet, you might also consider a dedicated app like StockMarketEye (full disclosure, I’m the developer) or Investoscope.

    Art
    6/13/17 @ 7:10 pm

    Apparently numbers doesn’t always update the stock price. I look at the notifications and check the price on line and they match but the price in the numbers spread sheet seems to be from a day ago. This is what shows in the window at the bottom of the spread sheet for the cell contents $54.36 — BMY, jun 12, 2017, 4:00 PM EDT this is on the 13th of June. Any suggestions on how to update the spreadsheet?

    6/13/17 @ 7:17 pm

    Art: The update of Numbers from today took away real-time stocks. It now just returns the closing price. Must have been a licensing issue is my guess.

    Jeff
    6/14/17 @ 8:01 am

    Is this true about the licensing issue? Numbers is not updating today, you would expect Apple to send out a message if there is a problem?

    6/14/17 @ 8:04 am

    Jeff: I don’t know if that is true that it is a “licensing issue.” It is just “my guess” based on what I know about stock market data and computer data systems.
    But it is true that the STOCK function only gives you the closing price now. If you read the help text for the function, you can see that this is stated clearly now, so it is not a mistake or bug. Also, the release notes in the App Store state: “The Insert Stock Quote feature and the Stock and Currency functions now return data from the previous market day’s close.”

    JohnnyLundy
    6/14/17 @ 1:57 pm

    Argggghhhhhh! I just worked a week on an AppleScript that updates a Numbers sheet when run at 6:20 PM by launchd. The Numbers sheet has several stock fund quotes that the script uses for its calculations.

    Are you sure that the current day closing price is not available? It’s still listed on the popups.

    ray
    6/16/17 @ 5:18 pm

    Yes, I can confirm that Numbers only uses the data from the close of the previous day, not today’s prices and certainly not live as before. Big bummer!

    Norm Johnson
    6/20/17 @ 1:40 pm

    Why can I get up-to-date stock prices via Apple’s app on iPhone, but not in Numbers. Apple had one feature in Numbers that was am improvement over Excel and now they have destroyed that by only providing closing numbers. And, if your spreadsheet in Numbers is open the spreadsheet won’t even update with the previous day’s closing numbers. I had to shut down Numbers and then relaunch it….who is running Apple’s software development? …Google/Microsoft/Samsung employees?

    6/20/17 @ 2:06 pm

    Norm: We can only speculate here. I know that real-time stock prices aren’t free, and they aren’t given to anyone for any reason. I’m pretty sure that Yahoo, who supplies the stock prices in that app, pays handsomely for real-time prices. You get them for free there because Yahoo is paying for it. Apple may have found out that the price was too high to do this in Numbers, or that they were simply denied access for some other reason. Perhaps licenses for stock numbers come with restrictions on using them directly in calculation apps. Just speculation. I’m sure Apple didn’t do this just because — they had a good reason. And we may never know the story.

    Marcus M
    6/21/17 @ 2:31 am

    As for swedish stocks they are not updating at all. Not even to previous close.
    Now that makes this function completely and utterly useless.
    Guess I will have to rewrite everything to go directly against yahoo or google finance instead.
    Thanks a lot for that AAAPPPPPPLLLLLEEEEE

    Thomas Wisniewski
    6/26/17 @ 8:13 am

    Why Numbers doesn’t give current stock quotes ?

    6/26/17 @ 8:33 am

    Thomas: The the earlier comments on this page.

    Jesper
    6/26/17 @ 10:42 am

    Hi,
    can I add several stock quotes from just one formula? Like in Google sheets where
    “=GOOGLEFINANCE(A1,”low”,TODAY()-2,TODAY())” fetches two days quotes and puts them in the table. is that possible in Numbers?

    6/26/17 @ 10:46 am

    Jesper: Not quite sure what you are trying to do. Two numbers in the same cell? That doesn’t make sense. But if you just want the difference between days, then you can just subtract from two STOCKH functions.

    Jesper
    6/26/17 @ 10:50 am

    no not in the same cell. google sheets puts them on separate rows. so “today-2” in google means today’s and yesterdays quote. In apple numbers I always just get one quote. I want to get several quotes :)

    6/26/17 @ 11:06 am

    Jesper: So just put a formula in one cell to get today’s quote and a formula in other cell to get yesterday’s quote.

    Aaron
    6/26/17 @ 10:00 pm

    My spreadsheet is not updating today at all. Its showing data from close on 24th June. I have closed the app and reopened it but still no change. Any ideas?

    6/27/17 @ 7:02 am

    Aaron: The release notes (and function documentation) state:
    “The Stock and Currency functions now return data from the previous market day’s close.”
    You posted this the night of Monday, 6/26. So you would be seeing the prices from Friday, 6/23. The stock market is closed on the weekends.

    Jim quiggle
    6/28/17 @ 7:51 am

    Was using new feature of Number for stock quote.
    It was updating live when market was open. Last week June 21 2017, feature reverted to closing price few hours after close. Miss feature I used on PC excel years ago.

    Bob Foley
    7/1/17 @ 4:47 am

    It would be very helpful if Apple included the as of date of the data available in live stock prices. It appears to be an odd omission.

    7/1/17 @ 8:46 am

    Bob: Double-clcik on the cell with the stock formula and you get a little listing that shows the date.

    Craig
    7/1/17 @ 8:50 am

    Hey – noticed something funny – some of my vanguard mutual funds don’t return a value – sorry new to this thread, but try VTMGX as example?

    7/1/17 @ 8:51 am

    Craig: Works for me. I’m using =STOCK(“VTMGX”) and I get a price.

    Craig
    7/1/17 @ 8:59 am

    Aha – fascinating. I had filled down my formulas that referenced the ticker symbol, and they didn’t work. But if I separately (a new sheet) just did the basic as you showed, it would pull correctly, AND my filled formulas across sheets also populated at that point. It’s like it didn’t work as I hadn’t done it once before, so it must track which symbols you’ve looked up? Not sure – anyway fixed, thanks.

    7/1/17 @ 9:04 am

    Craig: Or, just a temporary problem with Yahoo’s financial servers.

    Craig
    7/1/17 @ 9:13 am

    thought about that, but when i changed the fund to AAPL (which I’ve used before) it populated instantly. But – thanks! Wouldn’t have actually tried that without your thought.

    Carolyn
    7/2/17 @ 5:04 pm

    Anyone having a problem with historical closing prices being different in Numbers than what NASDAQ.com has for historical closing price?

Comments Closed.