Q: Getting Stock Quotes Into iWork 3.1
-
Jan 25, 2014 10:06 AMby SGIII,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 to prices
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
-
Jan 26, 2014 6:46 PMby SGIII,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 to paste_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
-
Jan 27, 2014 3:57 PMby SGIII,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
-
Mar 10, 2014 6:34 AMby JonSamson,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
-----
-
Mar 10, 2014 6:36 AMby JonSamson,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!
-
Mar 10, 2014 10:15 AMby SGIII,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
-
Mar 10, 2014 11:51 AMby JonSamson,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
-
Mar 10, 2014 1:09 PMby SGIII,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
-
Mar 10, 2014 1:18 PMby JonSamson,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
-
Mar 10, 2014 1:56 PMby SGIII,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
-
Mar 10, 2014 4:04 PMby SGIII,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
--
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 to paste_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
-
Mar 12, 2014 1:39 PMby SGIII,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's symbolsStartRow) of column ((t's symbolsColNum) + 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's text item delimiters} to {AppleScript's text 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's text item delimiters} to {AppleScript's text item delimiters, ","}
set strLst to text items of str
set AppleScript's text item delimiters to tab
set tsvStr to strLst as text
set AppleScript's text item delimiters to oTid
return tsvStr
on error
return
end try
end commaToTab
--end of script
-
Mar 12, 2014 7:29 PMby SGIII,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 tell application "Numbers" block:
on idle
and these two lines right after the end tell 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 notification line.
SG
