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

20131Re: [smf_addin] Google API with more variables

Expand Messages
  • Randy Harmelink
    Oct 23, 2012
      General extraction formula would be something like:

      =smfStrExtr(smfStrExtr(smfWord(RCHGetWebData($D$2,1),$C4,"}"),""""&G$3&"""",","),"""","""")

      ....where:

      $D$2 = the URL to retrieve the data
      $C4 = which row to extract (e.g. 1, 2, 3, ...)
      G$3 = which data item to extract (e.g. e, t, l_cur, ...)

      Or, to retrieve by ticker symbol:

      =smfStrExtr(smfStrExtr(smfStrExtr(RCHGetWebData($D$2,1),"""t"" : """&$C4&"""","}"),""""&G$3&"""",","),"""","""")

      ...where:

      $D$2 = the URL to retrieve the data
      $C4 = which ticker symbol to extract
      G$3 = which data item to extract (e.g. e, t, l_cur, ...)

      I found this list of data items:

        avvo    = Average volume (float with multiplier, like '3.54M')
        beta    = Beta (float)
        c       = Amount of change while open (float)
        ccol    = (unknown) (chars)
        cl      = Last perc. change
        cp      = Change perc. while open (float)
        e       = Exchange (text, like 'NASDAQ')
        ec      = After hours last change from close (float)
        eccol   = (unknown) (chars)
        ecp     = After hours last chage perc. from close (float)
        el      = After. hours last quote (float)
        el_cur  = (unknown) (float)
        elt     = After hours last quote time (unknown)
        eo      = Exchange Open (0 or 1)
        eps     = Earnings per share (float)
        fwpe    = Forward PE ratio (float)
        hi      = Price high (float)
        hi52    = 52 weeks high (float)
        id      = Company id (identifying number)
        l       = Last value while open (float)
        l_cur   = Last value at close (like 'l')
        lo      = Price low (float)
        lo52    = 52 weeks low (float)
        lt      = Last value date/time
        ltt     = Last trade time (Same as "lt" without the data)
        mc      = Market cap. (float with multiplier, like '123.45B')
        name    = Company name (text)
        op      = Open price (float)
        pe      = PE ratio (float)
        t       = Ticker (text)
        type    = Type (i.e. 'Company')
        vo      = Volume (float with multiplier, like '3.54M')

      On Tue, Oct 23, 2012 at 1:18 AM, boo1712 <boo1712@...> wrote:
      Hi guys,

      I discovered a new google API:
      http://www.google.com/finance/info?infotype=infoquoteall&q=BTU,JPM,AIG

      It has more variables such as beta, 52wk high, premarket quotes etc.

      However I do not know how to handle the numerous """.

      Previously we had a spreadsheet using another google API
      smfStrExtr(RCHGetWebData("http://www.google.com/ig/api?stock="&smfJoin($C$4:$C$33,"&stock="),"<symbol data="""&$C4&"""",5000),"<"&E$3&" data=""","""")

      It is useful and fast for getting multiple quotes.

      Can anyone help to provide the formula to the new API with more variables?

    • Show all 8 messages in this topic