How to get stock prices from Google Finance in Google Sheets

Google Sheets has a very convenient feature to grab stock prices and other details from its Google Finance service and display in spreadsheets.

Photo by Oren Elbaz on Unsplash

It not only lets you get the latest real-time stock prices but also gets historical prices. This feature can be an interesting add-on to your financial spreadsheets or to build a tracker for your holdings or just to build a simple watchlist.

Get the real-time price of a stock

The simplest way to get the real-time stock prices is to use the =GOOGLEFINANCE() function with just the stock ticker or symbol using the following syntax.

=GOOGLEFINANCE(ticker)

For example, writing the following in a cell would display the current stock price of Apple.

=GOOGLEFINANCE("AAPL")

In Google Sheets, it looks like this:

The =GOOGLEFINANCE() function also accepts several attributes that can be used to get more information about a stock including the name, current and historical prices.

We will get into more examples of using attributes later, but for an example, using the "name" attribute like =GOOGLEFINANCE("AAPL", "name") returns the name of the company for the stock ticker.

Creating a watchlist

You can easily create a list of stocks that you want to monitor using the real-time attributes that are available by default with the =GOOGLEFINANCE() function. To use an attribute just add the attribute as a parameter in the function. For example, to get the market capitalization, use something like:

=GOOGLEFINANCE("AAPL", "marketcap")

In Google Sheets, it looks like this:

Here are some attributes that can be useful for building a watchlist. For the latest and full list of attributes, refer to this page on Google Support.

• "price" - Real-time price quote, delayed by up to 20 minutes. This is a default attribute.
• "priceopen" - The price as of market open.
• "high" - The current day's high price.
• "low" - The current day's low price.
• "volume" - The current day's trading volume.
• "marketcap" - The market capitalization of the stock.
• "tradetime" - The time of the last trade.
• "datadelay" - How far delayed the real-time data is.
• "volumeavg" - The average daily trading volume.
• "pe" - The price/earnings ratio.
• "eps" - The earnings per share.
• "high52" - The 52-week high price.
• "low52" - The 52-week low price.
• "change" - The price change since the previous trading day's close.
• "beta" - The beta value.
• "changepct" - The percentage change in price since the previous trading day's close.
• "closeyest" - The previous day's closing price.
• "shares" - The number of outstanding shares.
• "currency" - The currency in which the security is priced.

Using these attributes, a watchlist can be built that looks like this:

Ticker or symbol for a particular stock

The easiest way to get the ticker for a particular stock is to perform a search in your favorite search engine with the name of the company followed by the word "stock".

For example, in Google, search with the keywords "Apple stock" to get the result shown below where the stock ticker is listed as NASDAQ: AAPL.

Now use the symbol in a Google Sheets formula like =GOOGLEFINANCE("NASDAQ:GOOG") to get the current price. Notice that we have removed any blank spaces in the ticker name.

Notice that the symbol contains two parts which are separated by a colon. The part on the left of the colon is the designated exchange code and the part on the right is the symbol or ticker. For US stocks, Google Sheets automatically grabs the prices if you just use the ticker like =GOOGLEFINANCE("AAPL") but for international stocks it is best to specify the designated exchange code along with the ticker with no space in between. To get accurate results and avoid discrepancies it is recommended to always use the designated exchange code along with the ticker like =GOOGLEFINANCE("NASDAQ:AAPL").

To get the ticker of Tata Steel which is listed in the National Stock Exchange in India, a search result would return something like what is shown below.

Now use the symbol in a Google Sheets formula like =GOOGLEFINANCE("NSE:TATASTEEL") to get the current price.

A similar example is the Tessenderlo Group which is listed in the Brussels Stock Exchange, for which you can use =GOOGLEFINANCE("EBR:TESB".

Historical stock price

The historical price of stocks can aid in the analysis for choosing the right pick. The =GOOGLEFINANCE() function can also be used to get this historical data by using these attributes.

• "open" - The opening price for the specified date(s).
• "close" - The closing price for the specified date(s).
• "high" - The high price for the specified date(s).
• "low" - The low price for the specified date(s).
• "volume" - The volume for the specified date(s).
• "all" - All of the above.

To get the list of prices for a stock from a start date to an end date use the following syntax:

=GOOGLEFINANCE(ticker,[attribute],[start date],[end date | number of days],"DAILY")

For example, the following formula gets the daily closing price of the stock between two dates:

=GOOGLEFINANCE("NASDAQ:AAPL","price",DATE(2020,1,1),DATE(2020,1,14),"DAILY")

The following formula can be used to get the closing price of a stock for 8 trading days starting from a particular date:

=GOOGLEFINANCE("NASDAQ:AAPL","price",DATE(2020,1,1),8,"DAILY")

This formula can be used to get the closing price on trading days since the last 30 days:

=GOOGLEFINANCE("NASDAQ:AAPL","price", TODAY()-30), TODAY(),"DAILY")

