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

Skipping Hidden Rows in a Macro

As your macro processes information in a worksheet, you may want to make sure that it skips over rows that are hidden. The ...

Discover More

Counting All Graphics

Need to know how many graphics a document contains? Getting at the true number may take a little more work than it first ...

Discover More

Using Early Dates

Excel is brilliant at handling dates—as long as they aren't dates earlier than the base date used by the program. If ...

Discover More
More DriveTips

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

Filtering Data

When you get a lot of data in a spreadsheet, it can be hard to zero in on the data you really need to work with. That's where ...

Discover More

Hiding a Chart's Legend

When you create a chart, Sheets helpfully adds a legend to better explain the data that is in the chart. You may not want a ...

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 8 - 3?

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