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:

http://igoogledrive.blogspot.in/p/paid-projects.html

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,

iGoogleDrive