GoogleFinance() Symbols and Attributes?

Discussion in 'General Trading Discussion' started by baudwalk, Jun 20, 2016.

  1. baudwalk

    baudwalk Senior Investor

    Joined:
    May 2015
    Posts:
    1,459
    Likes Received:
    13
    I have developed a Google portfolio spreadsheet to track my holdings but I cannot ferret out the proper GoogleFinance() symbols to track Brent and WTI crude oil prices, a topic continually discussed daily on CBNC and Bloomberg. Searches turn up nothing useful in results dating back to 2010. The .DJI, .INX, and .IXIC produce data for Dow Jones, S&P 500 and the NASDAQ Composite, but crude oil stumps me. Can anyone help?

    Secondly, can any point to an updated list of GoogleFinance() functions? I note there is now a "name" attribute, returning the stock symbol's corporate name, that does not appear in the various help screens. For example, I'm wondering if there are attributes for a quarterly or annual dividend amount or yield (the extract workarounds out there are cumbersome at best and don't always work).

    Thanks in advance.
     
    Last edited by a moderator: Jul 8, 2016
  2. admin

    admin Administrator Staff Member

    Joined:
    Feb 2014
    Posts:
    9,592
    Likes Received:
    8
  3. baudwalk

    baudwalk Senior Investor

    Joined:
    May 2015
    Posts:
    1,459
    Likes Received:
    13
    Unfortunately those Google help pages haven't been updated in years. The "name" attribute doesn't appear.

    As far as the oil prices go, Yahoo data scraping is probably the easiest place to get the current pricing. For July futures, dump this formula into a spreadsheet.

    =IMPORTDATA("http://finance.yahoo.com/d/quotes.csv?s=CLN16.NYM+BZQ16.NYM&f=snpohgl1c1p2")

    The "&f=" switches can be found at http://www.jarloo.com/yahoo_finance/ but should you be cautious when using the scraping for stocks. Whereas pricing seems to be OK, things like the 50- and 200-moving averages, dividend dollars and yields, P/E and more seem suspect when comparing numbers to what I find on my brokerage account, Bloomberg, Morningstar and Stockcharts. Just engage a common sense filter.

    While GoogleFinance() occasionally returns a few odd values for some stocks, my intial impression is Yahoo is significantly more suspect. Whereas the former returns values, the latter returns text strings. String manipulation is doable, but just more annoying to do.

    I did find the Yahoo short ratio switch on Jaroo, something not available on GoogleFinance(). After validsting the numbers elsewhere, I dumped the scrape into my Watchlist spreadsheet.
     
  4. baudwalk

    baudwalk Senior Investor

    Joined:
    May 2015
    Posts:
    1,459
    Likes Received:
    13
    I know it has been 2.5 years since I posted the last message. Ignore the last message re Yahoo scraping. Yahoo doesn't provide data retrieval since being purchased by Yahoo.
     
  5. Chartman

    Chartman Senior Investor

    Joined:
    Sep 2017
    Posts:
    534
    Likes Received:
    8
    Hi @baudwalk

    I just tried that and it didnt work like you say lol

    Do you have any tips for data scraping for stocks and indices - I love a mess around with graphs, etc :)
     
  6. baudwalk

    baudwalk Senior Investor

    Joined:
    May 2015
    Posts:
    1,459
    Likes Received:
    13
    I just use Sheets with Googlefinance() functions. My portfolio sheet has about 2 dozen tabs. And I have other sheets to look at things like I just posted in Indicies. But the problem I have is I use a Samsung Galaxy Tab S3 and it is VERY difficult to insert a screenshot. I fumble around and sometimes it seems to work. The image icon demands a URL, but the screenshot is not on a website. Frustrating.
     
  7. Buyonthedips

    Buyonthedips Senior Investor

    Joined:
    Feb 2018
    Posts:
    268
    Likes Received:
    1
    Would you be able to attach an example of your spreadsheet to a post or would that be a step too far into your world of research :)
     
  8. baudwalk

    baudwalk Senior Investor

    Joined:
    May 2015
    Posts:
    1,459
    Likes Received:
    13
    Screenshot_20190107-154433_Sheets.jpg Here are some screens out of the package. I hope they are readable. This is the top sheet...
     
  9. baudwalk

    baudwalk Senior Investor

    Joined:
    May 2015
    Posts:
    1,459
    Likes Received:
    13
    And this looks at indicies Screenshot_20190107-154600_Sheets.jpg
     
  10. baudwalk

    baudwalk Senior Investor

    Joined:
    May 2015
    Posts:
    1,459
    Likes Received:
    13
    The big data sheet. 90% of the data is via the top "current" sheet. ROI on options and puts go into 2 more sheets. Altogether I have 23 tabs in this "master", 27 in my own version of this spreadsheet. The difference is a household budget/net worth sheet plus a few more graphs. My problem is it gets a bit sluggish on the tablet when loading or switching tabs.
    Screenshot_20190107-195755_Sheets.jpg
     

Share This Page