5 Support Videos for the Excel Stock Portfolio Spreadsheet [VIDEO]

Skeptical about what information to include in your stock portfolio spreadsheet?

Portfolio headers – video transcript

So, what information do you want to capture when you’re creating a stock portfolio spreadsheet in Excel or Sheets?

I like to capture the name and symbol, of course, to identify the investment.

The purchase date is important because it can help you calculate the annualized gain and loss.

Purchase price and shares, of course, is critical because it helps you calculate or just cost – the total amount you spent on the investment.

Then, the current price is necessary because it helps you come up with a gross current value which is the current price times the number of shares you purchased.

Then, of course, dividends can play an important part in any investment’s return. So, you’ll want to capture them.

Calculated columns in the Stock Portfolio Spreadsheet

The net current value is going to include the gross current value, which is capital gains, plus your total dividends received. Once you have that, you can calculate the total gain and loss in terms of dollars and total gain and loss terms of percentage. That gives you, obviously, a good perspective on how that particular investment has done.

But, to give it even a little more, I think it’s necessary to calculate the annualized gain and loss or CAGR, compounded annual growth rate. That puts every investment on an equal footing and looks at it on an annual basis.

Using SPARKLINES in the Stock Portfolio Spreadsheet

Another column that I just kind of discovered recently and added to my Create an Amazing Stock Portfolio Spreadsheet in Excel (which I’ll put a link to down in the description) is using spark lines and GOOGLEFINANCE to go back in time and be able to make a chart based off however many months you want to see. So, you can do, you know, as little as one month or do five years by doing 60 months and it’ll just give you a little mini chart there to kind of visualize what the return has been. I think that’s handy too.

Yeah, I’ll do a separate video and maybe a blog post on that formula to break it down and on how to add that. Or. you can just go to the blog post and download the updated spreadsheet.

Anyhow, I hope that gives you some ideas of what to include in your stock portfolio spreadsheet. Thanks for watching, see ya.

Flawlessly track the performance of different lots in your portfolio

Portfolio lots – video transcript

I’ve got another video here regarding some of the questions I’ve received about the Creating an Amazing Stock Portfolio Spreadsheet in Excel video and post.

Particularly, this time, I want to address a question I get pretty often, which is what to do if you have different purchase dates for different lots of the same stock. That is actually a good question. It’s not really obvious how to handle that, but I’ve got two ideas for how you might go about it.

Separate lots in the Stock Portfolio Spreadsheet

We’re looking at the spreadsheet here and in particular, we’re going to be looking at this company this Valley National Bank Corp (VLY). See all the pertinent details right there. Let’s pretend this is your portfolio this Valley National Bank Corp you purchased in two separate lots.

One of the two things you can do is simply list the two separate lots in your portfolio. Something like this. Valley National Bank Corp lot 1 and lot 2.

I want to leave the symbol VLY, of course, and not put any sort of indication of different lots there. Because the GOOGLEFINANCE function wouldn’t recognize it.

Then, if you look here, we’ve got two separate lots and one was purchased on March 9, 2014, at $10.61. 59 shares. This is the number of dividends received on that particular lot.

Then, you’ve got your second lot here. Purchased on September 15, 2014, at $9.46. A cheaper price and 141 shares. Here you can see the dividends you’ve received.

Grouping separate lots together

You can list them out like that and look at the performance of each lot separately. Or, you can do something similar to what I’ve done here for my example.

If you take the portfolio and the worksheet and duplicate it, okay, so you got a copy there. Then you can delete everything out and if you type your separate lots in here. What it’s going to do, at the top, is give you the information you would need but in one single lot.

So, see what it did here, based on these purchase dates and number of shares, it gave me a purchase date of 7/16/14. That matches what’s here

I’ll get the purchase price in just a second.

Of course, you’ll know the sum total of the shares you have. You put that in there. And, you’ll know the sum of the dividends.

Weighted average purchase price

Then all that’s really left, if you want to group it all together into one entry, is to come up with a weighted average purchase price. You can see that I did that here. All I did was take the number of shares divided by 200 (my total number of shares) and took it times the first purchase price.

