Adding Stock Information

by Allen Wyatt
(last updated November 28, 2013)

Spreadsheet programs are often use to track information of importance to a user. I've seen people use spreadsheets to organize their coin collections, their team rosters, and their recipe collection. Whatever your imagination and proclivities can come up with, you can probably track—in some fashion—in a spreadsheet.

One common thing that is tracked is stock information. If you have significant numbers of stocks, you may want to put together a "dashboard" that contains the current prices of those stocks and what that information does to your portfolio value. Sheets makes putting together such a dashboard easier than ever before.

Let's say, for example, that you want to display the current prices of Apple, Google, and Microsoft stocks. Simply put the following three formulas into three cells of a Sheets spreadsheet:

=GoogleFinance("AAPL")
=GoogleFinance("GOOG")
=GoogleFinance("MSFT")

In just a few seconds after entering each formula, you should see the then-current stock price for Apple, Google, and Microsoft. This magic is performed because the GoogleFinance function fetches information from the Google Finance service. The stock prices are delayed by 20 minutes (as is standard for most such free services in the world), but you no longer have to search for the data yourself—Sheets does it for you. (You'll also notice that if you use the GoogleFinance function anywhere within a sheet, Google displays a disclaimer about the delay at all times at the bottom of the sheet.)

Actually, the GoogleFinance function can retrieve a lot more than just stock prices. All you need to do is remember this syntax for the function:

=GoogleFinance("ticker", "attribute")

Just replace "ticker" with the stock symbol you want, enclosed in quotes, as was done with "AAPL" (Apple), "GOOG" (Google), and "MSFT" (Microsoft). The "attribute" placeholder is where you specify exactly what tidbit of information you want retrieved about that stock. This placeholder must, as well, be enclosed within quotes. The default attribute is, as you might have surmised, the stock's price. There are many other attributes you could use, though:

Attribute Meaning
price market price
priceopen today's opening price
high today's highest price
low today's lowest price
volume today's number of shares traded
closeyest yesterday's closing price
change price change since yesterday's close
changepct percentage price change since yesterday's close
marketcap market cap for the stock
tradetime last time the stock was traded
volumeavg average trading volume
pe price-to-earnings ratio
eps earnings per share
high52 52-week high
low52 52-week low
beta beta value of the stock
shares number of outstanding shares
currency trading currency for the stock
datadelay delay in the data presented

If you have a large portfolio, you should realize that Sheets does have a limit on the number of GoogleFinance functions that can appear within a spreadsheet. The max is currently set at 250, which may sound like a lot. However, if you are using the function to grab five pieces of data about a stock, that means you can only get full information on a total of 50 stocks in a sheet.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Different Layouts for Footnotes

If you want to have footnotes appear in a different number of columns than what your text appears in, you may be out of luck. ...

Discover More

Full-Screen Mode in Word 2007

Full-screen mode in Word 2007 doesn't work exactly the same as it did in earlier versions of Word. Don't fret; the ...

Discover More

Understanding Custom Chart Templates

Excel allows you to create custom chart formats that go beyond the standard formats provided in the program. These custom ...

Discover More
More DriveTips

Copying a Spreadsheet

There are times when you don't want to mess up a spreadsheet, but you want to try out some changes. This is when making a ...

Discover More

Adjusting Border Color

Want to make a particular cell or range of cells stand out? One way to do it is by changing the border color of the cells, as ...

Discover More

Creating a Named Range

Named ranges can be a great boon in creating easily understandable formulas. Here's what they are an how to define them.

Discover More
Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is six less than 6?

There are currently no comments for this tip. (Be the first to leave your comment—just use the simple form above!)