Get Stock Market Prices in Google Sheets


Click image to enlarge

I described in a previous post how to import up-to-date currency exchange rates live from the web into your spreadsheet using the googleFinance() function.

That function can do much more, though; you can use it, for example, to import the current price of an individual stock, or of a whole index.

The figure above shows a small area of a spreadsheet, in which the highlighted field is grabbing the current price of the London FTSE100 index, by using:

=googleFinance("UKX", "price")

In the cell immediately below, we’ve grabbed yesterday’s closing price for comparison, by adding a different second parameter to the function call:

=googleFinance("UKX", "closeyest")

There are a number of different parameters you can use instead to grab various pieces of near-real-time data relating to your stocks. In general, you need to use the format:

=GoogleFinance(“Symbol”, “Attribute”)

Here are some other possible attributes:

price: the market price.
priceopen: the opening price for today.
high: the highest price the stock traded at today.
low: the lowest price the stock traded for today.
volume: number of shares traded today.
marketcap: the market capitalization.
tradetime: the last time the stock was traded.
pe: the Price-to-Earnings ratio.
eps: the earnings-per-share.
high52: the 52-week high.
low52: the 52-week low.
change: the change in the price since yesterday’s market close.
changepct: the percentage change in the price of this stock since yesterday’s close.
closeyest: yesterday’s closing price of this stock.
currency: the currency in which this stock is traded.

You can replace the ticker code UKX – which denotes the FTSE100 index – for the code of any other stock or index. For instance, to get Google’s opening share price for today, try:

=googlefinance("GOOG", "priceopen")

In this post we discuss how to get historical stock price data to check how your investments have performed.