Then, 141 shares divided by 200 and took it times the second purchase price. So, that gives me a weighted average purchase price right here.

If you don’t want to do separate lots in the portfolio – make a copy of the worksheet, clear everything out, and type your separate lots in there. Most of the information you’re going to need is going to be automatically calculated for you. All you’ve got to do is come up with this weighted average price.

So, hopefully, that gives you an idea of how to deal with this particular issue

Thanks to the people who’ve emailed, commented, and brought that up; and hopefully, that helps.

Thanks for watching!

Utilize the GOOGLEFINANCE function in Sheets – “price” attribute

GOOGLEFINANCE – video transcript

I quickly wanted to get into a little more detail about the GOOGLEFINANCE function.

Specifically, I’m going to focus on the “price” functionality. There’s a lot of other things you can do with the GOOGLEFINANCE function in Google Sheets. But, this is related to my Create an Amazing Stock Portfolio Spreadsheet in Excel or Sheets and I’ll put a link to that post and to that video down in the description.

There are a couple of different things you can do with the “price” attribute and with the GOOGLEFINANCE function. I made some examples here to walk through.

GOOGLEFINANCE no attributes

So, as you can see, the first example shows you can have no attributes. As you can see here, you don’t even necessarily need to specify any attributes with GOOGLEFINANCE. If you don’t, it’s going to assume simply that you want “price.”

So I’ve got my ticker here for Tesla and all I entered in this cell, here, C4 is =GOOGLEFINANCE and C2.

If you’re curious about the dollar signs, and you’re not really familiar with spreadsheets that are just an absolute reference is what they call that. That’s just so I could copy this around and not have to retype in “C2.”

So, don’t worry too much about the dollar signs they’re just a little something extra added to the formula. There for my convenience.

It’s as simple as that, =GOOGLEFINANCE and then you could type in TSLA in quotes here or you could reference a cell. I would prefer to reference a cell because in that case, you can always change it to something else. AMZN, for instance.

Let’s see, um everything that’s referencing that cell is updating. Anyhow, that’s my recommendation. But, do you please.

GOOGLEFINANCE “price” attribute

Okay, so the next example, as I said, is using the “price” attribute with GOOGLEFINANCE. This is where I actually specify “price” now, and this is a little redundant. You can see it brings up the exact same results as this cell did. All I did was type =GOOGLEFINANCE(C2,”price”) and got the same results. There’s really no point in putting “price” in there unless you go on to do something which I’m going to show you with the next examples.

In this example here, which I just had to update. If you’re wondering why that changed – here’s what the formula looks like =GOOGLEFINANCE(C2,”price”). We’re referencing Tesla, we want the price. But, in this case, we’re doing “price” with a start date.

GOOGLEFINANCE start date

Okay, so, start date what does that mean? it means the beginning date that we want to go fetch the price for. Since we’re not specifying an end date. Yet it’s just going to get the date for that specific price. Right here, we specify January 1, 2019.

Now, the stock market was closed on January 1st, of course, for New Year’s Day. So, it just brings up the next day which is January 2nd, 2019 – the next trading day. Which tells you what the close was then.

Notice how I entered the formula here and how it brought in that date header because I specified a start date and a close date. Then, it gave me this information.

Okay, all that was populated just by this formula – those four cells. If I delete that for instance, you see, it all disappears. Put it back – it all reappears.

GOOGLEFINANCE end date

So the next example, logically, is if you can enter the start date – you can enter an end date. And, that does what you might expect – it’s going to bring up every price between those days.

Here’s our end date, right here, January 31st. So, we’re looking at just January 2019 with the start date of January 1st, 2019. And an end date of January 31st, 2019.

So, we type that formula. here we’ve got =C2 which tells GOOGLEFINANCE we want Tesla price and specifies the attribute. We got our start date and then comma end date so that’s there, and here it brings all this information in. I will also let you know that I formatted these dates so they look a little cleaner they’re going to come in initially like this – with the time “6:00.” Which is four o’clock, I guess. Which is when the closing price is official. I don’t know. anyhow that’s what it does, um, so to show you how flexible this can be.

