Monday, July 9, 2007 at 8:12 PM
Almost every stock investor that we talk to during our user visits makes use of a spreadsheet to compile the data they have researched and many use their own formula to help decide when it is optimal to trade a stock. Whether they use Excel or a napkin, vast numbers of investors spend time every day keying in or writing down data. Wouldn't it be great if there was a spreadsheet that you could access from anywhere with an Internet connection -- a spreadsheet you could share with certain friends and automatically get the data updated to save you doing that work each day? For a while now, Google Spreadsheets has included Finance formulas so you can do all this and more.
Within Google Spreadsheets you can select the Formula tab and follow the more >> link on the top right.
Look at the Google section within the dialogue box and from here you can see the syntax for entering a Finance formula (see above). The details of all the stock and mutual fund data that you can pull into the spreadsheet can be found on this help page. To quote a section:
"Here are a couple examples using the formula:
* To insert the current volume of Google stock:
=GoogleFinance("GOOG"; "volume")
* To insert the current price of Google stock:
=GoogleFinance("GOOG") and =GoogleFinance("GOOG"; "price")
* Alternatively, the stock symbol and/or the attribute values can come from spreadsheet cells. For example, the function can be:
=GoogleFinance(A2, B1)
In this case, the attribute specified as a string in cell B1 would be returned for the stock symbol in cell A2."
Using this approach you can set up your portfolios and watchlists and have the data be updated continually. This is a screenshot of a portfolio I pulled together in a few minutes:
If you have your own formula that you would normally use to screen stocks then go ahead, it is easy to add them in. If you want to collaborate with anyone else, maybe a co-worker or investment club, it is easy to add them through the Share tab and you can show off your winning formulas with the world by publishing the spreadsheet and linking to it on your blog.
You can see how powerful this tool is, and so we are busy talking to folks who use it, and exploring ways in which we can improve it. If you have any ideas, please feel free to comment below.
24 comments:
I know you've heard this already, but giving currency exchange rates would be incredibly powerful too. Just a pretty please from one avid GoogleFinance feature user. Thanks!
I created a spreadsheet a while ago which pulled most of the data through the GoogleFinance function.
http://spreadsheets.google.com/pub?key=pP_M4m27kvOSboJDqe6TKbA
I think the feature is a great idea, although I would like to see it implemented into the Excel download of the spreadsheet.
Is there a way to add indices - like VIX (implied volatility of S&P options)?
Interest Rates would be useful too.
Neat concept but I'd use GS a lot more if it wasn't limited to only 250 GoogleFinance functions in one sheet...for now sticking with Excel and macros for downloading Yahoo quotes. Hopefully Google removes this limit
When Google Finance will work with Brazil? any chances?
An easy feature to add that would be very useful:
The ability to select a portfolio to make visible publicly, just like Google Spreadsheets allows you to share a document.
All the transactions, returns and such I have stored in my Google Finance portfolios can't be loaded to Google Spreadsheets.
This looks good. Can we lookup also companies sales, earnings etc this way ?
For example can I get GOOGFinance("GOOG", Sales , Q2 2006), or something for GOOG's sales in 2nd quarter of 2006? And so on for return on equity or debt ratio, etc for different quarters , for any company?
Also, as in figure below,
http://www.investors.com/images/editimg/corner071207.gif
can we get say the average annual sales growth over last 3 years ?
This will help us screen for good companies.
Finally, we would like everything in the above picture to be accessible via GoogleFinance, for example, as the picture shows : sales growth for last quarter, or earnings growth over last two quarters, etc
Also, as in figure below,
www.investors.com/images/editimg/corner071207.gif
can we get say the average annual sales growth over last 3 years ?
This will help us screen for good companies.
Finally, we would like everything in the above picture to be accessible via GoogleFinance, for example, as the picture shows : sales growth for last quarter, or earnings growth over last two quarters, etc
Then I can create my own Google Finance gadget.
Nice article I have published on Finance, using GoogleDocs. http://docs.google.com/Doc?id=dthcr25_10hd5knd
ie docs.google.com/Doc?id=dthcr25_10hd5knd
Winning stocks usually have annual EPS growth of at least 25% over the past three years.
How do I find such stocks using GoogleFinance ?
Please email me agrawalavinash1@ yahoo.com if I can answer anything.
How about adding a reference to a company's Dividend and sales growth rate?
please add a third parameter of "Date" to the function to accept a date such as 01/01/2007 or 01-JAN-07
It would be great if you could directly import the entire balance sheet from Google Finance to Google Spreadsheet.
I second a third parameter, date. If we build an application comparing a stock performance over time, we might need to get historical data by specifying a date. It is indeed powerful and I can see MBA students to create powerful financial models using these features.
Can I work offline with a spreadsheet if I have Google Gears?
Ability to reference a cell in another spreadsheet (my own or a friend's public spreadsheet)
this is fantastic!!!
Congratulations Google team!!!
How do you add stocks from a specific stock exchange?
For instance, when I use the ticker symbol RDSA, I get the information from Royal Dutch Shell on the London stock exchange. However, I would like to have the information from Shell in Amsterdam.
I would expect that using 'ams:rdsa' should do the trick; however, that doesn't seem to work.
In general I think Google Finance doesn't make it easy finding information from specific stock exchanges.
I have the same problem as LOER.
I can get feedback from listings with just the ticker code, but not for listings using Exchange:TickerCode...
Ie: NASDAQ:GOOG
Nick,
would you be able to please share the sample spreadsheet that you put together?
thanks.
DL
Please add parameter to this function called "Date" to accept a date such as 01/01/2007 or 01-JAN-07 and return price(or attribute) on the specified particular date..
It would be so great if the financial statements could automatically be viewed in google doc format.
Thanks
I figured out a way to automatically import the entire income, balance, and cash flow sheets from Google Finance directly into Google Spreadsheets. It is a two part process. The first part imports the data. The second part removes special characters from the bold html in the finance tables.
This method works by using the importhtml function.
Type this in your spreadsheet:
=ImportHtml("http://finance.google.com/finance?fstype=ii&q=NASDAQ:AAPL", "table",3). That will import the quarterly income statement for Apple. If you change the # to 4, it will import the annual income statement for Apple. Changing the # to 5 moves to the next balance sheet and so on.
Once you import the data, find some empty space on your sheet or create a new sheet and use the substitute command to remove the "*".
Enter =substitute(datasheet!A1, "*",""), where datasheet is the other data sheet and A1 is replaced with the first field of the data.
This will create an automatically refreshing spreadsheet of numerical financial data.
you have to add dividends in the attributes
To make google-spreadsheet a truly good fundamental analytical tool, attributes need to most of not all the items in the financial statements. Specifically, Long term debt, Short term debt, Total Equity, Common Equity, Tangible Equity, Sales, Operating Income, Depreciation, Amortization, Net Income, Dividends, etc.
This would be a good start
Another vote for dividends.
Post a Comment