Q: Getting Stock Quotes Into iWork 3.1

SGIII,

I want to thank you for the script for getting stock quotes into Numbers 3.  I had for a long time been using modified script I created from the work of Yvan Koenig but under Numbers 3 it was throwing errors.  So using your script save me debugging time.

I have one question.  What are the script commands to move focus to a specific sheet in a multi-sheet Numbers file?  You stock quotes script of necessity puts focus into the sheet with the list of stock quotes and I'd like to have the script change focus to another sheet when it completes.

Thanks again!

Glad to hear it helped.

Larry Nolan wrote:

 

What are the script commands to move focus to a specific sheet in a multi-sheet Numbers file?  You stock quotes script of necessity puts focus into the sheet with the list of stock quotes and I'd like to have the script change focus to another sheet when it completes.

You could use something like this handler, which puts the focus on cell A1 of the first table of your target sheet. You can change that as needed. You can also use sheetName instead of sheetNum.

to goToSheetNumber(sheetNum)

          try

                    tell application "Numbers" to tell front document

                              tell sheet sheetNum

                                        tell table 1

                                                  set selection range to cell 1 of row 1

                                        end tell

                              end tell

                    end tell

          on error

                    display alert "Can't find sheet or sheet doesn't have a table" buttons "OK"

          end try

end goToSheetNumber

I'm assuming you are generally at home with AppleScript.  If you need specifics on how to use this, just post.

SG

Thanks SG; I did modify the handler to use sheetName and it works well for my use.

Larry

Anyone know if there is a way to execute scripts on an iPad 7.1?  Now that Numbers docs are compatible between Mac and iPad it would be great if I could also run scripts on either debice.

Alas, AppleScript won't run on iOS, at least not yet.  Python (via the excellent app Pythonista and others) will run on iOS but can't directly interact with Numbers (though it can access the clipboard and pass data to Numbers that way). So probably the best approach is to take advantage of AppleScript for automation on the Mac and sync to iOS.

SG

SGIII,

great script, but being a German non-scripter, I have a problem: The script uses ".", where my Numbers expects "," and possibly even uses "," as a thousands limiter, which is a nono in Germany. IIRC there was an old Numbers '09 script that was more flexible. Is there any chance, that you either expand  your script or give hints how to try to do it?

Thanks in advance!

Hi Herbert,

The script uses ".", where my Numbers expects "," and possibly even uses "," as a thousands limiter

Actually, I don't think any script above uses "." or ",".  They simply downloads the data in the format that Yahoo uses, and Yahoo! uses the North American convention.  I think you are asking to add code to convert the format Yahoo uses to the European format. That shouldn't be too hard.

IIRC there was an old Numbers '09 script that was more flexible. Is there any chance, that you either expand  your script or give hints how to try to do it?

I am not familiar with a stock quotes script that uses the European format. I'd like to study it.  Do know where I might be able to find it?

SG

SGIII,

I found it here in the discussion board, but discarded the link once I found your 3.x compatible version. Tomorrow I might be able to find it again and supply the reference.

Herbert

This script by KOENIG Yvan was the one I remembered: https://discussions.apple.com/thread/2515906

Hi Herbert,

That script is a little too long and complicated for me.

Below is a script that is the same as above with a minimal addition of code to convert periods to commas for "European format" where the comma is used to designate decimals.  This should produce the result that you want:

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}

*)

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

Thanks a lot. Works fine.

Herbert

SGIII,

thanks for the great info, the script is what I've been seeking out for ages and works wonderful.

My problem is I don't know how to get the applescript into Numbers Services as the picture shows. I've tried going through automator, but the script doesn't show up anywhere.

Please help?

tabbycat 14

Hi tabbycat 14

Glad to hear the script is useful.

See this thread for the steps to put an AppleScript into Automator. Be sure to select Service as document type and have the Service receive 'no input' and specify Numbers.app after 'in:' , etc.

SG

Hi SG,

I have been able to use the 1st script you posted, Jan 25, 2014 with Applescript and its great. I gave up on automator.

My problem is when I enter the code for "updates to run automatically", the script doesn' t work. No ready to paste Msg Box.

When I compile, the word idle turns blue at the top and bottom, which I understand it should.

Any thoughts?

So you're trying to use the script posted January 25, 2014 and it works. But when you add the automatic updates code posted March 12, 2014 you're not getting any results?

I think that's because the January 25 script expects you to have a column of symbols selected.  That kind of defeats the purpose of automatic updates because you can't be off doing something else while automatic updates occur. You have to be there to select the cells with the symbols.

The automatic update code was intended to work with the March 12 script, which doesn't depend on manual selection of cells. You use the property t statement to specify the column and start row for where you've put your symbols.

If you describe your setup and what you want to do in more detail, I can probably give you a script (perhaps within Automator if you prefer to run it from the Services menu) that will do what you want.

SG