Getting Stock Quotes Into iWork 3.1

Here's a script with better coding and error checking. It's faster because it doesn't travel to the Yahoo url twice (not sure how that mistake crept into the previous script). It places the data in the columns immediately to the right of the columns with the symbols.

SG

(*

Retrieves stock quotes and other data from Yahoo! and places them in a Numbers table.

Place symbols in specified column starting at specified row number; no blanks; no footer row.

Retrieved data will be placed in columns immediately to the right of the symbols column.

By SGIII, 201403 v. 1.1

*)

--In the following line specify the types of data to retrieve in the order desired:

property quoteProperties : "l1r0e7e8s6"

(*

See https://code.google.com/p/yahoo-finance-managed/wiki/enumQuoteProperty

Example: last trade price followed by pe followed by this year's estimated EPS ==> "l1roe7"

*)

--In the following line change the default values in black as needed to target the desired table:

property t : {targetDoc:1, targetSheet:1, targetTable:1, symbolsColNum:1, symbolsStartRow:2}

(*

Example: To populate a table named "Quotes" in sheet "Portfolio A" in document "Investments" with symbols starting on row 2 of column B==>

{targetDoc:"Investments.numbers", targetSheet:"Portfolio A", targetTable:"Quotes",symbolsColNum:2, symbolsStartRow:2}

*)

tell application "Numbers" to tell document (t's targetDoc) to tell sheet (t's targetSheet) to tell table (t's targetTable)

tell column (t's symbolsColNum) to set symbConcat to my joinList(value of cells (t's symbolsStartRow as number) thru (count cells), "+")

try

set the clipboard to my commaToTab(my getYData(symbConcat, quoteProperties))

set pasteStr to the clipboard

on error

return --halt script if error getting Yahoo data

end try

set the selection range to cell (t'ssymbolsStartRow) of column ((t'ssymbolsColNum) + 1)

tell application "Numbers" to activate

tell application "System Events" to keystroke "v" using {option down, shift down, command down}

display notification "Stock data has been updated" with title "Numbers"

end tell

to getYData(qSymb, qProp) -- get Yahoo! data

try

set baseURL to "http://download.finance.yahoo.com/d/quotes.csv?"

set {symbStr, propStr} to {"s=" & qSymb, "&f=" & qProp}

set yData to do shell script "curl -s " & quoted form of (baseURL & symbStr & propStr)

if yData's text 1 thru 2 is "<!" then error --intercept Yahoo error page

if yData's text 1 thru 3 is "0.0" then error --a little more error-checking

return yData

on error

display alert "Trouble getting data from Yahoo! Check symbols. Ensure there are no blanks in the column and no footer rows, and that you have the right values in t's properties."

return

end try

end getYData

to joinList(aList, separator) --convert AS list to delimited string

set {oTid, AppleScript'stext item delimiters} to {AppleScript'stext item delimiters, separator}

set lstStr to aList as string

set text item delimiters of AppleScript to oTid

return lstStr

end joinList

to commaToTab(str) -- Numbers 3 wants tab-separated for pasting

try

set {oTid, AppleScript'stext item delimiters} to {AppleScript'stext item delimiters, ","}

set strLst to text items of str

set AppleScript'stext item delimiters to tab

set tsvStr to strLst as text

set AppleScript'stext item delimiters to oTid

return tsvStr

on error

return

end try

end commaToTab

--end of script

That is absolutely fantastic! And super fast.

Great job and it worked beautifully, even when I modified as per your instructions the column and the data I wanted returned.

Kudos to you. Thanks again.

Thanks for the positive feedback! It's not quite fast enough to beat the HFT (high-frequency traders). But it is working better than I thought it would. Maybe it will help us make money, or at least lose less money.🙂

I noticed a superfluous line (originally for debugging) that doesn't do harm but probably should be removed to save a millisecond or so:

set pasteStr to the clipboard

Also if you want the quote updates to run automatically at set time intervals you can do this:

add this on a line before the tellapplication "Numbers" block:

on idle

and these two lines right after the endtell for that block, just before to getYData():

return 120 --run every 120 seconds (change number as desired)

end idle

Then, in AppleScript Editor, option File > Save as > File Format: Application

with the box checked before 'Stay open after run handler'

Then, when you want the updates to start, just launch it as you would any other app. When you want it to stop, find it on the Dock, right-click, and Quit. If you opt for quite frequent automatic updates, you'll probably want to remove or comment out (by typing leading -- ) the display notificationline.

SG

SGIII,

I want to thank you for the script for getting stock quotes into Numbers 3. I had for a long time been using modified script I created from the work of Yvan Koenig but under Numbers 3 it was throwing errors. So using your script save me debugging time.

I have one question. What are the script commands to move focus to a specific sheet in a multi-sheet Numbers file? You stock quotes script of necessity puts focus into the sheet with the list of stock quotes and I'd like to have the script change focus to another sheet when it completes.

Thanks again!

Glad to hear it helped.

Larry Nolan wrote:

What are the script commands to move focus to a specific sheet in a multi-sheet Numbers file? You stock quotes script of necessity puts focus into the sheet with the list of stock quotes and I'd like to have the script change focus to another sheet when it completes.

You could use something like this handler, which puts the focus on cell A1 of the first table of your target sheet. You can change that as needed. You can also use sheetName instead of sheetNum.

to goToSheetNumber(sheetNum)

try

tell application "Numbers" to tell front document

tell sheet sheetNum

tell table 1

set selection range to cell 1 of row 1

end tell

end tell

end tell

on error

display alert "Can't find sheet or sheet doesn't have a table" buttons "OK"

end try

end goToSheetNumber

I'm assuming you are generally at home with AppleScript. If you need specifics on how to use this, just post.

SG

