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 4 of 9 last Next
  • by JMANTN,

    JMANTN JMANTN Nov 13, 2014 12:35 PM in response to SGIII
    Level 1 (33 points)
    Photos for Mac
    Nov 13, 2014 12:35 PM in response to SGIII

    That did it!  Funny I have a fully loaded late 2012 iMac so thinking maybe it was getting ahead of itself. This is wonderful!

  • by JMANTN,

    JMANTN JMANTN Nov 13, 2014 12:46 PM in response to SGIII
    Level 1 (33 points)
    Photos for Mac
    Nov 13, 2014 12:46 PM in response to SGIII

    You are the best and I appreciate you taking time to reply with detailed answers and hopefully others will find this of use as well.  Coming from VBA to Applescript is interesting to say the least and with your scripts and replies to me and others I think I have enough know how to fully customize my own script and spreadsheet.

     

    I was referring to shares owned and again your advice is solid however the data I pull prior to that column has never spilled into a separate column but since I'm still fleshing this out within numbers I may end up putting it at the end as you suggested.

     

    Also your input regarding have a data sheet isn't a bad idea and something I did do for other info when I was using excel for my stock info.  

     

    Again, thank you for the help, your code is easy enough to understand to where customizing this shouldn't be an issue, just couldn't understand why it wasn't pasting but now I know

  • by SGIII,

    SGIII SGIII Nov 13, 2014 1:05 PM in response to JMANTN
    Level 6 (10,637 points)
    Mac OS X
    Nov 13, 2014 1:05 PM in response to JMANTN

    Good to know that a delay statement is needed on many machines before having AppleScript execute the paste.

     

    Know what you mean about VBA and AppleScript!  Each has its charms... and pitfalls.  It's amazing to me what a non-technical but reasonably adventurous user can do with either.

     

    You of course don't have to paste into Numbers.  You could write the values into the target cells with a loop.  But I've found that with Numbers a paste works much faster for blocks of data... even after adding that delay  statement.

     

    SG

  • by JMANTN,

    JMANTN JMANTN Nov 13, 2014 5:49 PM in response to SGIII
    Level 1 (33 points)
    Photos for Mac
    Nov 13, 2014 5:49 PM in response to SGIII

    Funny about applescript and VBA just spent two hours finding out how to remove a quote from the spreadsheet, not that hard in either but finding out how was a headache.  Googling for help is a challenge because googling "Numbers" brings up a lot of other stuff (for any beginner try searching iWork Numbers).

     

    I just wish Microsoft Access had a good counterpart that isn't expensive for Mac's and I'd be set but I digress.

     

    The writing cells into Numbers would be leagues over my head, interesting  as it sounds I think that'll end up going to the "maybe one day" list.

     

     

    I did modify your script and posting it here for anyone else interested.  The only change made thus far is that it removes all quotes from the output and I have a ton of stock info some will no doubt want to remove from the initial yahoo fetch and the "property t :" needs to be adjusted for your specific spreadsheet.  For me quotes can be an issue so having a script automatically remove all quotes is a blessing if you intend on using some of the output results in formulas...

     

    Hoping this helps someone!

     

    (*

    Retrieves stock quotes and other data from Yahoo! and places them in a Numbers table at specified time interval when saved as a "stay open" application.

    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, 201406 v. 1.2

    *)

     

    --In the following line specify the types of data to retrieve in the order desired:

    property quoteProperties : "n0l1c1c0o0h0g0m0v0d1t1p0w0m3m4p0c0" --other common choices: "r0e7e8s6" 

    (*

      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:"Numbers - Stocks Nov 2014", targetSheet:"Sheet 1", targetTable:"Table 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}

     

    *)

    on idle

      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))

      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

      delay 0.25

      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

      return 120

    end idle

     

    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 

      set the message_text to yData

      set the message_text to replace_chars(message_text, "\"", "")

      return message_text

      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

      return

      end try

    end commaToTab

     

     

    --This sets up the remove quotes part

    on replace_chars(this_text, search_string, replacement_string)

      set AppleScript's text item delimiters to the search_string

      set the item_list to every text item of this_text

      set AppleScript's text item delimiters to the replacement_string

      set this_text to the item_list as string

      set AppleScript's text item delimiters to ""

      return this_text

    end replace_chars

    --end of script

  • by SGIII,

    SGIII SGIII Nov 13, 2014 6:12 PM in response to JMANTN
    Level 6 (10,637 points)
    Mac OS X
    Nov 13, 2014 6:12 PM in response to JMANTN

    Thanks for posting. In case you haven't come across it already, you can find a good resource on Numbers-specific scripting here.

     

    And http://macscripter.net can be helpful too for AppleScript questions.

     

    To post an AppleScript that indents properly you can paste it in, then temporarily switch to html view and remove the minus sign wherever you see them in patterns like this:

     

          text-indent: -41.6px     (after deletion becomes text-indent: 41.6px)

     

    Then switch back by clicking Show Full Editor.

     

    Strange and easily squashed bug that has persisted for months now.  You'd think our hosts would take care of it. But they seem to have more urgent priorities than making it easy for us to post here.

     

    SG

  • by JMANTN,

    JMANTN JMANTN Nov 13, 2014 8:04 PM in response to SGIII
    Level 1 (33 points)
    Photos for Mac
    Nov 13, 2014 8:04 PM in response to SGIII

    yeah figuring out how to post that wasn't as straight forward as I was thinking as I kept looking for a code option.  They've made a lot of changes to this site but progress always seems to be on the back burner...

     

    Thanks I'll check out that site.  As a late switcher it's not been a bed of roses because a lot of potentially useful info that's out there is outdated.  For example looking for up to date automator tutorials or actions always brings up info from Lion or earlier and some actions you can download won't run correctly.  Another example would be the iWorkautomation.com site it's several years old and wish they had a way to host user made scripts.  Just a shame because I'd think these would be great places for users to contribute.  Always looking for an excuse to use Automator but sadly a lot of useful stuff has been removed or Apple now expects you to link automator to a script.

     

    But I digress, I've been bookmarking a ton of sites lately for Automator, Applescript, Javascript (since Yosemite allows automation that way though I've never used it I can see it becoming popular) and Swift so I appreciate any sites you could suggest!

     

    Also it may just be me but the site you suggested: http://macosxautomation.com/applescript/index.html has like a ton of hidden pages.  Just when I think I've read through all the links on that site I will do a google search for something and that site will have an article up regarding what I was searching for but just browsing the site would never come across it.

     

    Anyways You've been a huge help to me and knowing the longevity of these stock threads for numbers I made this as detailed as possible in case others may find it useful later on.

  • by Orchardjoan,

    Orchardjoan Orchardjoan Dec 16, 2014 1:31 PM in response to SGIII
    Level 1 (9 points)
    Dec 16, 2014 1:31 PM in response to SGIII

    MMy problem is while I can use AppleScript and get data from yahoo finance into numbers, the data isn't accurate, or at least isn't current. for example the expected annual dividend is wrong. It doesn't match a query through the browser to yahoo finance, nor to other stock quote websites.  Also cant find a field for the expected next dividend date. I think I only have the last dividend date.  so I am mistrusting even the latest stock price.  Why does a download not match a browser query?

  • by SGIII,

    SGIII SGIII Dec 16, 2014 1:42 PM in response to Orchardjoan
    Level 6 (10,637 points)
    Mac OS X
    Dec 16, 2014 1:42 PM in response to Orchardjoan

    You'd have to give more specifics before I could shed any light on the problems you are encountering.  I have no idea what quote properties you are using or how you are conducting your "browser query" or whether you are dealing with actively traded companies in a major market.  

     

    Generally I've found Yahoo! data is good for historical and current prices, and generally pretty good for earnings estimates. But I haven't paid much attention to dividend dates.

     

    SG

  • by Orchardjoan,

    Orchardjoan Orchardjoan Dec 16, 2014 5:01 PM in response to SGIII
    Level 1 (9 points)
    Dec 16, 2014 5:01 PM in response to SGIII

    Thanks for taking the time to answer me.

     

    I am using AppleScript on an iMac going into the latest release of numbers on the Mac both are on Yosemite.I am using as a starting point one of the Apple scripts on this forum. The data is from http://" & download.finance.yahoo.com/d/quotes.csv?s="

     

    I use the properties from the yahoo finance API pages.  l1 for current price, trailing annual dividend d0, ex dividend date q0.  What I want is the next ex dividend but don't know if this is the code to download it, but when I go to Safari and query a stock I get the next ex dividend date.  on the same query the expected annualized dividend is different for 15 out of 20 stocks that I checked vs the download using d0. All of the companies are actively traded on NYSE. Companies like GM, IBM, FORD, ATT etc.

     

    I Sure hope you can help. Knowing the expected annualized dividend and next ex dividend date is really important to me.  Right now I am havinfg to query each stock.  And a query to yahoo stock on Safari is how I have been getting the data. That is why I am trying to learn AppleScript.

  • by Orchardjoan,

    Orchardjoan Orchardjoan Dec 17, 2014 5:39 AM in response to SGIII
    Level 1 (9 points)
    Dec 17, 2014 5:39 AM in response to SGIII

    Ok, I figured out what I am getting and why it differs from what a query shows.  The data is what it says it is and I should have known it, the past 4 quarters of the dividends, not what I want and not what a query shows which is the last quarterly dividend * 4. I cannot get the last quarterly dividend from yahoo Even though they have it in the query on a browser.  but I do not know how to download it from Yahoo or from any other site. I would try php to a site, but not sure it would work either.  Also I want to know an announced ex dividend date. Past dates are of little value Which is the date Yahoo provides. So at the moment the useful fields to me are YTD high and current price, although those are very helpful.

     

    ARe you aware of any other sites where I can use AppleScript to get the other 2 fields I want to use?

     

    many thanks  for taking the time to answer applescript questions. This is my first AppleScript and I could not have done it without help.  Btw can I automate the script to tie it to a numbers file and run the whole thing by saving them in a manner on my desktop that I don't have to open the numbers spreadsheet and open AppleScript then open this script then click on the run button?  where would I go to find out how. That is if it is doable. 

  • by SGIII,

    SGIII SGIII Dec 17, 2014 5:54 AM in response to Orchardjoan
    Level 6 (10,637 points)
    Mac OS X
    Dec 17, 2014 5:54 AM in response to Orchardjoan

    Wow, this is a challenging way to start out with AppleScript!

     

    I suspect it is going to be difficult to find an easily automated way (unless you have access to Bloomberg) to get the expected annualized dividend, though one would think the latest quarterly dividend would be accessible.

     

    If you know of a site where you can view the data you want in your browser, post the link and I can have a look and see if there is a way to "screen scrape" the data.  Sometimes it is possible to script that without too much trouble.  In other cases it is tough.  It all depends on the specific site.

     

    SG

  • by Orchardjoan,

    Orchardjoan Orchardjoan Dec 17, 2014 6:30 AM in response to SGIII
    Level 1 (9 points)
    Dec 17, 2014 6:30 AM in response to SGIII

    what an amazingly quick note.  It has been challenging as I haven't coded anything since the dinosaurs, assembly language and huge computers. The site I look at for dividend history is www.dividends.com.  I get alerts on ex dividend dates from an iPad app so not sure where this app gets the ex-dividend announcement. I will try to find out.  I know a little php and mysql from a free online course I just finished. I find AppleScript u unusual, and I would like to learn Swift. Do you think it will replace AppleScript With interface to Numbers?

  • by Orchardjoan,

    Orchardjoan Orchardjoan Dec 17, 2014 9:01 AM in response to SGIII
    Level 1 (9 points)
    Dec 17, 2014 9:01 AM in response to SGIII

    what an amazingly quick note.  It has been challenging as I haven't coded anything since the dinosaurs, assembly language and huge computers. The site I look at for dividend history is www.dividends.com.  I know a little php and mysql from a free online course I just finished. I find AppleScript u unusual, and I would like to learn Swift.

     

    I looked again at  a Yahoo Finance query via Safari.  If you go down to more statistics on any stock, say Verizon, VZ, they show what I want under  dividends.  What I want is called Forward Annual Dividend and ex-dividend date.  Both they claim they retrieve from Morningstar.inc.

    You mentioned "scraping". I don't know anything about this. Is it hard for me to learn how to do??

    Thanks again so much for taking an interest in helping me.

  • by SGIII,

    SGIII SGIII Dec 17, 2014 3:41 PM in response to Orchardjoan
    Level 6 (10,637 points)
    Mac OS X
    Dec 17, 2014 3:41 PM in response to Orchardjoan

    Web scraping is one name for a somewhat messy process of extracting the data you want from the html source of a website.

     

    If you go to http://finance.yahoo.com/q/ks?s=VZ+Key+Statistics and in Safari's Develop menu choose Show Page Source you will see the html for that page in a pane at the bottom.  Select all the html there, copy, and paste into TextEdit or another editor, then search for Forward Annual Dividend rate. You will find some text that looks like this:

     

    <td class="yfnc_tablehead1" width="74%">Forward Annual Dividend Rate<font size="-1"><sup>4</sup></font>:</td><td class="yfnc_tabledata1">2.20</td>

     

    Here I think you want to extract the 2.20.

     

    So it looks feasible to have an AppleScript fetch the html code associated with a web address you give it (for Yahoo! you would set up the url and have the script insert the relevant symbol), then split the retrieved string into pieces and find the number that appears following the first yfnc_tabledata1"> that occurs after Forward Annual Dividend Rate.

     

    This is fiddly and will not win any awards for speed, but may work for you.

     

    For an example of that type of approach (used to fetch an FX currency conversion rate) see the script in this thread.

     

    SG

  • by Orchardjoan,

    Orchardjoan Orchardjoan Dec 17, 2014 5:54 PM in response to SGIII
    Level 1 (9 points)
    Dec 17, 2014 5:54 PM in response to SGIII

    THank you so much for helping.  I can't imagine how much time it took.  It may take me awhile to figure out how to put this into an AppleScript. I can follow how to copy the html and put it into an editor, but not sure from there how I get it into an AppleScript that works with my spreadsheet.  However, thanks so much for giving me so much help.

first Previous Page 4 of 9 last Next