How to build a stock portfolio tracker into Excel?

two white printer papers near macbook on brown surface
  • Best investing books
    I own a small library of investing books. Right now I have 13 books on the table. Some days ago I ordered 6 new books from Amazon. So in total, I have 19 investing books. I have not read all of them, but I want to finish them by the end of this year. But […]
  • How to build a stock portfolio tracker into Excel?
    Excel is one of the best places to keep track of your investment portfolio. Yes, there are some cool applications or platforms where you can see even more information than Excel. However, if you are granted some loans or have real estate, you cannot add them to it. This forces you to keep track of […]
  • Investing tips: How to beat 90% of investors?
    History has shown that 80-95% of investors usually lose money. Thus I want to teach you how to avoid losing money. In my first year, I was down over € 100 what was over 20% of my portfolio. At the end of 2020, I started to look for investments and I found something really good. […]

Excel is one of the best places to keep track of your investment portfolio. Yes, there are some cool applications or platforms where you can see even more information than Excel. However, if you are granted some loans or have real estate, you cannot add them to it. This forces you to keep track of your stock portfolio in these applications or platforms and your real estate in Excel. Why monitor them separately?

First of all think about design

Personally, I don’t like a design where all the data is in one table and all the assets are there. So I made three different tables: stock, co-financing and crypt. It helps me get a better idea. So I suggest you do the same.

In this link https://www.oldschoolvalue.com/investment-tools/stock-tracking-spreadsheet/, you can see this tracker design what I don’t like. Yes, there is a lot of information. But this is too variegated for me. I like when there is only this information that I need and that’s it.

My design is like this.

How to get data into it?

This part is a little complicated. I get my data automatically from Yahoo Finance, but there are some stocks that I have to insert manually. But this is 5 minutes of work. And everything else is copy-paste.

First, I entered all the daily stock prices one by one. Then I realized that if I continued like that, I would finish my job in 3 weeks. So I tried and and tried until I found out a way where you can do it automatically.

There are two ways to do this. The first option is to use an Excel function called STOCKHISTORY. This is the easiest way. Here you can see what data you need and all. However, this method does not work very well and is only available at Microsoft 365.

Another way to get data is to “Get data from the web.” You can find it in the Excel data section in the upper left corner.

Now you need to find your shares in Yahoo Finance or if you are looking for crypto, use Coindesk because Yahoo Finance does not have enough data and their prices are usually wrong. So let’s make an example.

I want to add Tesla daily prices and I want excel to refresh all these data when I open my Excel tracker. You can do this step also automatically with VBA.

Step 1

Find Tesla stock from Yahoo finance. And now click on “Historical Data”.

Step 2

Now select your buy date. Let’s say that I bought Tesla from IPO ( I wish I would ). Now copy this page URL link. And paste it into that “Get data from web”. Use basic and press “Enter”.

Step 3

Now you should see this. On the left, you can see ” Display Options”. Click on Table 2. Now you are faced with an error – what says that you can enter only a few month data with this method because of file size. If someone can help me with this, that would be nice. My solution for this is that I download this data manually and copy all that I need – daily prices.

Step 4

After you had clicked on “Table 2” then if you want that same table, click on the “Load”. But if you want to delete some columns or rename headings, then click on the “Transform Data”.

Now you should see this table. If you want to use formulas like multiply with your share amount then in my country (Estonia) I have to change all “.” to “,”. You can do it by replacing – what you found under “Home” – in the right corner.

I don’t need these columns: Open, High, Low, Volume, and Adj Close. So I delete these with “Remove columns”. If you want to rename some columns name then you can do it under “Transform”.

After you are finished with editing then click “Load”. Now you can refresh your data whenever you open Excel. The second way to do so is by writing a VBA code that refreshes your data automatically.

Next step is you to design your own “Analysis” page. You can use my design or this one what I don’t like.

All this information above where I teach you how to get stock historical data is needed for this graph what I have under “Investeeringud” (which means in English “Investments”).

How to make tables like I have?

I have invested into three different assets: stocks, crowdfundings, and cryptocurrencies. Thus I have only three tables, but I want to invest in future into loans and also I want to own a real estate.

I selected the stock table.

In the headings I have: Asset, Quantity, Buy price, Total, Last price, Last value, and Yield. To get stocks tickers and last price you can use also excel functions. you only have to type what stock you hold – example Plug Power.

Click on the “Data” section and then on the “Stocks” next to “Currencies”

Now there will appear “Data Selector” this is because your company can be listed on several stock exchanges. You select this one where you bought it.

When this is done then you will see this little box. If you click on that there will be many different opportunities where you can choose what info you need. I selected the “Ticker symbol” and “Price”.

In the bottom line

I hope you learned something new. If you found this post really helpful then share this with others and comment below this post if you find something I did wrong or you need help with something.

Also, go and check out my posts about how to start with investing, how to beat 90% of investors or if you want to get your first million then check this post.

One thought on “How to build a stock portfolio tracker into Excel?

Leave a Reply