We could, for instance, do the first quarter of 2019 by simply changing this to 3/31/2019. It’s gonna load for a second, and boom. here we go. See, it goes on. I didn’t format these last few cells with the dollar or with the date. but, you can see it goes on.

So, that’s a quick and easy way to get an automated update of closing prices for a period of time. and you can actually go above and beyond this with the GOOGLEFINANCE function.

But that’s a topic for another video. let’s change this back here 1/31/2019.

GOOGLEFINANCE number of days

Okay, we’ll look at the next example. This one’s similar in that we specify a “price” with a start date and then with a number of days.

You might ask yourself “well what’s the difference? 30 days difference between January 1st, 2019 and January 31st, 2019?

Well, you know this is redundant. also, but, it’s not exactly, because in this case – the number of days is going to refer to trading days. See how it goes well into February? Halfway through February. There. In fact, because we specified 30 trading days and since the market isn’t open on weekends or holidays – this is what we get instead.

The benefit of this, I guess, would be if you wanted 250 days of history for a particular stock well you don’t have to sit there and calculate what day was 250 days ago, not counting holidays, not counting weekends, you know. If you manipulated this particular function, correctly you could just get a rolling 250 days of history for a given stock. And, as you can see, we entered =GOOGLEFINANCE, specified Tesla, specified the “price” attribute, had our start date, which is C10, right here, but then G15, which is the 30 days.

GOOGLEFINANCE weekly interval

Okay, so the final example is “price” with a start and end date; but at a weekly interval. You know, if you want to look at a little longer term. Or, you know, you don’t want to take up so much of your spreadsheet, with a bunch of data by date. Or, if you’re more interested in what’s happening week to week then all you’ve got to do the same thing you did previously – specify your ticker, “price” attribute, start date, and end date. But go ahead and put another comma at the end and type “weekly” at the end.

You can see what it does here, it updates with every Friday’s closing price. Look here at January 4th, 2019, $63.54 was the close for Tesla. that matches here.

$69.45 for January 11th, same thing. It’s flexible. whatever you want, start and end date to be.

Let’s take this to 12/31, a full years’ worth. Okay, we can see every Friday here. Thursday in some instances. I would assume because of Good Friday and other holidays.

But, you know, again keep in mind that the formatting, you’re going to have to probably mess with because it’s going to come in kind of rough. But that’s easy enough (to fix). And there you go there are 52 weeks of closing price data for Tesla.

Anyhow again I make this to just go into a little more detail about the GOOGLEFINANCE function. There are a lot of things you can do with it beyond this.

If this video proves to be somewhat popular I can go into more detail on those. But this is kind of a test run. Again it’s serving as something of a compliment to my Create an Amazing Stock Portfolio Spreadsheet in Excel video and post.

Like I said, links, to those down in the description

That’s all I got. See ya.

Add dividends to your stock portfolio spreadsheet

Dividends in portfolio – Video transcript

Dividends are an important part of overall returns so that’s why I included a column on my Create an Amazing Stock Portfolio Spreadsheet which I’ll link down in the description.

That’s why I included a column for total dividends received as you can see. That’s entered manually here.

Entering dividends manually

I talk a little bit about why the reason it has to be manual is that it’s difficult information to automate, unfortunately. Total dividends received figures into the net current value. Which is what is used to calculate total gain and loss, dollars, percentage, and, ultimately annualized gain and loss.

Like I mentioned in the post and in the Create an Amazing Stock Portfolio Spreadsheet video (I’ll also link that in the description) is the total dividends received should be entered for each individual stock.

If you decide to enter your lots separately like say DEO here. You purchased 100 shares at one point and 30 shares at another point. Rather than 130 shares all at once. Then you want to break those dividends down by lot just to get an accurate total gain and loss for each lot.

Dividends from broker

