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.
Never looked into GoogleFinance options to be honest, not sure if this of any use: https://support.google.com/docs/answer/3093281?hl=en&rd=1 https://support.google.com/docs/table/25273?hl=en&ref_topic=3105411
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.
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.
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
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.
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
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.
