How to make a Virtual Stock Portfolio (in Google Docs)?

For everyone who invests their money in stocks, needs to track them on regular basis. It’s not like, you park your money and forget about it.

Traders and Investors who have demat accounts, can easily track their portfolios online via their broker’s website. But for those who are students or do not have an account or want to try investing virtually, there is a way to track their portfolios. There are many websites that offer virtual portfolio tracking, but we have to sign up for them.

However, Google Docs offers a way to track portfolio. Since, almost everyone has a google account, it would be easier to make virtual portfolios through google rather than signing up for websites.

So here is a step by step guide on how to track virtual portfolio through Google Docs:

Step 1:
Search Google Spreadsheets on your browser.

Step 2:
Select the result that shows: Google Sheets: Sign In

Step 3:
Click on Blank Template and once it opens, you may see a spreadsheet with columns and rows.

Step 4:
Now here is the actual task begins. First you have to name all the columns.
Here is an example.

Step 5:
Now, we have to apply formulas in each row under the column name.
Here are the formulas for the spreadsheet shown above.
LTP : =GOOGLEFINANCE(“NSE:” &__,”PRICE”)
52 Week High: =GOOGLEFINANCE(“NSE:” &__,”HIGH52″)
52 Week : =GOOGLEFINANCE(“NSE:” &__,”LOW52″)
Total Investment: __*__ (BUY PRICE multiplied by QUANTITY)
Investment Value: __*__ (LTP multiplied by QUANTITY)
Profit/Loss: __-__ (Investment Value-Total Investment)
Profit/Loss %: __/__*100 (Profit/Loss divided by Total Investment multiplied by 100)

Please Note: The underscores above (__) means the cell number of the spreadsheet. (For eg. A4 or C8)

Here is an Example:


The formulas here are:
LTP: =GOOGLEFINANCE(“NSE:” &A2,”PRICE”)
52 Week High: =GOOGLEFINANCE(“NSE:” &A2,”high52″)
52 Week Low: =GOOGLEFINANCE(“NSE:” &A2,”low52″)
Total Investment: =E2*F2
Investment Value: =B2*F2
Profit/Loss: =H2-G2
Profit/Loss%: =I2/G2*100

Step 6:
Now if you want to create watchlist for multiple stocks, you can click and drag the bottom right corner of a formula cell and then, automatically, the formula will get applied to other cells.
Please remember, if you dont write the name of stock correctly, then the data wont show up.

The formula I have provided above will show data only for NSE. So the scrip name must be present in NSE.
If you are having problem in dragging the formulas, please refer to this article: https://www.wikihow.com/Apply-a-Formula-to-an-Entire-Column-on-Google-Sheets-on-PC-or-Mac


Done!
And that’s how you make portfolios using Google Sheets/Doc. I know this method is a bit time consuming and other websites provide online portfolio trackers with much convenience, but then, when you sign up for them, you give them their email id and phone number. Then you shall be ready for all those spam messages in your emails and messaging apps filled with ads and other stuff.
So with Google Sheets, you can easily make portfolios without any signup!
Also Google offers other formulas useful for investors and traders. To check these formulas, please refer this article: https://support.google.com/docs/answer/3093281?hl=en

If you have any query or doubt in the process, please ask it in the comment section and I will be happy to help you with your query.

Thank You

Similar Posts

One Comment

Leave a Reply