Hopefully, that makes sense. So where do you go about retrieving this information? As I mentioned in the post the ideal place is from your broker. Now I give a little screenshot here of my account from TD Ameritrade. For this video, I tried to chase down where to find this information for some of the bigger brokers. Your E-trades, Schwabs, what is it, Fidelity. In them and surprisingly this information was kind of hard to find you know. Because I wanted to be able to say if you have any trading account go click, here, here, and here. And um yeah like I said it was uh not easy information to get a hold of.

So I can only show you firsthand for TD Ameritrade. You know hopefully it’s not too hard to find for your particular broker and it should follow more or less the same logic. It should be readily available information. Anyhow uh on TD Ameritrade as I show here you just click on the transactions tab, select dividends, from the list, and then you’ll have to enter your symbol. Which is fine. And enter a date range and that’s it.

You know it brings up a search and the total amount of dividends. You got to kind of calculate them manually but not too much trouble.

Dividends from other sources

Now if for whatever reason your broker doesn’t do that then I recommend going to the Nasdaq dividend history page. This is the best tool that I’ve found. You can enter a symbol here. You want to click on dividend history here.

Okay, so here you can find a dividend history going back a very long time. So this is of course going to be per share and then you’re going to have to do a little work on your own to figure it out.

So you know if we look here back on the portfolio spreadsheet, VLY we can see we purchased it on July 16, 2014. 200 shares. So in this case we would know we want to look at the ex-dividend date and pick the ex-dividend date that’s after that purchase date.

Okay, so July of 2014. So it’s basically every dividend from here forward you want to copy it. You should be able to copy that information and you can create a new tab. I would probably say Ctrl+Shift+V just drop in the information.

Calculating dividend history from other sources

There you go and then all we’re going to do here is say equals per share dividend amount × the number of shares we have. It looks like it was $0.11 per share consistently ever since. So we just type in equals the column with the dividend times our 200 shares.

Copy that up and there we go. We’ve got our total here, some $594.

As I mentioned the spreadsheet was a little dated at this point. But we would just update that and there we go.

It’s going to update our amounts are net earned value, total gain and loss, etc. Again the ideal way is to get it from your broker. The backup way is to go to nasdaq.com and look at dividend history or some similar site.

You know just take your amount of shares times the per-share dividend. There you go. It’s a little labor-intensive. It would be ideal for it to be a little more automated. But you know we work with the tools we have.

If there’s a better way I please feel free to comment. Let me know I’d love to know it.

Dividend tracking spreadsheet

I’ll make one last point here. I also have a Create an Amazing Dividend Tracking Spreadsheet. It’s proven to be pretty popular and I will make an in-depth video on it at one point. But I’ll link to it down in the description of course.

Just wanted to bring it to your attention. It’s a situation where real quick where you can put in the stock you own the number of shares, purchase price, which will help with the yield on cost. I think it’s called.

Then you’ve got a separate screen where you enter your individual dividend data. See all that here and what I’ll do for you then is automatically update these pivot tables where you can look at your yield, yield on cost, all these kinds of cool analyses.

Then you can make dividend growth to kind of analyze each individual stock and look at its merits from a dividend-paying standpoint. You can judge it on that one aspect by itself.

Check that out and like I said video for that is coming soon.

That’s all I got, thanks for watching. See you.

Calculating Returns in a Portfolio Spreadsheet | $ and % Return Plus CAGR Formulas

Calculating portfolio returns – Video transcript

All right so if you’re going to create a stock portfolio spreadsheet, you’re of course going to want to measure the performance of each of the individual stocks In your portfolio.

The reason I made this video is so that I could cover some of the different columns I’ve included that allow you to measure the performance of each individual stock. And I just wanted to go into a little more detail beyond what I’ve talked about in my create an amazing stock portfolio spreadsheet post and video. Go into a little more detail like I said, about those column performance formulas.

Net current value

The first one here is pretty straightforward as most of them are. It’s net current value which is going to include gross current value oh, okay. Which is the current price times the number of shares. G6 X I6 there you can see. Plus dividends received.

