Monday, October 8, 2012

Extracting Net Tangible Assets and other financial figures in Google Spreadsheet

Question:

Google Spreadsheets supports a function named GoogleFinance to retrieve price and volume related attributes for stock symbols.   Does anyone know of either a Google-supported - or a third party tool - that would return attributes from the financial statement looked up by symbol?   For example, the function might retrieve the tangible net assets from the latest quarter balance sheet.

Having such a function would make both Google Finance and Google Spreadsheets ENORMOUSLY more valuable, since it would largely automate much of the drudgery of manually updating data from the latest quarter's reports, when tracking a large number of situations.   Are there any options either delivered or announced for future delivery?


Solution:

You can retrieve the tangible net assets from the latest quarter balance sheet and many other such information using several of functions available in Google Spreadsheet, like GoogleFinance, ImportHtml, ImportXML, ImportData, ImportFeed and many such others.

Let us see now the example of how to retrieve the tangible net assets for "GOOG" from the latest quarter balance sheet.

To retrieve the tangible net assets for stocks we will here use data from "finance.yahoo.com" using ImportHtml formula.

Look at the following Screenshot of "http://finance.yahoo.com/q/bs?s=GOOG":




The following formula will get you the whole table into your spreadsheet, put it in a new blank sheet in Cell "A1":
=ImportHtml("http://finance.yahoo.com/q/bs?s=GOOG";"table";0)

Look at the screenshot of spreadsheet displaying the above table using the above formula:



Now to extract only the tangible net assets for "GOOG" from the latest quarter balance sheet use the following formula:

=QUERY(ImportHtml("http://finance.yahoo.com/q/bs?s=GOOG";"table";0);"select Col2 where Col1 = '*Net Tangible Assets*'";0)

This will display you the result as:
*46,739,000 *


Now, to remove extra spaces and the star from the above displayed answer use the following formula:

=LEFT(RIGHT(QUERY(ImportHtml("http://finance.yahoo.com/q/bs?s=GOOG";"table";0);"select Col2 where Col1 = '*Net Tangible Assets*'";0);len(QUERY(ImportHtml("http://finance.yahoo.com/q/bs?s=GOOG";"table";0);"select Col2 where Col1 = '*Net Tangible Assets*'";0))-1);len(QUERY(ImportHtml("http://finance.yahoo.com/q/bs?s=GOOG";"table";0);"select Col2 where Col1 = '*Net Tangible Assets*'";0))-4)



I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private or confidential projects:

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,
Kishan,

3 comments:

  1. Just wondering, say for example I used the code outlined in the beginning "=ImportHtml("http://finance.yahoo.com/q/bs?s=GOOG";"table";0)" is there anyway that instead of having GOOG written in the link I can reference it to a cell for the PART of the link.

    So say I have a spread sheet where all I want to do is calculate the Net Tangible Assets for several different stocks, I have all the stock tickers written in one column. Can i make that link, in the column next to it, be referenced to the ticker in the previous column.

    Your help is appreciated. Thanks

    ReplyDelete
  2. I was able to modify your code a little bit just to reference a cell within a hyperlink,
    =LEFT(RIGHT(QUERY(ImportHtml("http://finance.yahoo.com/q/bs?s="&A1&"";"table";0);"select Col2 where Col1 = '*Total Assets*'";0);len(QUERY(ImportHtml("http://finance.yahoo.com/q/bs?s="&A1&"";"table";0);"select Col2 where Col1 = '*Total Assets*'";0))-1);len(QUERY(ImportHtml("http://finance.yahoo.com/q/bs?s="&A1&"";"table";0);"select Col2 where Col1 = '*Total Assets*'";0))-4)


    But now I am stuck at fetching data from the other columns (Previous years)

    ReplyDelete
  3. yahoo does not seem to be providing balance sheet data anymore for GOOG per your http://finance.yahoo.com/q/bs?s=GOOG

    ReplyDelete