The following formula will get all the historical attributes of a stock for 8 trading days starting from a particular date:

=GOOGLEFINANCE("NASDAQ:AAPL", "all", DATE(2020,1,1),8,"DAILY")

Charting Historical Prices using SPARKLINE

The =SPARKLINE() function can be used in combination to historical stock prices from =GOOGLEFINANCE() to display a chart in Google Sheets. The following formula will display a chart plotting the price trend over the last 90 days for the Apple stock.

=SPARKLINE(GOOGLEFINANCE("NASDAQ:AAPL", "price",TODAY()-90, TODAY()))

Sparklines can be used to enhance your watchlist to provide a miniature visual on how the prices are trending.

Mutual Fund Prices

The =GOOGLEFINANCE() function can also be used to grab prices of mutual funds. There are several attributes to provide this functionality.

• "closeyest" - The previous day's closing price.
• "date" - The date at which the net asset value was reported.
• "returnytd" - The year-to-date return.
• "netassets" - The net assets.
• "change" - The change in the most recently reported net asset value and the one immediately prior.
• "changepct" - The percentage change in the net asset value.
• "yieldpct" - The distribution yield, the sum of the prior 12 months' income distributions (stock dividends and fixed income interest payments) and net asset value gains divided by the previous month's net asset value number.
• "returnday" - One-day total return.
• "return1" - One-week total return.
• "return4" - Four-week total return.
• "return13" - Thirteen-week total return.
• "return52" - Fifty-two-week (annual) total return.
• "return156" - 156-week (3-year) total return.
• "return260" - 260-week (5-year) total return.
• "incomedividend" - The amount of the most recent cash distribution.
• "incomedividenddate" - The date of the most recent cash distribution.
• "capitalgain" - The amount of the most recent capital gain distribution.
• "morningstarrating" - The Morningstar "star" rating.
• "expenseratio" - The fund's expense ratio.

For example, to get the last closing price of the Schwab S&P 500 Index Fund, use something like:

=GOOGLEFINANCE("MUTF:SWPPX", "closeyest")

A combination of these attributes can be used to create a watchlist that looks like this.

Cryptocurrency Prices

There are limited ways that you can use the =GOOGLEFINANCE() function to grab real-time prices of some cryptocurrencies. At the time of this writing, it works with Bitcoin and Ethereum. To do this use the cryptocurrency symbol that you see in the URL as shown below.

From the URL shown in the example above, you can take the BTC-USD ticker and use it directly like this in a formula.

=GOOGLEFINANCE("BTC-USD")

This will pull the price and display in a cell in Google Sheets.

Conclusion

To sum up, monitoring stocks, mutual funds and cryptocurrency within Google Sheets is a potent method for analyze your holdings. It is user-friendly, adaptable, and doesn't cost a dime. Consequently, you can promptly access both historical and real-time stock information on your spreadsheet. I hope this article holds some helpful information for you.

Here is this posted in LinkedIn.

TD Ameritrade trading accounts moving to Schwab

A new email to TD Ameritrade users state that all stock brokerage accounts will transition to Schwab. Charles Schwab had acquired TD Ameritrade in an all-stock deal worth $26 billion in November 2019. The deal was completed in October 2020, creating an online brokerage giant with more than $6 trillion in client assets and nearly 28 million brokerage accounts.

Here is the text from the email.

Your move from TD Ameritrade to Schwab.

Dear TD Ameritrade clients,

When Charles Schwab acquired TD Ameritrade in 2019, our aim was to create something special for clients of both firms—best-in-class strength and stability, a wider array of products and services available, industry-leading low costs, and a larger team of professionals with a shared passion for helping clients achieve their goals.

We're nearing the point where two great firms become one and TD Ameritrade clients become Schwab clients. We are honored to have the privilege to serve you, and we want to make sure you know what to expect with the transition and the benefits you'll receive as a Schwab client.

What to know about your move.

Please know that in most cases there will be very little for you to do, other than to set up new login credentials. We'll take care of the rest.