So that’s what the shares are currently worth plus the dividends you’ve received since you’ve owned the stock. All right.

Total gain and loss in dollars

Knowing that then we can calculate total gain and loss in dollars.

This is simply going to be the net current value minus purchase cost. Column L minus column H. This is the total amount of money that you made owning the stock. Also, like I said pretty straightforward.

Total gain and loss percentage

Then, knowing that we go onto total gain and loss expressed as a percentage.

This is your total return on this given stock. Total gain and loss as a percentage is simply measured by taking column M / column H. Which is total gain and loss in dollars divided by purchase cost.

Annualized gain and loss, CAGR

Then, we come to, annualize gain and loss which is a little more complicated to calculate. It’s something you want to know because, you know, total gain and loss doesn’t tell the entire story. It doesn’t tell you about time. Okay.

For instance, if we look at this DEO here with a 66.9% total gain and loss. That sounds good, but, you know, if we’ve only gained at 66.9% over 20 years. That’s not so great. If we’ve gained that in one year, or a month, or even two years that’s a pretty healthy return. You know we’ve got to come up with a way to compare apples to apples here. Basically, what the yearly return has been on each stock so that way we can really size up how well it’s performed.

How we do that is with the annualized gain and loss? Often also known as CAGR compound annual growth rate.

Portfolio updated date

In order for this formula to work, you’re going to notice that it references D2 which is your portfolio updated date. Now, if you download my spreadsheet, this is automatically going to be updated as of today. If you make it yourself that might not be the case. But, you’re going to want this date to be accurate because it’s going to reference that. The amount of time that’s passed between today and between the purchase date of the stock. In order to give you an accurate annualized gain and loss. All right.

For instance, if we change this to a year ago. You look here we had a 9% return annualized return for DEO. We change that to 4/14/20. Well, it gets much better. Why? Because the past year isn’t taken into account.

It’s important that you make sure that your portfolio updated date is what you wanted to be in order to get an accurate annualized gain and loss.

Annualized gain and loss formula

Okay so the formula itself, oh, let me also touch on something here you’ll notice that I’ve got some of this gobbledygook at the beginning of the formula. Which basically just looks at another cell and determines whether it’s blank or not. If it is blank it’s going to give me a blank here. This is just done so that I can keep the spreadsheet looking pretty clean.

The formula to calculate annualized gain and loss is kind of complicated. Definitely more complicated than anything else in the spreadsheet. If you’re not a formula wizard or the type of person who’s comfortable with formulas then just copy it and trust it works.

You can, um, compare that. Go to another website and look for an annualized gain and loss calculator. Plugin the values double check it. Make sure it works.

I’m sure the formula is accurate. But I’m just saying if you copy it to your spreadsheet and want to be sure you can do something like that.

What it does in essence is it looks at the return. Which is L6 / H6. Net current value / purchase cost. It looks at that and it takes that to the power of a fraction.

The fraction is 1 / the amount of time, the number of years that have passed, That’s what this YEARFRAC does. YEARFRAC returns the number of years including fractional years, okay, which is good. Because then we get a, you know, and up to the day annualize gain and loss.

It includes fractional years between two dates by setting a specified count convention. What we do is put in their start date and end date. The start date at the date we purchased it and the end date is today’s date technically. Then we take all of that minus 1 just so it can be expressed accurately as a percentage.

But the formula, in whole, is open parentheses L6 / H6. Then you’re going to take that to the power and open another parenthesis. 1 / this YEARFRAC, purchase date comma portfolio updated date, close all the parentheses. Take it minus one.

This last close parenthesis here is just for the IF function for the blank cells.

DIY, download, or copy the formula

Again that might be something you want to tackle. That might be something that’s beyond the scope of what you care to learn. Fair enough. Just copy it and roll with it, double-check if you like. Or, you know if you’re interested in learning more you can toy with it, of course.

Anyhow that covers all of our performance formulas. You know what the current value is, current gain and loss dollars, total gain and loss percentage, and annualize getting lost percentage is.

That’s all I got, for now, thanks for watching. See you.