{"id":61564,"date":"2022-12-23T17:17:05","date_gmt":"2022-12-23T11:47:05","guid":{"rendered":"https:\/\/www.quickonlinetips.com\/archives\/?p=61564"},"modified":"2026-10-11T16:19:11","modified_gmt":"2026-10-11T10:49:11","slug":"track-stock-watchlist-portfolio-in-google-sheets","status":"publish","type":"post","link":"https:\/\/www.quickonlinetips.com\/archives\/2022\/12\/track-stock-watchlist-portfolio-in-google-sheets\/","title":{"rendered":"How to Create a Stock Watchlist and Track Your Portfolio in Google Sheets (GOOGLEFINANCE Guide)"},"content":{"rendered":"<p>You don&#8217;t need a paid app to track your shares: you can track a stock portfolio in Google Sheets for free. With the free <strong>GOOGLEFINANCE<\/strong> function, Google Sheets can pull live stock prices for NSE, BSE and US stocks, and work out your profit and loss automatically. Set it up once and your watchlist and portfolio update themselves every time you open the sheet. Here&#8217;s how to build both, with formulas you can copy.<\/p>\n<p><img decoding=\"async\" class=\"aligncenter\" src=\"https:\/\/www.quickonlinetips.com\/archives\/wp-content\/uploads\/track-stock-portfolio.jpg\" alt=\"Track stock portfolio in Google Sheets\" width=\"960\" height=\"540\" \/><\/p>\n<p><strong>Quick answer:<\/strong> In any cell, type <code>=GOOGLEFINANCE(\"NSE:RELIANCE\",\"price\")<\/code> to get the latest price of Reliance on the NSE. Put stock symbols in column A and use <code>=GOOGLEFINANCE(A2,\"price\")<\/code> in column B, then drag the formula down to build a watchlist.<\/p>\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_89 counter-hierarchy ez-toc-counter ez-toc-custom ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-6ad66ba03e421\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"ez-toc-cssicon\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #1a202c;color:#1a202c\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #1a202c;color:#1a202c\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-6ad66ba03e421\" checked aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.quickonlinetips.com\/archives\/2022\/12\/track-stock-watchlist-portfolio-in-google-sheets\/#How_GOOGLEFINANCE_works\" >How GOOGLEFINANCE works<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.quickonlinetips.com\/archives\/2022\/12\/track-stock-watchlist-portfolio-in-google-sheets\/#Useful_GOOGLEFINANCE_attributes\" >Useful GOOGLEFINANCE attributes<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.quickonlinetips.com\/archives\/2022\/12\/track-stock-watchlist-portfolio-in-google-sheets\/#Part_1_Create_a_stock_watchlist\" >Part 1: Create a stock watchlist<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.quickonlinetips.com\/archives\/2022\/12\/track-stock-watchlist-portfolio-in-google-sheets\/#Colour_gains_green_and_losses_red\" >Colour gains green and losses red<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/www.quickonlinetips.com\/archives\/2022\/12\/track-stock-watchlist-portfolio-in-google-sheets\/#Add_a_mini_price_chart\" >Add a mini price chart<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/www.quickonlinetips.com\/archives\/2022\/12\/track-stock-watchlist-portfolio-in-google-sheets\/#Part_2_Track_your_stock_portfolio_in_Google_Sheets\" >Part 2: Track your stock portfolio in Google Sheets<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/www.quickonlinetips.com\/archives\/2022\/12\/track-stock-watchlist-portfolio-in-google-sheets\/#Tips_and_fixes\" >Tips and fixes<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/www.quickonlinetips.com\/archives\/2022\/12\/track-stock-watchlist-portfolio-in-google-sheets\/#Frequently_asked_questions\" >Frequently asked questions<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/www.quickonlinetips.com\/archives\/2022\/12\/track-stock-watchlist-portfolio-in-google-sheets\/#Is_Google_Sheets_stock_data_real_time\" >Is Google Sheets stock data real time?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/www.quickonlinetips.com\/archives\/2022\/12\/track-stock-watchlist-portfolio-in-google-sheets\/#Does_GOOGLEFINANCE_work_for_Indian_stocks\" >Does GOOGLEFINANCE work for Indian stocks?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-11\" href=\"https:\/\/www.quickonlinetips.com\/archives\/2022\/12\/track-stock-watchlist-portfolio-in-google-sheets\/#Can_I_get_historical_stock_prices\" >Can I get historical stock prices?<\/a><\/li><\/ul><\/li><\/ul><\/nav><\/div>\n<h2><span class=\"ez-toc-section\" id=\"How_GOOGLEFINANCE_works\"><\/span>How GOOGLEFINANCE works<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>The basic formula is:<\/p>\n<p><code>=GOOGLEFINANCE(ticker, attribute)<\/code><\/p>\n<ul>\n<li><strong>ticker<\/strong> is the exchange and symbol, written as <code>EXCHANGE:SYMBOL<\/code>. Use <code>NSE:<\/code> for National Stock Exchange stocks (e.g. <code>NSE:INFY<\/code>, <code>NSE:TCS<\/code>), <code>BOM:<\/code> plus the BSE code for BSE stocks (e.g. <code>BOM:500325<\/code>), and <code>NASDAQ:<\/code> or <code>NYSE:<\/code> for US stocks (e.g. <code>NASDAQ:AAPL<\/code>).<\/li>\n<li><strong>attribute<\/strong> is what you want: price, change, 52-week high and so on (see the table below). If you leave it out, you get the price.<\/li>\n<\/ul>\n<p>Prices are <strong>delayed by up to 20 minutes<\/strong>, so use your broker&#8217;s app for live trading. Google Sheets is for tracking, not for placing orders.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Useful_GOOGLEFINANCE_attributes\"><\/span>Useful GOOGLEFINANCE attributes<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<table>\n<thead>\n<tr>\n<th>Attribute<\/th>\n<th>What it shows<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td><code>\"price\"<\/code><\/td>\n<td>Latest price (delayed up to 20 minutes)<\/td>\n<\/tr>\n<tr>\n<td><code>\"change\"<\/code><\/td>\n<td>Change in price since yesterday&#8217;s close<\/td>\n<\/tr>\n<tr>\n<td><code>\"changepct\"<\/code><\/td>\n<td>Percentage change since yesterday&#8217;s close<\/td>\n<\/tr>\n<tr>\n<td><code>\"closeyest\"<\/code><\/td>\n<td>Yesterday&#8217;s closing price<\/td>\n<\/tr>\n<tr>\n<td><code>\"high52\"<\/code> \/ <code>\"low52\"<\/code><\/td>\n<td>52-week high and low<\/td>\n<\/tr>\n<tr>\n<td><code>\"pe\"<\/code><\/td>\n<td>Price-to-earnings ratio<\/td>\n<\/tr>\n<tr>\n<td><code>\"marketcap\"<\/code><\/td>\n<td>Market capitalisation<\/td>\n<\/tr>\n<tr>\n<td><code>\"volume\"<\/code><\/td>\n<td>Number of shares traded today<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Start typing <code>=GOOGLEFINANCE(<\/code> in a cell and Sheets suggests the options. Google&#8217;s full list is on its <a href=\"https:\/\/support.google.com\/docs\/answer\/3093281?hl=en\" target=\"_blank\" rel=\"noopener\">GOOGLEFINANCE help page<\/a>.<\/p>\n<h2><span class=\"ez-toc-section\" id=\"Part_1_Create_a_stock_watchlist\"><\/span>Part 1: Create a stock watchlist<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>Set up these columns in row 1: <strong>Symbol<\/strong>, <strong>Price<\/strong>, <strong>Change<\/strong>, <strong>Change %<\/strong>, <strong>52W High<\/strong>, <strong>52W Low<\/strong>. Then enter your stock symbols in column A and these formulas in row 2:<\/p>\n<table>\n<thead>\n<tr>\n<th>Column<\/th>\n<th>Formula in row 2<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>A: Symbol<\/td>\n<td><code>NSE:INFY<\/code> (type the symbol)<\/td>\n<\/tr>\n<tr>\n<td>B: Price<\/td>\n<td><code>=GOOGLEFINANCE(A2,\"price\")<\/code><\/td>\n<\/tr>\n<tr>\n<td>C: Change<\/td>\n<td><code>=GOOGLEFINANCE(A2,\"change\")<\/code><\/td>\n<\/tr>\n<tr>\n<td>D: Change %<\/td>\n<td><code>=GOOGLEFINANCE(A2,\"changepct\")\/100<\/code><\/td>\n<\/tr>\n<tr>\n<td>E: 52W High<\/td>\n<td><code>=GOOGLEFINANCE(A2,\"high52\")<\/code><\/td>\n<\/tr>\n<tr>\n<td>F: 52W Low<\/td>\n<td><code>=GOOGLEFINANCE(A2,\"low52\")<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>Format column D as a percentage, then select row 2 and drag the formulas down for each stock you add. This video walks through the whole setup:<\/p>\n<div class=\"entry-content-asset videofit\"><iframe title=\"How to Make Stock Watchlist Using Google Sheets\" width=\"720\" height=\"405\" src=\"https:\/\/www.youtube.com\/embed\/3dYUMBXgSWU?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe><\/div>\n<h3><span class=\"ez-toc-section\" id=\"Colour_gains_green_and_losses_red\"><\/span>Colour gains green and losses red<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<ol>\n<li>Select the Change and Change % columns.<\/li>\n<li>Go to <strong>Format \u2192 Conditional formatting<\/strong>.<\/li>\n<li>Add a rule: <strong>Greater than<\/strong> 0, with a green fill.<\/li>\n<li>Add a second rule: <strong>Less than<\/strong> 0, with a red fill.<\/li>\n<\/ol>\n<h3><span class=\"ez-toc-section\" id=\"Add_a_mini_price_chart\"><\/span>Add a mini price chart<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Add a 30-day trend line next to each stock with the SPARKLINE function:<\/p>\n<p><code>=SPARKLINE(GOOGLEFINANCE(A2,\"price\",TODAY()-30,TODAY()))<\/code><\/p>\n<h2><span class=\"ez-toc-section\" id=\"Part_2_Track_your_stock_portfolio_in_Google_Sheets\"><\/span>Part 2: Track your stock portfolio in Google Sheets<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<p>To track what you actually own, add three more columns for each stock: <strong>Quantity<\/strong>, <strong>Buy price<\/strong> and the calculated values. Using columns G onward:<\/p>\n<table>\n<thead>\n<tr>\n<th>Column<\/th>\n<th>What to enter<\/th>\n<\/tr>\n<\/thead>\n<tbody>\n<tr>\n<td>G: Quantity<\/td>\n<td>Number of shares you hold<\/td>\n<\/tr>\n<tr>\n<td>H: Avg buy price<\/td>\n<td>Your average purchase price<\/td>\n<\/tr>\n<tr>\n<td>I: Invested<\/td>\n<td><code>=G2*H2<\/code><\/td>\n<\/tr>\n<tr>\n<td>J: Current value<\/td>\n<td><code>=G2*B2<\/code><\/td>\n<\/tr>\n<tr>\n<td>K: Profit \/ loss<\/td>\n<td><code>=J2-I2<\/code><\/td>\n<\/tr>\n<tr>\n<td>L: Return %<\/td>\n<td><code>=IF(I2=0,0,K2\/I2)<\/code><\/td>\n<\/tr>\n<tr>\n<td>M: Today&#8217;s gain<\/td>\n<td><code>=G2*C2<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p>At the bottom, total the Invested, Current value, Profit\/loss and Today&#8217;s gain columns with <code>=SUM()<\/code>. That gives you your portfolio&#8217;s total value and overall return at a glance. Here&#8217;s the portfolio version step by step:<\/p>\n<div class=\"entry-content-asset videofit\"><iframe title=\"How to track stock portfolio in Google Sheets\" width=\"720\" height=\"405\" src=\"https:\/\/www.youtube.com\/embed\/JomBexRnWY8?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe><\/div>\n<h2><span class=\"ez-toc-section\" id=\"Tips_and_fixes\"><\/span>Tips and fixes<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<ul>\n<li><strong>#N\/A error?<\/strong> Check the ticker format. It must be <code>NSE:SYMBOL<\/code> or <code>BOM:code<\/code>, with the exchange prefix and no spaces. Search the company on Google Finance to find the exact symbol.<\/li>\n<li><strong>Indices:<\/strong> try <code>INDEXNSE:NIFTY_50<\/code> for the Nifty 50 and <code>INDEXBOM:SENSEX<\/code> for the Sensex.<\/li>\n<li><strong>Currency rates:<\/strong> <code>=GOOGLEFINANCE(\"CURRENCY:USDINR\")<\/code> gives the dollar-rupee rate, handy if you hold US stocks.<\/li>\n<li><strong>Mutual funds:<\/strong> GOOGLEFINANCE doesn&#8217;t reliably cover Indian mutual fund NAVs, so enter those values by hand or use your fund platform.<\/li>\n<li><strong>Refresh:<\/strong> prices update automatically while the sheet is open. Reload the page if they look stuck.<\/li>\n<li><strong>Privacy:<\/strong> keep your portfolio sheet private, and don&#8217;t share the link with &#8220;anyone with the link&#8221; access.<\/li>\n<\/ul>\n<h2><span class=\"ez-toc-section\" id=\"Frequently_asked_questions\"><\/span>Frequently asked questions<span class=\"ez-toc-section-end\"><\/span><\/h2>\n<h3><span class=\"ez-toc-section\" id=\"Is_Google_Sheets_stock_data_real_time\"><\/span>Is Google Sheets stock data real time?<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>No. GOOGLEFINANCE prices can be delayed by up to 20 minutes, which is fine for tracking but not for intraday trading.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Does_GOOGLEFINANCE_work_for_Indian_stocks\"><\/span>Does GOOGLEFINANCE work for Indian stocks?<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Yes. Use the <code>NSE:<\/code> prefix with the NSE symbol (like <code>NSE:HDFCBANK<\/code>) or <code>BOM:<\/code> with the BSE scrip code.<\/p>\n<h3><span class=\"ez-toc-section\" id=\"Can_I_get_historical_stock_prices\"><\/span>Can I get historical stock prices?<span class=\"ez-toc-section-end\"><\/span><\/h3>\n<p>Yes. Add start and end dates, for example <code>=GOOGLEFINANCE(\"NSE:TCS\",\"close\",DATE(2026,1,1),TODAY())<\/code> returns daily closing prices since January 1, 2026.<\/p>\n<p><em>This guide is for tracking and learning only and isn&#8217;t investment advice.<\/em><\/p>\n","protected":false},"excerpt":{"rendered":"<p>You don&#8217;t need a paid app to track your shares: you can track a stock portfolio in Google Sheets for&#8230;<\/p>\n","protected":false},"author":4,"featured_media":61585,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_kad_post_transparent":"default","_kad_post_title":"default","_kad_post_layout":"default","_kad_post_sidebar_id":"","_kad_post_content_style":"default","_kad_post_vertical_padding":"default","_kad_post_feature":"","_kad_post_feature_position":"","_kad_post_header":false,"_kad_post_footer":false,"_kad_post_classname":"","footnotes":""},"categories":[3],"tags":[49],"class_list":["post-61564","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-google","tag-tutorials"],"_links":{"self":[{"href":"https:\/\/www.quickonlinetips.com\/archives\/wp-json\/wp\/v2\/posts\/61564","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.quickonlinetips.com\/archives\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.quickonlinetips.com\/archives\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.quickonlinetips.com\/archives\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.quickonlinetips.com\/archives\/wp-json\/wp\/v2\/comments?post=61564"}],"version-history":[{"count":3,"href":"https:\/\/www.quickonlinetips.com\/archives\/wp-json\/wp\/v2\/posts\/61564\/revisions"}],"predecessor-version":[{"id":63898,"href":"https:\/\/www.quickonlinetips.com\/archives\/wp-json\/wp\/v2\/posts\/61564\/revisions\/63898"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.quickonlinetips.com\/archives\/wp-json\/wp\/v2\/media\/61585"}],"wp:attachment":[{"href":"https:\/\/www.quickonlinetips.com\/archives\/wp-json\/wp\/v2\/media?parent=61564"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.quickonlinetips.com\/archives\/wp-json\/wp\/v2\/categories?post=61564"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.quickonlinetips.com\/archives\/wp-json\/wp\/v2\/tags?post=61564"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}