When the stock quotes feature was added to numbers last month, the formulas would update when the market was open. Now after this last numbers update, the stock quotes are only showing yesterdays closing amount.
Is there a way that this can be changed to make it a more live quote, i.e. a 15 minute delay or a manual force refresh? Otherwise the stock quote formula has become virtually worthless in numbers.
Thanks
MacBook Pro (13-inch, Late 2016, 4 TBT3)
Orchardjoan wrote:
If so is there a way that I can create a script which picks up the stock name from the first column and puts the formula STOCK in another column and have it pick up yesterday’s price when I run the script?
If the formulas are not updating correctly then there should be a way to have a script "kick start" the update.
But first, could you test with v 4.3.1, just out today. Maybe that issue has been addressed.
SG
Are the prices for the Thai stocks being updated on Yahoo! Finance? That's where Numbers gets the data.
SG
I haven't yet looked into any alternative (free) API for "near real-time" price quotes. I'm still happy using the STOCK and STOCKH functions in Numbers for previous close and older historical quotes. In normal times I've found those sufficient for my spreadsheet analysis work (let's hope we don't go through abnormal times like 10 years ago when prices would frequently change 10% from one day to the next!).
I've never really tried to use a spreadsheet as an intraday portfolio tracking or trading system, as apparently others have been doing. For me the Yahoo! Finance API was mostly a convenient way to gather fundamental information about a few companies at once. For intraday quotes I've found that setting up a portfolio on the Yahoo! Finance website or other sites (or perhaps even Google Sheets) to be a simple and reliable solution.
If you come across another good API please do post here, and I'll see if it's scriptable similar to the old Yahoo! Finance.
SG
I think Yahoo! Finance (Yahoo! was recently bought by Verizon) may have discontinued the quotes "API" service.
Bummer, if true.
SG
I am having a slightly different issue - I invest in the Thailand stock market (the SET). I used to be able to get last day's close on all my stocks, however there now appears to be a problem with the feed - some of my stocks are loading with data that is days out of date. This only started happening a few days ago, but any help in understanding why it's happening and what, if anything, I can do to get it fixed would be great.
Yes, it's updating on Yahoo Finance but still not in my spreadsheet.
I saw your post about the script - I tried it but wasn't able to make it work. Maybe I didn't understand how to use it? In it, on the line
set {d, s, t} to {1, "My Sheet", "My Quotes"}
-- front doc, sheet named "My Sheet", table named "My Quotes”
I substituted in “Stock manager.numbers” (in all sorts of various variations) for the name of the file and the editor kept giving me error messages (I know nothing about scripts and so am probably doing something silly and also very wrong…). The script never ran past that point.
No worries. It can be a little confusing when first starting out with scripts, though I tried to make this one as easy to configure as possible. It needs to know what document, what sheet within that document, and what table on that sheet.
You are probably going to have your document open when you run the script. If so, you can simply leave the 1 as is, and don't worry about entering the name of your document. The 1 simply means the currently "frontmost" document.
You will, however, want to change "My Sheet" to the actual name of your sheet (tab) and "My Quotes" to the actual name of the table where you want the quotes. (The name of the sheet and the name of the table need to be in quotes just as my dummy names are).
Post back if you still have problems.
SG
Partial success with your suggestions. I’m getting this in the first cell:
<html><head><title>Yahoo! - 999 Unable to process request at this time -- error 999</title></head><body>Sorry, Unable to process request at this time -- error 999.</body></html>
I’m using this as the script:
set {d, s, t} to {1, "Prices", "Prices"}
-- front doc, sheet named "My Sheet", table named "My Quotes"
set quoteProps to "l1" --
For more props see http://www.jarloo.com/yahoo_finance/
set baseURL to "http://download.finance.yahoo.com/d/quotes.csv?"
tell application "Numbers" to tell documentd'ssheets'stablet
set theSymb to column 1's (cells 2 thru -1)'s value
set AppleScript'stext item delimiters to "+"
set theSymb to "s=" & theSymb
set quoteProps to "&f=" & quoteProps
set the clipboard to (do shell script "curl -s " & (baseURL & theSymb & quoteProps)'s quoted form)
set selection range to range "B2"
activate
tell application "System Events" to keystroke "v" using {option down, shift down, command down}
end tell
I had thought that perhaps it was because the market wasn’t open and so waited until after open and still got this result (still got this thinking that waiting 15 minutes past opening might make a difference - nope).
One weird thing I did notice with Yahoo Finance is that refreshing the page with a stock such as TRUE.BK would work fine. However, with say TCB.BK, Yahoo would return with no data and I’d have to retype the ticker into the search bar.
Ug. Oh well... I'll just manually go through and adjust what needs adjusting. Thankfully there are only 650 or so stocks on the SET.
Thanks so much for all the help. If ever you find yourself in the Chiang Mai area, let me know - I owe you a beer.
I was wrong saying that when I opened numbers the cells with STOCK did not update each time. I just changed my cells to stock and checked for two days and they were updated with the prior days close each time. So I am OK now. Still wish they had more current data, even today's close instead of yesterday would be better.
Thanks again
You can give feed to Apple via Numbers > Provide Numbers Feedback in your menu.
Not sure why you think the script solution is "fiddling around under the hood." It's essentially a one-click solution, similar to what one would do in Excel You can make it a menu choice if you want, or assign a keyboard shortcut to it. Have you tried it?
BTW, the "close-to-live" quotes were not "silently taken away." These are the release notes, that you can see in the Mac App Store.
SG
"Live" or actually "near live" quotes are not available in the latest version of Numbers. However they are quite easily retrieved via script, as described here, similar to what one would do in Excel.
I still find the STOCK function handy for retrieving a wide variety of stock information, including the previous day's closing prices. There's lots of analysis to be done on stocks that doesn't require constantly updating prices!
SG
I opened my spreadsheet (and that was the only sheet open). Left the 1 in documents place. Got this error:
error "Numbers got an error: Can’t get document 1." number -1728 fromdocument 1
You have been a huge help to me ( and everyone) is there an alternative script that I can use to get current stock prices since YAHOO pulled the API?
I did update numbers so maybe that was the fix. Thanks.
Numbers Stock Quotes are not updating.