We plan to move your account in one of several groups beginning in 2023. To help avoid investing or trading disruptions, your transition will happen over a weekend. We'll let you know your transition date about three months before your move.

What you rely on is moving with you.

thinkorswim®, the award-winning trading platform, is here to stay, and will move to Schwab later in 2023. Your service teams and Financial Consultants will also be there to support you after the transition, and you'll continue to enjoy many of the features, tools, and education you've come to rely on with TD Ameritrade.

Plus, you can look forward to the low costs you're used to. In fact, by leveraging the combined strength of both companies, we'll be able to offer you even more pricing improvements.

What to know about the benefits you'll experience from being at Schwab.

They've already begun.

Today, TD Ameritrade clients already benefit. Since the acquisition, TD Ameritrade has significantly enhanced client service response times, lowered mutual fund transaction fees, eliminated some account service fees, launched a Satisfaction Guarantee, and enabled access to some Schwab products not previously available at TD Ameritrade.

Once you transition to Schwab, you'll benefit from these commitments we make to you:

Low pricing. Schwab has a long history of lowering the cost of investing for everyone. It is a defining part of who we are and why we're proud that today we make the lowest revenue per dollar of client assets of any publicly traded major investment firm. That commitment to low costs will continue. In fact, when you transition, you have our commitment that the fee schedule for online trading commissions and fees at Schwab will be the same or lower for all transactions.

Exceptional service. We're committed to maintaining industry-leading response times from Schwab's experienced representatives, which will include the TD Ameritrade service teams and Financial Consultants who will be moving to Schwab with you, as well as specialists in all areas of investing and trading. Plus, you'll have access to more than 400 physical branches across the United States, London, Singapore, and Hong Kong.

World-class trading and investing experiences. Whether you use our industry-leading web, app, or thinkorswim suite of platforms, you can count on an innovative, easy-to-use experience with high-quality execution. Plus, you'll have comprehensive education and market insights for all types of traders and investors.

A complete range of wealth management and lending solutions. Count on a range of portfolio and wealth management solutions, from automated investing to comprehensive wealth management tailored to your unique needs. You can also rely on competitive solutions for borrowing against your assets as well as discounts based on your relationship at Schwab.

A firm that stands behind its word. Earning your trust is our highest ambition. We also know actions speak louder than words, which is why we back our words with both the Schwab Satisfaction and Security Guarantees.

In summary, you'll have much of what you loved at TD Ameritrade—combined with much more from Schwab.

What's next.

Right now, there's nothing you need to do. Please keep pursuing your trading and investing goals using TD Ameritrade platforms and solutions. We'll provide updates and contact you about three months before your account transitions with all the information you'll need. Should you have any questions or want more details now, please visit our Client Information Hub.

Thank you for allowing us to be a part of your financial life. The employees at Schwab and TD Ameritrade are extremely proud of what we are building, and we look forward to continuing to serve you.

Sincerely,

Jonathan Craig

Head of Investor Services

Update 2023-08-02:

Creation of Schwab accounts by TD Ameritrade users began on August 1. From September 1, the TD Ameritrade user accounts will be frozen and migrated to the Schwab system. On September 5, these users can start trading using Schwab's platform.

Update 2023-09-05:

Users received an email from Charles Schwab confirming transition and to review the account verification letter which allow logging in to your account.

Here is the text from the email.

ACTION REQUESTED: Review your Schwab Account Verification letter.

Welcome to Charles Schwab. Please review your Account Verification letter.

We're so glad to welcome you to Schwab!

Now that we've moved your TD Ameritrade account to Schwab, you'll have access to the combined power of our firms, including best-in-class strength and stability; a wider array of products and services for industry-leading low costs; and a larger team of professionals with a shared passion for helping clients achieve their goals.

Harshad Mehta: From Pied Piper of the markets to India's best-known scamster

Harshad Mehta, a charismatic and ambitious stockbroker, rose to fame in the 1990s, captivating the dreams of middle-class Indians with his rags-to-riches story. He became a symbol of success on Dalal Street, the Indian financial market. However, his questionable means and the Rs 50-billion securities scam tarnished his image. Despite attempts at comebacks, his flashy strategies and old charm failed to work, leading to his downfall. His involvement in market manipulation, bribery claims, and legal battles led to his decline. Despite once being a guru dispensing stock tips and creating speculative bubbles, his optimism waned, and financial troubles mounted. He eventually passed away in jail, marked by convictions and trials, ending an era of his influence on Indian markets. Sucheta Dalal chronicles the story of Harshad Mehta in this article.