Loading ...
Sorry, an error occurred while loading the content.

Getting quotes data from the Google API

Expand Messages
  • Randy H
    I wouldn t use the smfGetTagContent() that way, because you have no idea if the last data will always be the 12th item in the list. Also, I found I was doing
    Message 1 of 8 , May 1, 2012
    View Source
    • 0 Attachment
      I wouldn't use the smfGetTagContent() that way, because you have no idea if the last data will always be the 12th item in the list.

      Also, I found I was doing the FIND/FIND/MID technique you're using so often, I wrote the smfStrExtr() function to do it it as a user-defined function.

      In any case, this is what I would use for the extraction:

      =0+smfStrExtr(RCHGetWebData("http://www.google.com/ig/api?stock=msft","last data=",50),"=""","""")

      You could extend that formula to any of the other data items by changing the "last data=" search string.

      Did you know you can get multiple ticker symbols with one Internet access? For example, if you put ticker symbols in cells A2:A4, put this formula in cell B2 and copy down:

      =0+smfStrExtr(RCHGetWebData("http://www.google.com/ig/api?stock="&sfJoin($A$2:$A$4,"&stock="),"<symbol data="""&A2&"""",5000),"last data=""","""")

      On Mon, Apr 30, 2012 at 11:09 PM, B B <kokdari82@...> wrote:
      Thanks for that suggestion but one reason I like to stay away from yahoo to get prices is because pink sheet stock symbols require .pk at the end otherwise nothing shows up.

      That's why I much prefer something like Google.

      It also seems like Google has a api too which makes the basic information easier to get.

      http://www.google.com/ig/api? stock=aapl

      So for something like this where I want the last price which is defined by the following tag <last data="583.98"/> , how should I extract that?

      I did something like the following, but is there a more efficient way of using the getsmftagcontent function?

      Cell A1 has
      =smfGetTagContent("http://www. google.com/ig/api?stock=msft", "",12)

      Cell A2
      =FIND("/",A1)-1

      Cell A3
      =FIND("=",A1)+2

      Cell A4
      =MID(A1,A3,A2-A3)

      Cell A4 displays the correct last price.

    • Randy H
      I just uploaded a little template to give an example of extracting data from the Google Quotes API: smfGetTagContent-Google-API-Quotes.xls Like Yahoo, it can
      Message 2 of 8 , May 1, 2012
      View Source
      • 0 Attachment
        I just uploaded a little template to give an example of extracting data
        from the Google Quotes API:

        smfGetTagContent-Google-API-Quotes.xls

        Like Yahoo, it can be used to get multiple data items and multiple
        ticker symbols in a single Internet access. I'm not sure what the
        limitation is for the number of ticker symbols. I was able to do the
        first 202 companies of the S&P 500 before I got errors, but I suspect
        the limit is based on the length of the URL created by concatenating
        ticker symbols rather than on the actual number of ticker symbols.
      • B B
        Awesome. I didn t realize the functions could be used like this. Thank you very much.
        Message 3 of 8 , May 1, 2012
        View Source
        • 0 Attachment
          Awesome. I didn't realize the functions could be used like this.
          Thank you very much.

          --- In smf_addin@yahoogroups.com, "Randy H" <rharmelink@...> wrote:
          >
          > I just uploaded a little template to give an example of extracting data
          > from the Google Quotes API:
          >
          > smfGetTagContent-Google-API-Quotes.xls
          >
          > Like Yahoo, it can be used to get multiple data items and multiple
          > ticker symbols in a single Internet access. I'm not sure what the
          > limitation is for the number of ticker symbols. I was able to do the
          > first 202 companies of the S&P 500 before I got errors, but I suspect
          > the limit is based on the length of the URL created by concatenating
          > ticker symbols rather than on the actual number of ticker symbols.
          >
        • ken
          I see how the first extraction will get the last price for MSFT - how do I set it up to get the last price of whatever symbol I enter in A2? _____ From:
          Message 4 of 8 , May 1, 2012
          View Source
          • 0 Attachment

            I see how the first extraction will get the last price for MSFT – how do I set it up to get the last price of whatever symbol I enter in A2?

             


            From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy H
            Sent: Tuesday, May 01, 2012 5:04 AM
            To: smf_addin@yahoogroups.com
            Subject: [smf_addin] Getting quotes data from the Google API

             

             

            I wouldn't use the smfGetTagContent() that way, because you have no idea if the last data will always be the 12th item in the list.

            Also, I found I was doing the FIND/FIND/MID technique you're using so often, I wrote the smfStrExtr() function to do it it as a user-defined function.

            In any case, this is what I would use for the extraction:

            =0+smfStrExtr(RCHGetWebData("http://www.google.com/ig/api?stock=msft","last data=",50),"=""","""")


            You could extend that formula to any of the other data items by changing the "last data=" search string.

            Did you know you can get multiple ticker symbols with one Internet access? For example, if you put ticker symbols in cells A2:A4, put this formula in cell B2 and copy down:

            =0+smfStrExtr(RCHGetWebData("http://www.google.com/ig/api?stock="&sfJoin($A$2:$A$4,"&stock="),"<symbol data="""&A2&"""",5000),"last data=""","""")

            On Mon, Apr 30, 2012 at 11:09 PM, B B <kokdari82@...> wrote:

            Thanks for that suggestion but one reason I like to stay away from yahoo to get prices is because pink sheet stock symbols require .pk at the end otherwise nothing shows up.

            That's why I much prefer something like Google.

            It also seems like Google has a api too which makes the basic information easier to get.

            http://www.google.com/ig/api? stock=aapl

            So for something like this where I want the last price which is defined by the following tag <last data="583.98"/> , how should I extract that?

            I did something like the following, but is there a more efficient way of using the getsmftagcontent function?

            Cell A1 has
            =smfGetTagContent("http://www. google.com/ig/api?stock=msft", "",12)

            Cell A2
            =FIND("/",A1)-1

            Cell A3
            =FIND("=",A1)+2

            Cell A4
            =MID(A1,A3,A2-A3)

            Cell A4 displays the correct last price.

             

          • Randy Harmelink
            Take a look at the template. It uses smfGetTagContent() to get the tag for the particular ticker symbol, and then uses smfStrExtr() to extract the
            Message 5 of 8 , May 1, 2012
            View Source
            • 0 Attachment
              Take a look at the template. It uses smfGetTagContent() to get the <finance> tag for the particular ticker symbol, and then uses smfStrExtr() to extract the particular data item from that <finance> tag.

              On Tue, May 1, 2012 at 5:43 PM, ken <kab777@...> wrote:

              I see how the first extraction will get the last price for MSFT – how do I set it up to get the last price of whatever symbol I enter in A2?


            • carmine288
              Looks like this template uses RCH_Functions_Library.xla Should that be part of the RCH_Stock_Market_Functions.xla? Thanks, Carmine
              Message 6 of 8 , May 2, 2012
              View Source
              • 0 Attachment
                Looks like this template uses RCH_Functions_Library.xla
                Should that be part of the RCH_Stock_Market_Functions.xla?

                Thanks,
                Carmine

                --- In smf_addin@yahoogroups.com, "Randy H" <rharmelink@...> wrote:
                >
                > I just uploaded a little template to give an example of extracting data
                > from the Google Quotes API:
                >
                > smfGetTagContent-Google-API-Quotes.xls
                >
                > Like Yahoo, it can be used to get multiple data items and multiple
                > ticker symbols in a single Internet access. I'm not sure what the
                > limitation is for the number of ticker symbols. I was able to do the
                > first 202 companies of the S&P 500 before I got errors, but I suspect
                > the limit is based on the length of the URL created by concatenating
                > ticker symbols rather than on the actual number of ticker symbols.
                >
              • Randy Harmelink
                Which function or functions is from RCH_Functions_Library?
                Message 7 of 8 , May 2, 2012
                View Source
                • 0 Attachment
                  Which function or functions is from RCH_Functions_Library?

                  On Wed, May 2, 2012 at 4:41 PM, carmine288 <carmine.nicoletta@...> wrote:
                  Looks like this template uses RCH_Functions_Library.xla
                  Should that be part of the RCH_Stock_Market_Functions.xla?

                • carmine288
                  Don t know. Somehow the template what s to link to it.
                  Message 8 of 8 , May 2, 2012
                  View Source
                  • 0 Attachment
                    Don't know. Somehow the template what's to link to it.

                    --- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
                    >
                    > Which function or functions is from RCH_Functions_Library?
                    >
                    > On Wed, May 2, 2012 at 4:41 PM, carmine288 <carmine.nicoletta@...>wrote:
                    >
                    > > Looks like this template uses RCH_Functions_Library.xla
                    > > Should that be part of the RCH_Stock_Market_Functions.xla?
                    > >
                    >
                  Your message has been successfully submitted and would be delivered to recipients shortly.