-
All replies
-
Helpful answers
-
Dec 30, 2015 6:43 AM in response to resullivanby SGIII,Hi, it's hard to suggest anything without seeing what you are doing. Have you tried running the script in Script Editor?
SG
-
Dec 30, 2015 6:57 AM in response to SGIIIby resullivan,Here is the script I am using:
property quoteProperties : "l1r0e7e8s6"
property t : {targetDoc:"Investments.numbers", targetSheet:"Master", targetTable:"Master", symbolsColNum:1, 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))
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
How should I be saving this to use as a service within numbers?
Thanks
-
Dec 30, 2015 7:32 AM in response to resullivanby SGIII,Is it working for you in Script Editor?
If so you can put the script in the script menu.
If you want it as a Service, try here or many other places (search for: automator service run applescript). Be sure to set to 'no input' at the top.
SG
-
Dec 30, 2015 8:00 AM in response to SGIIIby resullivan,I am unable to get it to work in Script Editor either, it is displaying the error: Numbers got an error: Can’t get document "Investments.numbers". (-1728)
-
Dec 30, 2015 8:12 AM in response to resullivanby SGIII,Do you have that document (with that exact name) open in Numbers?
SG
-
Dec 30, 2015 8:19 AM in response to SGIIIby resullivan,Yeah I do. I can get this script to work after adding it as a service but I want it to paste the data into the cells immediately to the right rather than me doing it manually. Here is the script:
set quote_properties to "l1c1p2e7e8s6"
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 dialog "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
-
Dec 30, 2015 2:22 PM in response to resullivanby SGIII,resullivan wrote:
I want it to paste the data into the cells immediately to the right rather than me doing it manually. Here is the script:
This one should do what you want. It's been a while since I've looked at these but they should still work.
SG
-
Dec 30, 2015 3:09 PM in response to SGIIIby resullivan,Using that I just get the error message: The action “Run AppleScript” encountered an error.
My document is called "Investments" and the sheet "Master" and the table "Master" so I have used the line
property t : {targetDoc:"Investments.numbers", targetSheet:"Master", targetTable:"Master", symbolsColNum:1, symbolsStartRow:2}
but that doesn't seem to work. Is there something else I should be changing?
Thanks
-
Dec 30, 2015 6:38 PM in response to resullivanby SGIII,Sorry to hear you are having trouble. I just tried the script I linked to above (This one ) and it ran without error here in Script Editor. Since it's using "gui scripting" to paste the values into Numbers I'm not sure how well it will work within an Automator Service. If you get it working in Script Editor you could try placing the script in the script menu (which will appear if 'Show Script menu in menu bar' is checked under Script Editor > Preferences) and running it from there. Note that to simplify the coding the script assumes you have no blanks in the column with the symbols, i.e. the first header row contains something ('Symbols' or something original like that) and there are no blank rows after the symbols either.
SG
-
-
Jan 18, 2016 2:39 PM in response to SGIIIby tabbycat14,SGIII,
I use this great, getting quotes script, you created, everyday.
Is there a way to add "earnings date release" into a column?
Tcat
-
Jan 18, 2016 4:51 PM in response to tabbycat14by SGIII,H Tcat,
Sadly earnings release date does not seem to be among the properties available via the Yahoo Finance API. It would be quite handy. Just in case you are not aware the dates can be looked up one-by-one here.
SG
-
Jan 19, 2016 2:48 PM in response to SGIIIby tabbycat14,Hi SG,
I've been using this script for a while now, and most of the time would have single properties in quotes, as in "e7" or "s6" etc. Though the script allows multiple quote_properties, when I try to use two or more, it only puts out one.
I was trying to use "gh" over two columns, which are the day's high and low. I've tried spacing, adding 0's, other properties, all to no avail. By them selves they work perfect.
Is this something you might know about, or any thoughts on this?
Tcat
-
Jan 19, 2016 4:09 PM in response to tabbycat14by SGIII,H Tcat,
Which script is "this script"?
And what exactly are you putting between the quotation marks in the line that sets the quote_properties.
SG
-
Jan 19, 2016 7:08 PM in response to SGIIIby tabbycat14,H SG,
The script that is dated Jan 26, 2014, 6:46pm. Get stock quotes into iworks 3.
Instead of set quote_properties to "l1r0e7e8s6", I'm trying to go set quote_properties to "gh". The "g" is day's low, and "h" day's high.
They work individually, but for some reason, not together.
Tcat
