Getting Stock Quotes Into iWork 3.1

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

I think the list you are looking for is in my first post in this thread.

SG

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>

I guess those links have gone dead. Search for Yahoo Finance API properties. I found some here.

BTW, suggest editing out your personal information in your post.

SG

SGIII

Thanks for all your time spent on helping Newbies like me..

Is there any way to, stead of show a message that a ticker is wrong "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.", the script just write something like #error, and bring all others?

(be kind with my poor English)

From Brazil

Wilbor

Hi Wilbor,

I'm not sure. I'll have to have a look at that. Which script?

SG

This One

(*

• 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:
• propertyquoteProperties : "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:
• propertyt : {targetDoc:"InvestimentosMAC.numbers", targetSheet:"Base", targetTable:"Tabela", 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}
• *)
• propertydecSymbol : ","
• tellapplication "Numbers" totelldocument (t'stargetDoc) totellsheet (t'stargetSheet) totelltable (t'stargetTable)
• tellcolumn (t'ssymbolsColNum) tosetsymbConcattomyjoinList(valueofcells (t'ssymbolsStartRowasnumber) thru (countcells), "+")
• 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
• onerror
• return --halt script if error getting Yahoo data
• endtry
• set the selection range to cell (t'ssymbolsStartRow) of column ((t'ssymbolsColNum) + 1)
• tellapplication "Numbers" toactivate
• tellapplication "System Events" tokeystroke "v" using {option down, shift down, command down}
• display notification "Stock data has been updated" with title "Numbers"
• endtell
• to getYData(qSymb, qProp) -- get Yahoo! data
• try
• setbaseURLto "http://download.finance.yahoo.com/d/quotes.csv?"
• set {symbStr, propStr} to {"s=" & qSymb, "&f=" & qProp}
• setyDatatodo shell script "curl -s " & quoted formof (baseURL & symbStr & propStr)
• ifyData'stext 1 thru 2 is "<!" thenerror--intercept Yahoo error page
• ifyData'stext 1 thru 3 is "0.0" thenerror--a little more error-checking
• returnyData
• onerror
• 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
• endtry
• end getYData
• to joinList(aList, separator) --convert AS list to delimited string
• set {oTid, AppleScript'stext item delimiters} to {AppleScript'stext item delimiters, separator}
• setlstStrtoaListasstring
• set text item delimiters of AppleScript to oTid
• returnlstStr
• end joinList
• to commaToTab(str) -- Numbers 3 wants tab-separated for pasting
• try
• set {oTid, AppleScript'stext item delimiters} to {AppleScript'stext item delimiters, ","}
• setstrLsttotext itemsofstr
• set AppleScript'stext item delimiters to tab
• settsvStrtostrLstastext
• set AppleScript'stext item delimiters to oTid
• returntsvStr
• onerror
• 
  display alert "Error!"
• return
• endtry
• end commaToTab
• to findReplace(tt, f, r)
• set {AppleScript'stext item delimiters, oTid} to {f, AppleScript'stext item delimiters}
• setlsttotext itemsoftt
• set AppleScript'stext item delimiters to r
• settttolstasstring
• set AppleScript'stext item delimiters to oTid
• returntt
• end findReplace
• --end of script

Here's what I get when using the script (pasted back in below):

Note that the script keeps running if you give an invalid symbol, and the cells are filled with N/A.

Just be sure not to have any blank rows in the middle of the table or at the bottom and it should work.

SG

--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:"InvestimentosMAC.numbers", targetSheet:"Base", targetTable:"Tabela", 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

SGIII

It's Working, but Is there any kind of limitation on script or on the API? 'Cause it stop on line 140. I Tried to change some parameter, did't work.

Wilbor Arhur wrote:

It's Working, but Is there any kind of limitation on script or on the API? 'Cause it stop on line 140.

Glad it's working. I've never tried it for that many symbols. Probably Yahoo! has some limit on the API. I don't think it's intended for a very large numbers of calls at one time (essentially your sending it a url address and there is probably a limit on how long those can be).

SG

I haven't had this script work successfully for me since May 27th. Anyone have any ideas about what has changed?

I'd like to help but am not a mind reader. Which script? What behavior are you getting? Error messages? Screen shots? Is there something significant about May 27th?

SG

Its the most previous posting of the script 4 posts back... "May 18, 2016 2:33 PM in response to Wilbor Arhur". I can't think of anything significant happening at that time. That's just the last time I had it work successfully. I can't think of any updates that have occurred during this time, but maybe I'm forgetting something. I have numbers 3.6.2 if that helps. Error message is... well what the heck? Its working again. I guess I wasn't holding my mouth right. Thanks anyway.

Possibly the Yahoo! server has been down from time to time? In any case I haven't heard that anything has changed recently Glad to hear you got it working again.

SG

Thanks for this script. I have been making a couple of minor changes and I got stuck.

I have a Quotes sheet and a Trader sheet. Items on the Trader sheet are populated from the Quotes sheet.

What I want to have happen is to have Numbers return to the Trader sheet after running the script. It currently stays on the Quotes sheet.

So how do you get Applescript to tell numbers to select a sheet?

Thanks,