Getting Stock Quotes Into iWork 3.1

SG,

Nice alternative, however, not being a programmer, I'm lost. The {d, s, t} is confusing. As shown below, I put them where they might go, but nothing. If possible, please show an example, in the script, including an APPL stock symbol.

Much appreciated, Tab

"Almost live" stock quotes have been dropped from the latest Numbers release, though other parameters of the STOCK and STOCKH functions still appear to retrieve other data.

Here is an updated way to continue to retrieve "almost live" quotes for use in Numbers. It isn't as convenient as formulas but is shorter and more straightforward than the older scripts posted upthread.

Set up a table like this, giving it a different name if you want. Put the symbols you want in column A. You can have many more than in this example. Don't leave blank rows at the bottom.

2. Copy-paste the script below into Script Editor (in your Applications > Utilities folder).

3. Match the sheet and table names in the script to your actual sheet and table name.

4. Click the triangle 'run' button in Script Editor.

5. Done!

(If "nothing happens" make sure you've enabled Script Editor.app at System Preferences > Security and Privacy > Accessibility).

If used often, then this can be made more convenient by placing it in a Script menu or Automator service and attaching a keyboard shortcut.

Other stock properties can still be obtained via STOCK and STOCKH in formulas.

SG

set {d, s, t} to {1, "My Sheet", "My Quotes"}

-- front doc, sheet named "My Sheet", table named "My Quotes"

setquoteProps to "l1" -- For more properties see http://www.jarloo.com/yahoo_finance/

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

tell application "Numbers" to tell document d's sheet s's table t

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 rangetorange "B2"

activate

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

end tell

SG

Numbers 4.1 for the Mac and Numbers for iOS 3.1 (released March 2017) introduce the STOCK, STOCKH, CURRENCY, CURRENCYH functions that greatly simplify the task of retrieving prices and other common information on stocks and currencies. Scripts are now needed only for less commonly needed attributes.

SG

Not exactly sure what you've done there, but there is no need to change things down in the guts of the script. Change the first line only to match your document setup. The only things you need to change are to the right of theto in the first line, between the { }.

The 1 there simply means the frontmost document. That's almost always what you want, so no need to change that. Substitute the name you are using for your sheet (the tab at the top) where you see "My Sheet" in the first line. Make sure your name still has quotes around it. Substitute the name you are using for your table where you see "My Quotes" in the first line. Make sure your name still has quotes around it.

And make sure the rest of the script is changed back to the way it was.

SG

Hi Mike,

Not an idiots guide, but here is simple script that works well here.

Paste this into AppleScript Editor or a Run AppleScript action in Automator (the indentation should correct itself after hitting the "hammer"):

-- select symbols in a Numbers column (no blanks!), run script, paste values in desired column

tell application "Numbers"

tell front document

tell active sheet

tell (first table whose class of selection range is range)

set symbols to my joinList(value of every cell of selection range, ",")

set prices to my getYData(symbols)

set the clipboard toprices

display dialog "Ready to paste"

end tell

end tell

end tell

end tell

-- handler to get Yahoo! data

to getYData(s)

set base_url to "http://download.finance.yahoo.com/d/quotes.csv?s="

set quote_properties to "&f=l1" -- use l1 for last trade, p0 for previous close

set r to do shell script "curl -s " & "'" & base_url & s & quote_properties & "'"

end getYData

--handler to convert AppleScript list to delimited string

to joinList(a_list, list_delimiter)

set orig_tid to text item delimiters of AppleScript

set text item delimiters of AppleScript to list_delimiter

set r to a_list as string

set text item delimiters of AppleScript to orig_tid

return r

end joinList

To use the script, select one or more cells in a column of symbols, run the script, click once on the first cell in the range where you want the prices, and command-v or option-shift-command-v to paste.

After putting the script in an "Automator Service" my setup looks like this:

I've selected multiple symbols in column A. After running the script, I put my cursor on B2 and paste.

Caveats: This will only work in Numbers 3.1 and up as it uses properties Numbers 2 doesn't have. Also, it has no error handling. There can be no blanks in the cells you select for the symbols, or the script will behave badly! It could be gussied up to include error handling and further automation (e.g., paste in a specified range) but there's something to be said for keeping things basic. The script already does the heavy lifting of getting the data from the web quite well.

For further reading on Yahoo! quotes (you can get far more than prices) see:

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

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

SG

The previous script just brought one column of prices into Numbers. Here's a script to populate multiple columns, including selected fundamental data if you want:

-- select symbols in a Numbers column (no blanks!), run script, paste where wanted

set quote_properties to "l1r0e7e8s6"

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

--p0 prev close; l1 last trade; e7 est EPS curr yr; e8 est EPS next yr; s6 revenue

--p6 p/b; r0 pe; y0 div yield, j4 EBITDA,j2 shares out; n0 name;s0 symbol, etc

tell application "Numbers" to tell front document to tell active sheet

tell (first table whose class of selection range is range)

set symbols to my joinList(value of every cell of selection range, "+")

set paste_str to my csv2tsv(my getYData(symbols, quote_properties))

set the clipboard topaste_str

display notification "Ready to paste"

end tell

end tell

to getYData(symb, q_prop) -- get Yahoo! data

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

set symb_str to "s=" & symb

set property_str to "&f=" & q_prop

set script_str to "curl -s " & "'" & base_url & symb_str & property_str & "'"

set r to do shell script "curl -s " & script_str

end getYData

to joinList(a_list, list_delimiter) --convert AS list to delimited string

set orig_tid to text item delimiters of AppleScript

set text item delimiters of AppleScript to list_delimiter

set r to a_list as string

set text item delimiters of AppleScript to orig_tid

return r

end joinList

to csv2tsv(str) -- Numbers 3 wants tsv for pasting

set orig_tid to text item delimiters of AppleScript

set text item delimiters of AppleScript to ","

set l to text items of str

set text item delimiters of AppleScript to " " --tab

set str to l as text

set text item delimiters of AppleScript to orig_tid

return str

end csv2tsv

So in the following table select cells A2 through A10, run the script, select cell B2 and command-v (or option-shift-command-v) to paste. That's it.

The table expands automatically if it's not wide enough. If you want to doublecheck that you've pasted the right data on the right rows, append s0 to quote_properties to fetch the symbol too and delete that column when it's not needed any more. If you want Yahoo to fetch the name it's best to add the n0 last. That way other columns won't be affected if a name contains a comma, causing an occasional spillover into an extra column.

SG

And if you want to include names, which often have commas within them, replace the csv3tsv handler with this one:

to csv2tsv(str) -- Numbers 3 wants tab-delimited for pasting

--adapted from http://blog.patrick-morgan.net/2005/09/perl-script-to-convert-csv-to-tab.html

set perl_scpt to "

while (<>) {

my @fields = ();

push(@fields, $+) while $_ =~ m{

(\"[^\"]*(?:\"\"[^\"]*)*\"),?

| ([^,\\n\\n]+),?

| ,

}gx;

push(@fields, undef) if substr($_, -1,1) eq ',';

print join(\"\\t\", @fields);

print \"\\n

\";

}"

set output to do shell script "echo " & quoted form of str & " | perl -e " & quoted form of perl_scpt

end csv2tsv

It sure looks ugly, but it can handle csv files that have commas within a field surrounded by quotes so you don't have to do cleanup after an import.

SG

I have Yvan Koenig's original script which works - however has certain errors under the new Applescript. Perhaps someone with better knowledge could look at it and clean it up? Its slow and also returns a huge error string from the website when it cannot retrieve the quote. Can someone help??

property theApp : "Numbers"

--=====

on run

local texte, rowsCount, trueDoc, trueSheet, trueTable

say "starting"

set t3 to "http://" & "download.finance.yahoo.com/d/quotes.csv?s="

tell application "Numbers"

activate

set trueDoc to name of front document

set trueSheet to name of first sheet of document trueDoc

set trueTable to name of first table of sheet trueSheet of document trueDoc

tell application "Numbers" to tell document trueDoc to tell sheet trueSheet to tell table trueTable

set rowsCount to row count

end tell

end tell

repeat with y from 2 to rowsCount

tell application "Numbers" to tell document trueDoc to tell sheet trueSheet to tell table trueTable

tell row (y)

set parms to get value of cell (2)

end tell

set t4 to "&f=l1"

set request to t3 & parms & t4

end tell

set texte to download(request)

try

tell application "Safari" to close window 1

end try

tell application "Numbers" to tell document trueDoc to tell sheet trueSheet to tell table trueTable

tell row (y)

set value of cell (3) to texte

end tell

end tell

end repeat

repeat with y from 2 to rowsCount

tell application "Numbers" to tell document trueDoc to tell sheet trueSheet to tell table trueTable

tell row (y)

set parms to get value of cell (2)

end tell

set t4 to "&f=e"

set request to t3 & parms & t4

end tell

set texte to download(request)

try

tell application "Safari" to close window 1

end try

tell application "Numbers" to tell document trueDoc to tell sheet trueSheet to tell table trueTable

tell row (y)

set value of cell (4) to texte

end tell

end tell

end repeat

beep 1

say "get quotes done"

end run

on download(t)

set t to do shell script "curl " & quoted form of t

return t

end download

-----

Your script doesn't really split the numbers up into separate columns. It puts it into one string. Also, it requires selecting the group of symbols every time. The script I just posted, which is from Yvan does it automatically. Perhaps you are able to look at it and make it more efficient? Thanks!

Your script doesn't really split the numbers up into separate columns. It puts it into one string.

It puts the data in one string. But it is a tab-separated string. That means that after running the script all you have to do is click once in a cell of an existing table and command-v to paste. The data will then split automatically into separate columns. Perhaps you could try again and let me know if it isn't working on your end.

Also, it requires selecting the group of symbols every time. The script I just posted, which is from Yvan does it automatically.

Yes, that is true. But I like the freedom of selecting the symbols for which I want data (wherever I have them in a document) and then pasting the results wherever I want. Yvan's script is less flexible, requiring the symbols to be in a specific place in the first table on the first sheet of a document. It then places the data in a specified place.

Perhaps you are able to look at it and make it more efficient? Thanks!

I'll have a look at it. Meanwhile, it would be great if you could advise whether you are able to paste the data in multiple columns after running my script.

SG

SG,

It is not splitting automatically into separate columns. See screenshot:

I think it would also be very helpful to have the option of all quotes being updated automatically rather than selecting and then pasting. However, I understand where you are coming from. I think, for most of us, there is a watchlist of some sort, and then the symbols are static and the results should be pasted automatically either next to it or in a user defined place?

Regards

Jonathan

Message was edited by: JonSamson

Hi Jonathan,

Some things can get lost when copying pasting from the forum into AppleScript Editor.

I just copied-pasted from my post above and the script didn't do the right thing, until I noticed that the line:

set text item delimiters of AppleScript to " " --tab

does not paste into AppleScript Editor with a single tab between the paretheses. Try deleting the white space between the parentheses and (with the cursor still between them) hit the tab key and save. That should clear up the problem with the columns. Please post if it doesn't, and I'll post a script in a different way.

On the question of where to place the values, where would you want them? Immediately to the right of the column with the symbols?

And about "updating automatically" are you thinking you would like the updates to run at regular time intervals?

SG

Thanks for the fix. That did work.

On the question of where to place the values, where would you want them? Immediately to the right of the column with the symbols?

Yes - immediately to the right. So if I want two values (last quote, EPS), then it should go to the 2 columns to the right of the symbols. If I want 4 values, then four columns to the right of the symbols.

And about "updating automatically" are you thinking you would like the updates to run at regular time intervals?

No - I should have been clearer. While that is a nifty feature, it is not necessary. I meant that the script should automatically recognize Column A" or "B" as the list of symbols, and therefore return the values requested automatically to the right. It should figure that out without having to

1. Select group of symbols

2. Run Script

3. Select cell to paste

4. Paste results.

Instead , it should be

1. Run script

and that's it. The script will do the rest. Does that make sense? *(which is what the script i pasted does, more or less).

Jonathan

1. Select group of symbols

2. Run Script

3. Select cell to paste

4. Paste results.

Instead , it should be

1. Run script

and that's it. The script will do the rest.

That would reduce a click, a drag, a click, a click, and a keystroke to one-click at the cost of flexibility, worthwhile if you're constantly refreshing, you have only one portfolio, and you have only one place you will ever want to place your stock information. I usually like more flexibility. But... shouldn't be hard to do. Will post.

SG

Hi Jonathan,

Does the following work for you better. Edit the properties to fit your setup. As written it assumes one Header Row, no blanks in the list of symbols, and no Footer Row.

The table will expand automatically to accommodate the columns and rows in your data. It won't shrink automatically, however. So if you reduce the number of data items you are retrieving you simply delete extra columns and run.

Will be interested in hearing how it works on your end.

SG

--retrieves stock quotes and other data from Yahoo! and places in Numbers table

--Usage: place symbols in column starting at row 2; no blanks; no footer row

--Change properties to fit your setup

--https://discussions.apple.com/thread/5822522?answerId=25127300022#25127300022

property quote_properties : "l1r0e7e8s6"

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

--p0 prev close; l1 last trade; e7 est EPS curr yr; e8 est EPS next yr; s6 revenue

--p6 p/b; r0 pe; y0 div yield, j4 EBITDA,j2 shares out; n0 name;s0 symbol, etc

property target_sheet : "Sheet 9"

property target_table : "Table 1"

property symbol_column : 1

property data_start_cell : "B2"

tell application "Numbers" to tell front document to tell sheet target_sheet to tell table target_table

tell column symbol_column to set symbols to my joinList(value of cells 2 through (count of cells), "+")

set paste_str to my csv2tsv(my getYData(symbols, quote_properties))

set the clipboard topaste_str

try

set the selection range to cell data_start_cell

on error

display alert "You designated a cell that doesn't exist. Check your table." buttons "OK"

return

end try

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(symb, q_prop) -- get Yahoo! data

try

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

set symb_str to "s=" & symb

set property_str to "&f=" & q_prop

set script_str to "curl -s " & "'" & base_url & symb_str & property_str & "'"

set r to do shell script "curl -s " & script_str

if r's text items 1 thru 2 as text is equal to "<!" then error

return r

on error

display alert "Had trouble getting data from Yahoo! server. Check symbols! No blanks."

end try

end getYData

to joinList(a_list, list_delimiter) --convert AS list to delimited string

set orig_tid to text item delimiters of AppleScript

set text item delimiters of AppleScript to list_delimiter

set r to a_list as string

set text item delimiters of AppleScript to orig_tid

return r

end joinList

to csv2tsv(str) -- Numbers 3 wants tsv for pasting

set orig_tid to text item delimiters of AppleScript

set text item delimiters of AppleScript to ","

set l to text items of str

set text item delimiters of AppleScript to tab

set str to l as text

set text item delimiters of AppleScript to orig_tid

return str

end csv2tsv

--end of script