Thanks SG; I did modify the handler to use sheetName and it works well for my use.

Larry

Anyone know if there is a way to execute scripts on an iPad 7.1? Now that Numbers docs are compatible between Mac and iPad it would be great if I could also run scripts on either debice.

Alas, AppleScript won't run on iOS, at least not yet. Python (via the excellent app Pythonista and others) will run on iOS but can't directly interact with Numbers (though it can access the clipboard and pass data to Numbers that way). So probably the best approach is to take advantage of AppleScript for automation on the Mac and sync to iOS.

SG

SGIII,

great script, but being a German non-scripter, I have a problem: The script uses ".", where my Numbers expects "," and possibly even uses "," as a thousands limiter, which is a nono in Germany. IIRC there was an old Numbers '09 script that was more flexible. Is there any chance, that you either expand your script or give hints how to try to do it?

Thanks in advance!

Hi Herbert,

The script uses ".", where my Numbers expects "," and possibly even uses "," as a thousands limiter

Actually, I don't think any script above uses "." or ",". They simply downloads the data in the format that Yahoo uses, and Yahoo! uses the North American convention. I think you are asking to add code to convert the format Yahoo uses to the European format. That shouldn't be too hard.

IIRC there was an old Numbers '09 script that was more flexible. Is there any chance, that you either expand your script or give hints how to try to do it?

I am not familiar with a stock quotes script that uses the European format. I'd like to study it. Do know where I might be able to find it?

SG

SGIII,

I found it here in the discussion board, but discarded the link once I found your 3.x compatible version. Tomorrow I might be able to find it again and supply the reference.

Herbert

This script by KOENIG Yvan was the one I remembered: https://discussions.apple.com/thread/2515906

Hi Herbert,

That script is a little too long and complicated for me.

Below is a script that is the same as above with a minimal addition of code to convert periods to commas for "European format" where the comma is used to designate decimals. This should produce the result that you want:

SG

(*

Retrieves stock quotes and other data from Yahoo! and places them in a Numbers table.

Place symbols in specified column starting at specified row number; no blanks; no footer row.

Retrieved data will be placed in columns immediately to the right of the symbols column.

By SGIII, 201403 v. 1.1

*)

--In the following line specify the types of data to retrieve in the order desired:

property quoteProperties : "l1r0e7e8s6"

(*

See https://code.google.com/p/yahoo-finance-managed/wiki/enumQuoteProperty

Example: last trade price followed by pe followed by this year's estimated EPS ==> "l1roe7"

*)

--In the following line change the default values in black as needed to target the desired table:

property t : {targetDoc:1, targetSheet:1, targetTable:1, symbolsColNum:1, symbolsStartRow:2}

(*

Example: To populate a table named "Quotes" in sheet "Portfolio A" in document "Investments" with symbols starting on row 2 of column B==>

{targetDoc:"Investments.numbers", targetSheet:"Portfolio A", targetTable:"Quotes",symbolsColNum:2, symbolsStartRow:2}

*)

property decSymbol : ","

tell application "Numbers" to tell document (t's targetDoc) to tell sheet (t's targetSheet) to tell table (t's targetTable)

tell column (t's symbolsColNum) to set symbConcat to my joinList(value of cells (t's symbolsStartRow as number) thru (count cells), "+")

try

set the clipboard to my commaToTab(my getYData(symbConcat, quoteProperties))

--add this line to convert decimals from . to , ("European format")

set the clipboard to my findReplace(the clipboard, ".", ",")

set pasteStr to the clipboard

on error

return --halt script if error getting Yahoo data

end try

set the selection range to cell (t'ssymbolsStartRow) of column ((t'ssymbolsColNum) + 1)

tell application "Numbers" to activate

tell application "System Events" to keystroke "v" using {option down, shift down, command down}

display notification "Stock data has been updated" with title "Numbers"

end tell

to getYData(qSymb, qProp) -- get Yahoo! data

try

set baseURL to "http://download.finance.yahoo.com/d/quotes.csv?"

set {symbStr, propStr} to {"s=" & qSymb, "&f=" & qProp}

set yData to do shell script "curl -s " & quoted form of (baseURL & symbStr & propStr)

if yData's text 1 thru 2 is "<!" then error --intercept Yahoo error page

if yData's text 1 thru 3 is "0.0" then error --a little more error-checking

return yData

on error

display alert "Trouble getting data from Yahoo! Check symbols. Ensure there are no blanks in the column and no footer rows, and that you have the right values in t's properties."

return

end try

end getYData

to joinList(aList, separator) --convert AS list to delimited string

set {oTid, AppleScript'stext item delimiters} to {AppleScript'stext item delimiters, separator}

set lstStr to aList as string

set text item delimiters of AppleScript to oTid

return lstStr

end joinList

to commaToTab(str) -- Numbers 3 wants tab-separated for pasting

try

set {oTid, AppleScript'stext item delimiters} to {AppleScript'stext item delimiters, ","}

set strLst to text items of str

set AppleScript'stext item delimiters to tab

set tsvStr to strLst as text

set AppleScript'stext item delimiters to oTid

return tsvStr

on error

display alert "Error!"

return

end try

end commaToTab

to findReplace(tt, f, r)

set {AppleScript'stext item delimiters, oTid} to {f, AppleScript'stext item delimiters}

set lst to text items of tt

set AppleScript'stext item delimiters to r

set tt to lst as string

set AppleScript'stext item delimiters to oTid

return tt

end findReplace

--end of script

Thanks a lot. Works fine.

Herbert

SGIII,

thanks for the great info, the script is what I've been seeking out for ages and works wonderful.

My problem is I don't know how to get the applescript into Numbers Services as the picture shows. I've tried going through automator, but the script doesn't show up anywhere.

Please help?

tabbycat 14