-
All replies
-
Helpful answers
-
Jan 20, 2016 7:52 AM in response to MikeCavanby Abraham.G,What is this forum all about?
As I understand is it a spreadsheet that's picked up data from the Interne, I'm correct?
Since I'm trading stocks and options I want to use this spreadshee can som share with me the templat with Dropbox or iclout,
not the script since I don't know how to handle it
Thank you!
can I use it with a iPa as well? Or iClou.com?
-
Jan 20, 2016 10:15 AM in response to tabbycat14by SGIII,tabbycat14 wrote:
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.
Hi Tcat,
I'm guessing it may have something to do with that troublesome tab between the quotation marks, which copies from the forum as spaces.
Numbers expects a tab between values on the same row, so the spaces are confusing it.
I've revised the script to try to avoid that problem. Try this version:
set quote_properties to "gh" --"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
--g day's low, h day's high
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
-
Jan 20, 2016 10:21 AM in response to Abraham.Gby SGIII,Abraham.G wrote:
Since I'm trading stocks and options I want to use this spreadshee can som share with me the templat with Dropbox or iclout,
not the script since I don't know how to handle it
A script is needed to retrieve values from the internet and place them on the system clipboard for pasting into a Numbers table. This won't work without a script and won't work on an iPad. Sorry.
BTW, running a script is really easy on the Mac. Copy-paste it into Script Editor (in Applications > Utilities), select cells in Numbers containing symbols, and click the 'run' button.
SG
-
Jan 20, 2016 1:51 PM in response to SGIIIby tabbycat14,SG,
That was the answer. It really simplifies things for me.
Thank you,
Tcat
-
Feb 2, 2016 9:57 AM in response to SGIIIby marcusfromdallas,any idea what I need to do in order to change the dates to pull from so i could possibly pull prices as of a defined date?
-
Feb 2, 2016 10:49 AM in response to marcusfromdallasby SGIII,You can get historical prices for a range of dates with this one:
SG
(*
Retrieves historical stock quotes for pasting into a Numbers table.
Run, click once in a cell, and command-v to paste
By SGIII, rev 201602
*)
--In the following line specify the symbol and period desired:
property q : ¬
{symbol:"GOOG", startMonthNum:1, startDay:15, startYear:2010, endMonthNum:9, endDay:30, endYear:2015}
(*
Example: {symbol:"GOOG", startMonthNum:1, startDay:15, startYear:2010, endMonthNum:12, endDay:15, endYear:2010}
*)
try
set qProperties to "s=" & q's symbol & ¬
"&a=" & ((q's startMonthNum) - 1) & ¬
"&b=" & q's startDay & ¬
"&c=" & q's startYear & ¬
"&d=" & ((q's endMonthNum) - 1) & ¬
"&e=" & q's endDay & ¬
"&f=" & q's endYear
--> s=GOOG&a=0&b=15&c=2010&d=11&e=15&f=2010
-- See https://code.google.com/p/yahoo-finance-managed/wiki/csvHistQuotesDownload
set the clipboard to my commaToTab(my getYData(qProperties))
set pasteStr to the clipboard
on error
return --halt script if error getting Yahoo data
end try
display notification "Historical stock data for " & q's symbol & " is ready to paste" with title "Numbers"
to getYData(qProp) -- get Yahoo! data
try
set baseURL to "http://ichart.yahoo.com/table.csv?"
set yData to do shell script "curl -s " & quoted form of (baseURL & qProp)
if yData's text 1 thru 2 is "<!" then error --intercept Yahoo error page
return yData
on error
display alert "Trouble getting data from Yahoo! Check the symbol. And make sure the end date is after the start date."
return
end try
end getYData
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
--pasteStr--uncomment this line to see results in Result pane for debugging
-
Feb 4, 2016 11:14 AM in response to SGIIIby marcusfromdallas,Good to know but i'm looking to create a spreadsheet for a list of about 500 tickers (columns) on specific dates (rows) so that I can create year to year, month to month, n-period to n-period price changes and % changes. So i'm looking to pull prices for a list of stock at any specific date in history.
-
Feb 4, 2016 11:20 AM in response to marcusfromdallasby SGIII,Sorry, but I don't know a practical way to do that with the Yahoo API.
SG
-
-
-
Mar 9, 2016 11:27 AM in response to Cici Harahapby SGIII,What quote_properties do you have in your script?
SG
-
May 14, 2016 5:55 PM in response to SGIIIby PurduefanGA,HI, me again. You helped me through all my scripting problems with yahoo finance, but now I want to get additional data and I can't find the list of codes for fields that I can get. I found it months ago when I first used Apple script, but can't find it now. Can you help me?
I Want to script is ROE, 5 yr avg yield and debt for a given stock. Thanks for all your help.
-
May 14, 2016 6:18 PM in response to SGIIIby Orchardjoan,CCan you tell me where the code list is for yahoo finance? I need to download a few more fields and no longer have the list. Thanks
-
May 14, 2016 7:06 PM in response to Orchardjoanby SGIII,I think the list you are looking for is in my first post in this thread.
SG
-
May 14, 2016 8:31 PM in response to SGIIIby PurduefanGA,Thanks for your help. It is very kind and generous
Of you. I tried to look at a link in the
Post but got a 404 error.
Can you send it again?
Sent from my iPhone
Danny
***********icloud.com
Danny ***-***-****
Joan ***-***-****
<Personal Information and Email Edited by Host>
