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

Inconsistent Formatting in an Index

When indexing a document, you may find that some of your index entries aren't formatted the save as your other index ...

Discover More

Limiting Choices in a Cell

Want to limit what a person can enter into a particular cell? You can use Excel's data validation feature to help enforce ...

Discover More

Averaging Values for a Given Month and Year

Excel is often used to analyze data collected over time. In doing the analysis, you may want to only look at data ...

Discover More
More DriveTips

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 ...

Discover More

Easy Translations

If you work in an international environment, you may have a need to translate text from one language to another. Sheets ...

Discover More

Creating a Conditional Format

Conditional formatting is a powerful tool you can use to dynamically adjust the formatting in your worksheet. This tip ...

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. Maximum image size is 6Mpixels. 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 nine more than 0?

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