The GOOGLEFINANCE function in Google Sheets utilizes Google's finance APIs to let you import real time and historical data on financial securities and currencies (including cryptocurrencies). This will let you build investment models, keep track of your portfolio, and more, though it is not recommended for making real time professional investment decisions as the real time stock and currency data only refreshes every 20 minutes or so.
=GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date/num_days], [interval])Ticker is the only required argument of the GOOGLEFINANCE function and is the symbol for the security you want to analyze. If you only include the ticker argument in the function, GOOGLEFINANCE will simply return the current price of the specified stock or currency, which is updated roughly every 20 minutes. The format of the ticker argument for stocks and currencies is as follows:
"exchange_symbol:stock_symbol"
Where the exchange symbol is the exchange the stock trades on. The exchange symbol is NYSE for the New York Stock Exchange and is NASDAQ for the Nasdaq exchange. For example, Salesforce stock (CRM) trades on the NYSE, so the ticker would be "NYSE:CRM" and Google stock (GOOG) trades on the Nasdaq so the ticker would be "NASDAQ:GOOG".
"CURRENCY:SourceTarget"
The ticker for currency begins with CURRENCY: and is followed by the three letter code for the currency whose price you want (the source currency) followed by the currency you want to convert to (the target currency). Note that there are no spaces.
For example, to view the price of Euros in US Dollars, you would use the ticker "CURRENCY:EURUSD". To view the price of a Mexican Peso in British Pounds, you would use the ticker "CURRENCY:MXNGBP". See the bottom of this article for a full list of currency codes by country.
The GOOGLEFINANCE function also works with cryptocurrency. See below for details on using GOOGLEFINANCE with cryptocurrencies.
The official Google Sheets documentation recommends including the exchange symbol of the ticker you wish to analyze, though it is not required. This means that while you can simply write =GOOGLEFINANCE("CRM") to get real time stock data on Salesforce stock it will work, but it's recommended that you instead write =GOOGLEFINANCE("NYSE:CRM") because Salesforce stock trades on the New York Stock Exchange. Conversely, Google stock trades on the Nasdaq exchange, so you would write =GOOGLEFINANCE("NASDAQ:GOOG") to get real time price data on Google stock, though =GOOGLEFINANCE("GOOG") also works just fine.
Why does Google recommend this? There are rare cases where different exchanges may have the same symbols for different securities, and if you exclude the exchange from the ticker, the GOOGLEFINANCE function will simply make its best guess about which one you want.
The two big stock exchanges in the United States are the New York Stock Exchange (NYSE) and Nasdaq. Not sure which exchange your stock trades on? If the symbol has four letters, it likely trades on the Nasdaq exchange. So Salesforce's stock symbol, CRM (three letters) trades on the NYSE, and Google's stock symbol, GOOG (four letters) trades on the Nasdaq.
The attribute is the second argument of the GOOGLEFINANCE function and specifies what information you wish to return about the ticker. It is optional, and if left blank will default to price. Keep in mind that specifying any of the date parameters tells GOOGLEFINANCE to return historical data rather than real time data, and that there are different attribute options for historical and real time data.
When viewing real time stock data, you can pick from the following attributes. The attribute specifies what information you want to view about the stock, whether it's the stock's price, market capitalization, beta, earnings per share, or anything else from the long list below:
| "price" | the current price of the security, updated roughly every 20 minutes. This is the default if you omit the attribute argument. |
| "priceopen" | the price of the security at the time the market opened that day |
| "high" | the current day's highest price |
| "low" | the current day's lowest price |
| "volume" | the trading volume of the current day |
| "marketcap" | the current market capitalization |
| "tradetime" | the time of the last trade |
| "datadelay" | how delayed the data is |
| "volumeavg" | average trading volume of the current day |
| "pe" | the current price to earnings ratio |
| "eps" | earnings per share |
| "high52" | highest price in the last 52 weeks |
| "low52" | lowest price in the last 52 weeks |
| "change" | the price change in price since the last day's close of trading |
| "beta" | the beta of the stock |
| "changepct" | the percent change in price since the last day's close of trading |
| "closeyest" | yesterday's closing price |
| "shares" | the number of shares outstanding |
| "currency" | the currency that the stock trades in |
When you view historical financial data in Google Sheets, the GOOGLEFINANCE function has a more limited range of attributes from which to choose.
| "open" | the stock's price at market open on the specified dates |
| "close" | the stock's price at market close on the specified dates |
| "high" | the stock's highest price on the specified dates |
| "low" | the stock's lowest price on the specified dates |
| "volume" | the volume on the specified dates |
| "all" | will return data for all of the above metrics |
Note that when viewing currency rather than a stock, the only attribute available for historical data is "close" which is essentially the exchange rate at the end of the day.
The start_date argument is optional and, if included, specifies the start date of the date range for which you want historical data. Remember, if you supply any of the date arguments to the GOOGLEFINANCE function, it will return historical data. Otherwise, it will return real time data. If you include the start_date but not the end_date/num_days argument, then GOOGLEFINANCE will return only historical data for the start date.
The end_date/num_days argument specifies either the end date or the length, in days, of the date range of historical data which you want GOOGLEFINANCE to return. This argument can either be a date in the format DATE(2021,12,31) specifying the end date of the date range, or can be a number specifying the length of the date range in days.
When requesting a historical date range of data with the GOOGLEFINANCE function you can specify daily data or weekly data. This argument can either be "DAILY" or "WEEKLY" or, alternatively, 1 or 7. No other numerical values are allowed.
Now that we've explained the syntax of the GOOGLEFINANCE function, let's take a look at some examples of how you can begin using this function to analyze financial data in Google Sheets.
=GOOGLEFINANCE("NASDAQ:INTC")The formula above is the simplest version of the GOOGLEFINANCE function because it includes only the ticker argument. It will occupy a single cell in the spreadsheet and will simply return the current stock price of Intel stock, updated every 20 minutes.
=GOOGLEFINANCE("CURRENCY:JPYUSD")The formula above also only includes the ticker argument, and this time simply returns the current price (exchange rate) of a Japanese Yen in United States Dollars.
=GOOGLEFINANCE("NASDAQ:INTC", "pe")In the formula above we get a bit more complex by introducing the attribute argument. When omitted, the attribute defaults to price. But because we included "pe" as the attribute argument, this formula now returns the current PE (price to earnings) ratio for Intel stock.
=GOOGLEFINANCE("NASDAQ:INTC", "open", DATE(2020, 10, 8))In this formula, note that because I included the start date argument, we're now dealing with historical data so I had to change the attribute argument to an attribute that is compatible with historical data. Because there is no end_date/num_days argument it only shows historical data (stock price at market open) for the start date. Also, because we're now looking at historical data, the formula now returns two columns of data (date and corresponding open value), rather than occupying a single cell in Google Sheets.
=GOOGLEFINANCE("NASDAQ:INTC", "open", DATE(2020, 10, 8), 30)In adding the end_date/num_days argument to the formula, GOOGLEFINANCE now returns 30 days of historical data (Intel stock price at market open) from the start date. Rather than the number 30, we could also simply insert another date value to specify the end date of the historical range we want.
=GOOGLEFINANCE("CURRENCY:MXNUSD", "close", DATE(2022, 5, 1), DATE(2022, 5, 20))With this formula, we are asking GOOGLEFINANCE to return the historical price of Mexican Pesos in US Dollars for each day starting from May 1, 2022 until May 20, 2022.
=GOOGLEFINANCE("NASDAQ:INTC", "open", DATE(2020, 10, 8), 30, "WEEKLY")Finally we add the interval argument to the GOOGLEFINANCE function to specify that we want weekly values within our historical range of data. This limits the number of rows returned by the GOOGLEFINANCE function to six rows of data - one for the beginning of each week that falls within our specified date range.
The GOOGLEFINANCE function, in addition to returning exchange rate information on conventional currencies, also returns pricing information on cryptocurrencies. For example, to view the value of Bitcoin in US Dollars, you could use the following formula:
=GOOGLEFINANCE("CURRENCY:BTCUSD")Google Sheets provides exchange rate data most of the major cryptocurrencies through the GOOGLEFINANCE function including the following:
| CRYPTOCURRENCY | CURRENCY CODE |
| Cardano | ADA |
| Bitcoin Cash | BCH |
| Binance Coin | BNB |
| Bitcoin | BTC |
| Decred | DCR |
| DigiByte | DGB |
| EOS | EOS |
| Ethereum | ETH |
| Litecoin | LTC |
| Neo | NEO |
| TRON | TRX |
| Monero | XMR |
| Ripple | XRP |
| 0x | ZRX |
Note that because Google Sheets and the GOOGLEFINANCE function rely on the underlying Google Finance APIs, it expects currency codes to conform to the standard three-letter code format. For this reason, the GOOGLEFINANCE function does not work with cryptocurrencies with four-letter codes, such as Dogecoin (DOGE).
Below is a list of the three-letter currency codes to use with the GOOGLEFINANCE function when calculating exchange rates between currencies in Google Sheets.
| COUNTRY | CURRENCY CODE | CURRENCY |
| Albania | ALL | lek |
| Algeria | DZD | dinar |
| Angola | AOA | kwanza |
| Argentina | ARS | peso |
| Armenia | AMD | dram |
| Australia | AUD | dollar |
| Austria | EUR | euro |
| Azerbaijan | AZN | manat |
| Bahrain | BHD | dinar |
| Barbados | BBD | dollar |
| Belarus | BYN | rouble |
| Belgium | EUR | euro |
| Bermuda | BMD | dollar |
| Bolivia | BOB | boliviano |
| Bosnia and Herzegovina | BAM | konvertibilna marka |
| Botswana | BWP | pula |
| Brazil | BRL | real |
| Bulgaria | BGN | lev |
| Cabo Verde | CVE | escudo |
| Cambodia | KHR | riel |
| Cameroon, Republic of | XAF | CFA franc BEAC |
| Canada | CAD | dollar |
| Cayman Islands | KYD | dollar |
| Chad | XAF | CFA franc BEAC |
| Chile | CLP | peso |
| China, People’s Republic of | CNY | yuan |
| Colombia | COP | peso |
| Congo, Democratic Republic of the | CDF | franc |
| Congo, Republic of | XAF | CFA franc BEAC |
| Costa Rica | CRC | colon |
| Croatia | HRK | kuna |
| Cyprus | EUR | euro |
| Czech Republic | CZK | koruna |
| Denmark | DKK | kroner |
| Dominican Republic | DOP | peso |
| Ecuador | USD | dollar |
| Egypt | EGP | pound |
| El Salvador | USD | dollar |
| Equatorial Guinea | XAF | CFA franc BEAC |
| Estonia | EUR | euro |
| Fiji | FJD | dollar |
| Finland | EUR | euro |
| France | EUR | euro |
| Gabon | XAF | CFA franc BEAC |
| Georgia | GEL | lari |
| Germany | EUR | euro |
| Ghana | GHS | cedi |
| Gibraltar | GIP | pound |
| Greece | EUR | euro |
| Greenland | DKK | Danish krone |
| Guatemala | GTQ | quetzal |
| Guernsey, Channel Islands | GBP | British pound sterling |
| Guyana | GYD | dollar |
| Honduras | HNL | lempira |
| Hong Kong SAR | HKD | dollar |
| Hungary | HUF | forint |
| Iceland | ISK | krona |
| India | INR | rupee |
| Indonesia | IDR | rupiah |
| Iraq | IQD | dinar |
| Ireland | EUR | euro |
| Isle of Man | IMP | pound |
| Israel | ILS | shekel |
| Italy | EUR | euro |
| Ivory Coast (Cote d'Ivoire) | XOF | CFA franc BCEAO |
| Jamaica | JMD | dollar |
| Japan | JPY | yen |
| Jersey, Channel Islands | GBP | British pound sterling |
| Jordan | JOD | dinar |
| Kazakhstan | KZT | tenge |
| Kenya | KES | shilling |
| Korea, Republic of | KRW | won |
| Kosovo | EUR | euro |
| Kuwait | KWD | dinar |
| Kyrgyzstan | KGS | som |
| Lao PDR | LAK | kip |
| Latvia | EUR | euro |
| Lebanon | LBP | pound |
| Libya | LYD | dinar |
| Liechtenstein | CHF | franc |
| Lithuania | EUR | euro |
| Luxembourg | EUR | euro |
| Macau SAR | MOP | pataca |
| Madagascar | MGA | ariary |
| Malawi | MWK | kwacha |
| Malaysia | MYR | ringgit |
| Maldives | MVR | rufiyaa |
| Malta | EUR | euro |
| Mauritania | MRU | ouguiya |
| Mauritius | MUR | rupee |
| Mexico | MXN | peso |
| Moldova | MDL | leu |
| Mongolia | MNT | tugrik |
| Montenegro | EUR | euro |
| Morocco | MAD | dirham |
| Mozambique | MZN | metical |
| Myanmar | MMK | kyat |
| Namibia, Republic of | NAD | dollar |
| Netherlands | EUR | euro |
| New Zealand | NZD | dollar |
| Nicaragua | NIO | cordoba oro |
| Nigeria | NGN | naira |
| North Macedonia | MKD | denar |
| Norway | NOK | kroner |
| Oman | OMR | rial |
| Pakistan | PKR | rupee |
| Palestinian territories | ILS, USD, and JOD | shekel, dollar, and dinar |
| Panama | PAB and USD | balboa and dollar |
| Papua New Guinea | PGK | kina |
| Paraguay | PYG | guarani |
| Peru | PEN | nuevo sol |
| Philippines | PHP | peso |
| Poland | PLN | zloty |
| Portugal | EUR | euro |
| Puerto Rico | USD | dollar |
| Qatar | QAR | riyal |
| Romania | RON | leu |
| Russian Federation | RUB | ruble |
| Rwanda | RWF | franc |
| Saint Lucia | XCD | dollar |
| Saudi Arabia | SAR | riyal |
| Senegal | XOF | CFA franc BCEAO |
| Serbia | RSD | dinar |
| Singapore | SGD | dollar |
| Slovak Republic | EUR | euro |
| Slovenia | EUR | euro |
| South Africa | ZAR | rand |
| Spain | EUR | euro |
| Sri Lanka | LKR | rupee |
| Swaziland | SZL | lilangeni |
| Sweden | SEK | krona |
| Switzerland | CHF | franc |
| Taiwan | TWD | dollar |
| Tajikistan | TJS | somoni |
| Tanzania | TZS | shilling |
| Thailand | THB | baht |
| Timor-Leste | USD | US dollar |
| Trinidad And Tobago | TTD | dollar |
| Tunisia | TND | dinar |
| Turkey | TRY | lira |
| Turkmenistan | TMT | manat |
| Uganda | UGX | shilling |
| Ukraine | UAH | hryvnia |
| United Arab Emirates | AED | dirham |
| United Kingdom | GBP | sterling |
| United States | USD | dollar |
| Uruguay | UYU | peso |
| Uzbekistan, Republic of | UZS | sum |
| Venezuela | VEF | bolivar |
| Vietnam | VND | dong |
| Zambia | ZMW | kwacha |
| Zimbabwe | ZWD | dollar |
To fully commit this function to memory you need to get some hands on experience. Try some practice problems with the GOOGLEFINANCE function in our interactive Google Sheets Tutorial now!