Q: Getting Stock Quotes Into iWork 3.1

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's symbolsStartRow) of column ((t's symbolsColNum) + 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's text item delimiters} to {AppleScript's text 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's text item delimiters} to {AppleScript's text item delimiters, ","}
• setstrLsttotext itemsofstr
•   set AppleScript's text item delimiters to tab
• settsvStrtostrLstastext
•   set AppleScript's text item delimiters to oTid
• returntsvStr
• onerror
•   display alert "Error!"
• return
• endtry
• end commaToTab
• to findReplace(tt, f, r)
•   set {AppleScript's text item delimiters, oTid} to {f, AppleScript's text item delimiters}
• setlsttotext itemsoftt
•   set AppleScript's text item delimiters to r
• settttolstasstring
•   set AppleScript's text 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'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

  display alert "Error!"

  return

  end try

end commaToTab

to findReplace(tt, f, r)

  set {AppleScript's text item delimiters, oTid} to {f, AppleScript's text item delimiters}

  set lst to text items of tt

  set AppleScript's text item delimiters to r

  set tt to lst as string

  set AppleScript's text 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,

Figured it out:

  tell sheet (t's endSheet) to tell table (t's endTable) to set the selection range to column 1's last cell

crashmeister wrote:

 

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

This selects a sheet.  Your script selected the last cell in a particular table.

SG

tell application "Numbers" to tell front document

  set active sheet to sheet "MySheetName"

end tell

Thanks SG, much cleaner.