MikeCavan

Q: Getting Stock Quotes Into iWork 3.1

A few months back I was scouring this community for instructions on how to get stock information into iWork.

 

Folks like Yan Koenig and Bob H. posted some suggestions and I think I was this close to success when iWork 3.0 came out and Automator got blown out of the water. Now that it's back, I would be hugely grateful for The Complete Idiot's Guide to Stock Quotes in iWork 3.1.  Any takers?

 

Mike

Mac mini (Mid 2011), OS X Mavericks (10.9)

Posted on Jan 24, 2014 8:31 AM

Close

Q: Getting Stock Quotes Into iWork 3.1

  • All replies
  • Helpful answers

first Previous Page 6 of 9 last Next
  • by Orchardjoan,

    Orchardjoan Orchardjoan Dec 28, 2014 4:07 PM in response to Hiroto
    Level 1 (9 points)
    Dec 28, 2014 4:07 PM in response to Hiroto

    WOW!!! Thanks that got me all the data. I really appreciate your effort to help.

  • by Hiroto,

    Hiroto Hiroto Dec 29, 2014 1:01 AM in response to Orchardjoan
    Level 5 (7,281 points)
    Dec 29, 2014 1:01 AM in response to Orchardjoan

    You're quite welcome. Glad to hear it serves you well.

     

    Just please note that finance.yahoo.com may change its web page structure and contents at will, and if it does script should hopefully fail but could possibly return wrong data. So you'd better check the validity of the retrieved data by vising the real web page periodically. I did my best to retrieve correct data in current web page but there's no warranties of any kind, now and in the future.

     

    Good luck,

    Hiroto

  • by Orchardjoan,

    Orchardjoan Orchardjoan Apr 27, 2015 2:27 PM in response to SGIII
    Level 1 (9 points)
    Apr 27, 2015 2:27 PM in response to SGIII

    I have been happily using my script files for months now.  When suddenly one that was working quit and I am once again stumped and asking for help.  I can perhaps figure out how to copy the script which isn't long into a reply. But what I was successfully doing was on a numbers document I was taking the first sheet and the first table and using the value of the first cell to go to the 3rd sheet of the same document and the first table and looping through this table to find up a d the symbol which I compared to the value from the first sheet and first table.  Once found  I then took a value from this sheet(third) and table(first) and put it into a column on the first sheet.

     

    Everything was working great and I have used this script for weeks, but suddenly it says there is an error and it expecting a class (which I don't know anything about.)

     

    Has there been an update to applescript and how do I fix this?  Any help would be greatly appreciated.  I was so happy with my progress

  • by SGIII,

    SGIII SGIII Apr 27, 2015 6:49 PM in response to Orchardjoan
    Level 6 (10,637 points)
    Mac OS X
    Apr 27, 2015 6:49 PM in response to Orchardjoan

    Without seeing your script it's hard to guess what might be the problem. I have noticed, however, that Yahoo! has changed some of the quote properties that previously had an optional 0.  For example, you now have to use s for symbol (s0 no longer seems to work) and n for name (n0 no longer works).  So if you are using this properties you might try removing the 0.

     

    SG

  • by Orchardjoan,

    Orchardjoan Orchardjoan Apr 27, 2015 7:19 PM in response to SGIII
    Level 1 (9 points)
    Apr 27, 2015 7:19 PM in response to SGIII

    TThanks for the reply.  This script doesn't use Yahoo Finance. Thanks to your help that script works fine.  This script uses a cell from the first sheet to look up information on the third sheet.  I tried to copy it and paste it into a reply as it is pretty short, and pretty simple I think. The problem with the paste is that the script is shifted and you can't read the first half of each line, so I will have to re type it into a reply tomorrow unless I can figure out how to copy it without losing the first part of each line. 

  • by Orchardjoan,

    Orchardjoan Orchardjoan Apr 27, 2015 7:38 PM in response to SGIII
    Level 1 (9 points)
    Apr 27, 2015 7:38 PM in response to SGIII

    Here is the script I used. The error I get is that it should be a class

     

    (*

    GetQuotes

    AppleScript for Numbers - gets stock names from column 1 of row 2 to rowcount of the frontmost numbers' document, first sheet, first table, gets quotes for them from Yahoo

    in Numbers open a document (single sheet, single table) containing stock symbols in the second column . To get current quotes for them run the script from the Apple Script menu (this menu can be activated in AppleScript Editor preferences)

    *)

    property theApp : "Numbers"

     

     

    on run

      local texte, rowsCount, trueDoc, trueSheet, trueTable, trueDoc1, trueSheet1, trueTable1, rowsCount1

     

      say "starting"

     

      tell application "Numbers"

      activate

     

      end tell

     

      repeat with y from 2 to 31

      (* major loop through stock on first page, loop to find data on sheet 1 of downloaded data*)

      set trueDoc to name of front document

      set trueSheet to name of first sheet of document trueDoc

      set trueTable to name of first table of sheet trueSheet of document trueDoc

     

      tell application "Numbers" to tell document trueDoc to tell sheet trueSheet to tell table trueTable

      tell row (y)

      set stockSym to get value of cell (1)

      end tell

      end tell

     

      (* now find it on last table *)

      set trueDoc1 to name of front document

      set trueSheet1 to name of third sheet of document trueDoc1

      set trueTable1 to name of first table of sheet trueSheet1 of document trueDoc1

      tell application "Numbers" to tell document trueDoc1 to tell sheet trueSheet1 to tell table trueTable1

      set rowsCount1 to row count

      end tell

      repeat with x from 2 to rowsCount1

      tell application "Numbers" to tell document trueDoc1 to tell sheet trueSheet1 to tell table trueTable1

      tell row

      set parms to get value of cell (1)

      set setEX to get value of cell (7)

      set annDiv to get value of cell (4)

      set nextPay to get value of cell (9)

      if parms = stockSym then

      tell application "Numbers" to tell document trueDoc to tell sheet trueSheet to tell table trueTable

      tell row (y)

      set value of cell (10) to annDiv

      set value of cell (16) to setEX

      set value of cell (17) to nextPay

      end tell

      end tell

      end if

      end tell

      end tell

     

      end repeat

      end repeat

     

     

      say "get quotes done"

    end run

     

     

    on download(t)

      set t to do shell script "curl " & quoted form of t

      return

    end download

  • by Orchardjoan,

    Orchardjoan Orchardjoan Apr 27, 2015 7:42 PM in response to SGIII
    Level 1 (9 points)
    Apr 27, 2015 7:42 PM in response to SGIII

    The error said "syntax error   expected class name but found identifier."   However that is the same lines I used in the script to put the Yahoo finance data into the cells on the first sheet.  I just don't know what happened to have it quit working.  Especially since the other script with the same starting lines still works fine.

  • by Orchardjoan,

    Orchardjoan Orchardjoan Apr 28, 2015 5:20 AM in response to Orchardjoan
    Level 1 (9 points)
    Apr 28, 2015 5:20 AM in response to Orchardjoan

    TThe description is in error. It should not say I go to Yahoo. That is a leftover description from my script to pull information on stocks.  This script just goes to sheet 3 looks up a stock symbol from sheet 1 puts a value into a cell on sheet one.  It loops through sheet one until each stock has had the cell updated.  On page 3 there is one table. It contains all of the stocks on sheet one, plus a few more so the loops are basically : major loop rows of stock I have 2-31; inside loop 2-rowcount1 for look up.

     

    i Don't know how to edit my reply to correct this information

  • by Orchardjoan,

    Orchardjoan Orchardjoan Apr 28, 2015 3:25 PM in response to SGIII
    Level 1 (9 points)
    Apr 28, 2015 3:25 PM in response to SGIII

    I have been playing with this a long time.  Here is my latest. but I still get a syntax error no matter how I try to format the tell end tell portions. the funny is really(x) in the script.

     

    (*

    AppleScript for Numbers -  a numbers document has been opened. Now gets stock names from column 1 of row 2 to row 31 of the  document's first sheet, first table containing stock symbols in the column . Use each symbol on this first sheet to loop through the symbols on the third sheet; find the symbol and move the dividend date from cell on third sheet to cells on the first sheet

    *)

    property theApp : "Numbers"

     

     

    on run

      local   trueDoc, trueSheet, trueTable, trueDoc1, trueSheet1, trueTable1, rowsCount1

      say "starting"

     

      tell application "Numbers"

      activate

     

     

      set trueDoc to name of front document

      set trueSheet to name of first sheet of document trueDoc

      set trueTable to name of first table of sheet trueSheet of document trueDoc

      end tell

     

      (* major loop through stock on first sheet, for each stock find data on sheet 3 and copy 3 cells to

        first sheet *)

      repeat with y from 2 to 31

      tell application "Numbers" to tell document trueDoc to tell sheet trueSheet to tell table trueTable

      tell row (y)

      set stockSym to get value of cell (1)

      end tell

      end tell

     

      (* now find it on last table *)

      set trueSheet1 to name of third sheet of document trueDoc

      set trueTable1 to name of first table of sheet trueSheet1 of document trueDoc

      tell application "Numbers" to tell document trueDoc1 to tell sheet trueSheet1 to tell table trueTable1

      set rowsCount1 to row count

      end tell

      repeat with x from 2 to rowsCount1

      tell application "Numbers" to tell document trueDoc1 to tell sheet trueSheet1 to tell table trueTable1

      tell row

      set parms to get value of cell (1)

      set setEX to get value of cell (7)

      set annDiv to get value of cell (4)

      set nextPay to get value of cell (9)

      if parms = stockSym then

      tell application "Numbers" to tell document trueDoc to tell sheet trueSheet to tell table trueTable

      tell row (y)

      set value of cell (10) to annDiv

      set value of cell (16) to setEX

      set value of cell (17) to nextPay

      end tell

      end tell

      end if

      end tell

      end tell

       end repeat

      end repeat

     

      say "dividend information done"

      end run

  • by Orchardjoan,

    Orchardjoan Orchardjoan Apr 29, 2015 5:08 AM in response to Orchardjoan
    Level 1 (9 points)
    Apr 29, 2015 5:08 AM in response to Orchardjoan

    Now I have it almost working. I moved the setting of the variables for the 3rd sheet into the first block of tell.  So I have one last problem to overcome.  The script goes to the last say and then I assume after that the end run.  BUT when I try to close the script I get the Message

    "cannot close script is still running" . The only way to get out of the script is to force quit Script.  So what do i need to do to have it end without forcing the application to quit?

     

    Thanks everyone for your patience.

  • by jeff fromsilverspring,

    jeff fromsilverspring jeff fromsilverspring Oct 30, 2015 12:07 PM in response to SGIII
    Level 1 (0 points)
    Oct 30, 2015 12:07 PM in response to SGIII

    Thanks so much for the quick response.  I am a retired Luddite, but will give this a shot.  I handle stock portfolios for a handful of folks, and hate to hand enter prices when needed. 

  • by tabbycat14,

    tabbycat14 tabbycat14 Dec 16, 2015 6:24 PM in response to SGIII
    Level 1 (12 points)
    iWork
    Dec 16, 2015 6:24 PM in response to SGIII

    Hi SGIII,

    You were an immense help to me in the past with putting stock quotes beside a column of stock symbols into Numbers.

     

    I'm wondering if you could tell me how to put the EPS using apple-script beside a column of stock symbols . The script you showed on Jan 26, 2014 6:46pm works great for price, eps and revenue. All I really need is the eps, and don't know how to pare the rest down.

     

    Any thoughts on this would be appreciated.

     

    TCat

  • by SGIII,

    SGIII SGIII Dec 16, 2015 6:53 PM in response to tabbycat14
    Level 6 (10,637 points)
    Mac OS X
    Dec 16, 2015 6:53 PM in response to tabbycat14

    tabbycat14 wrote:

     

    The script you showed on Jan 26, 2014 6:46pm works great for price, eps and revenue. All I really need is the eps, and don't know how to pare the rest down.

     

     

    Hi TCat,

     

    If you just want estimated EPS for the current year try changing the first line to:

     

    set quote_properties to "e7"



    SG

  • by tabbycat14,

    tabbycat14 tabbycat14 Dec 16, 2015 7:21 PM in response to SGIII
    Level 1 (12 points)
    iWork
    Dec 16, 2015 7:21 PM in response to SGIII

    SGIII

     

    Everything went really smooth, thanks for the info.

     

    TCat

  • by resullivan,

    resullivan resullivan Dec 30, 2015 1:48 AM in response to SGIII
    Level 1 (0 points)
    Dec 30, 2015 1:48 AM in response to SGIII

    Hi SG

     

    I'm quite new to using Automator and I cannot get this to work as a service. I am using your Script from Mar 12, 2014 1:39 PM but I keep getting the error The action “Run AppleScript” encountered an error.

     

    I might have got something wrong in the setup of the service but I really don't know. Any help would be much appreciated.

     

    Thanks

first Previous Page 6 of 9 last Next