Stock Portfolio Tracker With Google Sheets [step-by-step] | Daniel JC | Skillshare

Playback Speed


  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x

Stock Portfolio Tracker With Google Sheets [step-by-step]

teacher avatar Daniel JC, Financial Teacher

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Watch this class and thousands more

Get unlimited access to every class
Taught by industry leaders & working professionals
Topics include illustration, design, photography, and more

Lessons in This Class

15 Lessons (1h 27m)
    • 1. Introduction

      1:24
    • 2. Overview of Spreadsheet

      0:46
    • 3. Database Part 1: Setting Up Equity Table

      7:10
    • 4. Database Part 2: Other Tables

      11:05
    • 5. Transactions Part 1: Data Entry

      7:10
    • 6. Transactions Part 2: Formula's and Formatting

      2:40
    • 7. Transactions Part 3: Rolling Cost Basis

      7:49
    • 8. Portfolio Summary Part 1: From Transactions to Positions

      12:21
    • 9. Portfolio Summary Part 2: Metrics and Other Stats

      12:31
    • 10. Portfolio Visualizations

      4:18
    • 11. Function Explanation: VLOOKUP

      5:03
    • 12. Spreadsheet Formatting Text and Cells

      4:19
    • 13. Function Explanation: SUMIFS and SUMIF

      4:30
    • 14. Function Explanation: GOOGLEFINANCE

      5:30
    • 15. Outro

      0:39
  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.

134

Students

--

Project

About This Class

Have you ever wondered how people build cool spreadsheets to track their investments? Well, you have come to the right place!

My name is Daniel and I run a mid-sized YouTube channel on investing (if you're curious, its called The Fire Grind). The purpose of this course is to share step-by-step, how to build a portfolio spreadsheet tracker from scratch! I use plenty of formulas in order to automate a lot of the manual repetitive data entry so that you can save minutes, if not hours of time each year. I'll walk through how each formula works and the great part is, you can then use these formulas to create new metrics and visualizations. Other applications which these formulas can be used are in budgeting, signup lists, and logging gaming data.

I've spoken a lot about formulas but haven't told you what they are. So here lets talk about that. By the end of the course, you should have a good understanding of how to use VLOOKUP, GOOGLEFINANCE, SUM, ISBLANK, ISODD, ROW, IF, IFERROR, SUMIFS, basic arithmetic and string manipulation.

I believe the best way to learn is to learn by doing. So follow along as we start from scratch. Lastly, always remember, google is the best place to search if you are looking for supplemental information on a formula.

Meet Your Teacher

Teacher Profile Image

Daniel JC

Financial Teacher

Teacher

Class Ratings

Expectations Met?
  • Exceeded!
    0%
  • Yes
    0%
  • Somewhat
    0%
  • Not really
    0%
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.

Transcripts

1. Introduction: Hi, my name is Daniel and I'm what you would call a finance nerd. I run a YouTube channel by night and recently I've gotten into advanced spread sheeting using Google Sheets. In this course, I'll see I can turn this blank spreadsheet into an advanced portfolio tracker. I'll walk you step-by-step so you can start from scratch with pretty much no knowledge of Google Sheets and become a master by the end of this course, we're gonna be using a lot of advanced formulas that you can just click, drag and copy down that you don't have to spend countless hours on data entry as well as recalculation. On top of this, we're going to be creating some visualizations, and these are just going to show you a quick snapshot how your portfolio is allocated. For example, maybe you want to know the risk of your portfolio. You want to be able to see the breakdown of low, medium, and high risk stocks in to be able to rebalance if you have too much for one or the other. And beyond this course, you're going to be able to take the spreadsheet and personalize it for yourself by creating maybe your own data columns as well as visualizations. Ultimately, my mission as a creator as well as a teacher, is to empower you as a student to be able to go out there, track your personal finances, and make the best investment decisions for your future. Any point if you ever get stuck, you can make a copy of my portfolio spreadsheet charter. So grab some coffee or an energy drink, and let's get started. 2. Overview of Spreadsheet: So the spreadsheet consists of three different tabs. First, we are the portfolio summary, which gives you a summary of your entire portfolio, each and every single position that you have in some performance metrics behind each investment. Next, we have a transaction is tab, which documents every single transaction that you make by date. And lastly, we have the database. And the database is just basically a tag where we store a bunch of different information that we're going to reference in the transaction as well as portfolio Summary tabs. And if you watch the very end, we're going to have a bonus tab, which are actually going to be some portfolio visualizations. And basically with these portfolio visualizations are, are just grass to this data from the portfolio summary. So you can see metrics at a quick glance from the visualizations, because we all know that pictures speak louder than a 1000 words. 3. Database Part 1: Setting Up Equity Table: So this is the end goal for the database sheet. You've got to Equity Bank, which actually documents all the different securities or stocks or the pot. And the different pieces of information that we wanted to know, including the exchange, their currency, acid types, sector risk, current price, the company name, and the order added. Next, these are the type of actions you can do. If you've already got a portfolio with positions in it, you can enter all your starting positions, then anything that you do afterwards, whether it's a buy, sell, a dividend, receiving a deposit or withdrawal, then you can track these types of actions as well. If we move on to the column next to it, we've got our asset types. The two isotypes currently supported by the spreadsheet are cached in stocks potential in the future. You can also add options or maybe even if you've got real estate and you want to type that into the sheet, then you can go ahead and do that as well. Which the next column we've got the number of security exchanges. So here I've documented all the Canadian as well as the American security exchanges. We've got the TSC, we've got the TSX venture, the Canadian Security Stock Exchange or the nasdaq. And we see, as well as the OTC markets in the states. Moving over to the right, we have our currencies and currently the sheets supports Canadian and US dollar. However, if you are overseas in Europe, for example, you can add your currencies here as well. In the exchanges. You wanna go over to Google Finance and figure out if the exchange, the urine is supported. So for example, if you're overseas in Europe and maybe a trade and UK, then you've got your own stock exchange and you can enter that symbol right here in the blank rows. Moving over to the next column, we've got currencies currently only of Canadian UST because those are the markets are, I'm invested in. Again, if you are overseas and you've got other currencies, you can place him down here further on in the column. Now if we shift over to the right, we've got our sectors. So these are the different sectors of the stock market. You can find information on this on Yahoo Finance. We've got to Yahoo Finance and just find an example. I'll show you what I mean. We're on Nike stock right now and you can see the sector that Nike plays him. They play in the consumer cyclical sector. And you can also add industry or you can factorize by industry if that's how you wanna do it. Here you can see all the current sectors I have from my portfolio, basic material, cash, finance, health care, et cetera. Finally, we've got risk in currently I've separated out into three different risk levels, low, medium, and high. If you want, you can even add other risk levels in there, but that's your personal choice. For me, I've only start to these three year. So knowledge jump over to an empty spreadsheet and I'll show you how I actually code this all up. First off, I'm going to change as a column width to be identical to the row width. So the row width is 21. So I'm going to change that right now. Now that's taken care of. Let's move on to the format of our equity table. So now I'm going to build up the equity table headings. So now we have all the table headings. Let's do some formatting. So now I format the headings, they will stand out. Now we can start building up the actual table, and this is what I'm gonna do. I'm going to add some borders all the way down to the very end. After adding the borders, are going to enter my first query symbol, which just give me a cash position. And we'll do an example here where I do a stock. So I'm gonna add Air Canada or AC. Now for the exchange, this is going to be a column that you have to manually enter and every single time. So the exchange for a Canada is going to be the TSX. I'm going to enter TAC. Cash doesn't have an exchange. So leave this blank. Now for the currency column, I'm going to leave the cash currency as blank right now, or we're just kinda hard-coded to Canadian dollars. If you're American or if you're in another country, you can type in your currency manually. Now for all your stocks and stuff that are listed on Google Finance, you can have a formula that actually pulls a currency from Google Finance. This way you don't have to enter it manually every single time. Rather, you can just click on this blue dot and drag the formula down. What you're going to type in is the self for the exchange first, then you login and sign. This is basically a concatenate. The strings are basically put it together all into one string. And then after that we're going to enter a colon sign and followed by an enzyme. And lastly, we're going to select the equity symbol. And what this really equals L. If we were to concatenate this or just push the string together, I'll just type it out so you guys can see it will actually equal to TOC colon AC. And that's what this formula here would actually Popper into the Google Finance formula. But because we want this to be formula driven and not having to actually enter this every single time. This is why we use this formula here rather than hard-coding and typing and TAC colon AC. So I'm going to delete that now and we're going to move on with the rest of this formula. Another tip, if you're stuck on what to enter into the formula, you can click on this question mark button. So the next thing that we want to type into this formula is the attribute. And the attribute I'm looking for is TSC, colon AC, or air Canada's currency. So for currency you're going to type in currency. And then we're going to close the bracket. And it's going to pop up the currency of TAC, TAC. Now for some formatting, we're actually going to center this and we're going to center the rest of these columns here. And that looks a little bit better. Next, acid type. So isotype is either cash or stock as I showed before. So isotope is actually a really manually entered server's going to type in cash and stock. Next we have the sector. And again, the sector is manually entered because we can't pull out from Google Finance. So for AAC, it's travel. In the cash column. I'm just gonna write cash again. So cash, I'm just going to assign a low risk. Although some could debate that it's high risk if your country is printing more and more currency. But that aside caches generally lower risk. So we're gonna type in low. For Air Canada, I'm going to assign it a button, medium risk at this point. So the risk column again, you're going to have to manually assign just like I'm doing here. Now for current place, we can use a Google Finance formula. So we're going to type in Google Finance and we can actually pull the TickerSymbol, the same formula that we had in the previous cell under currency. So I'm gonna do library now. Now that we've got the ticker, the attribute here is going to be the price. So this is going to give you the last closing price of the stock. So you can see Air Canada is $24.98. And if we go to Google Finance, we can see our Canada's currently trading at $24.98. Now the company name, or again, we're going to use the same Google Finance formula, same ticker formula, and we're going to select the name. Now the order column is really just the order that you place the stocks in this table in case you want to use filters later on and you don't want to forget the order that you enter them. So if you're going to add more stocks to stray your order down like that. Now that we've got this table pretty much built out, I'm just going to select these columns again just to some reformatting. So that's our first table belt Ray now. 4. Database Part 2: Other Tables: So this is the end goal for the database sheet. We've got to Equity Bank, which actually documents all the different securities or stocks. So we've bought and the different pieces of information that we wanted to know, including the exchange, their currency, acid types, sector risk, current price, the company name, and the order added. Next, these are the type of actions you can do. If you've already got a portfolio with positions in it, you can enter all your starting positions, then anything that you do afterwards, whether it's a buy, sell, a dividend, receiving a deposit or withdrawal, then you can track these types of actions as well. If we move on to the column next to it, we've got our asset types. The two acid types currently supported by the spreadsheet are cached and stocks potentially in the future. You can also add options or maybe even if you've got real estate and you want to type that into the sheet, then you can go ahead and do that as well. Over to the next column, we've got the number of security exchanges. So here I've documented all the Canadian as well as the American security exchanges. We've got the TSC, we've got the TSX venture, the Canadian Security Stock Exchange or the nasdaq. And we see, as well as the OTC markets in the states. Moving over to the right, we have our currencies in currently the sheets supports Canadian and US dollar. However, if you are overseas in Europe, for example, you can add your currencies here as well. In the exchanges. You wanna go over to Google Finance and figure out if the exchange, the urine is supported. So for example, if you're overseas in Europe and maybe you trade a new K, then you've got your own stock exchange and you can enter that symbol right here in the blank rows. Moving over to the next column, we've got currencies currently only have Canadian USD because those are the markets are, I'm invested in. Again, if you are overseas and you've got other currencies, you can place them down here further on in the column. Now if we shift over to the right, we've got our sectors. So these are the different sectors of the stock market. You can find information on this on Yahoo Finance. We've got to Yahoo Finance and just find an example. I'll show you what I mean. We're on Nike stock right now and you can see the sector that Nike plays him. They play in the consumer cyclical sector. And you can also add industry or you can satirize by industry if that's how you wanna do it. Here you can see all the current sectors I have from my portfolio, basic material, cash, finance, health care, et cetera. Finally, we've got risk in currently I've separated out into three different risk levels, low, medium, and high. If you want, you can even add other risk levels in there, but that's your personal choice. For me, I've only start to these three year. So knowledge jump over to an empty spreadsheet and I'll show you how I actually code this all up. First off, I'm going to change as a column width to be identical to the row width. So the row width is 21. So I'm going to change that right now. Now that's taken care of. Let's move on to the format of our equity table. So now I'm going to build up the equity table headings. So now we have all the table headings. Let's do some formatting. So now I format the headings, they will stand out. Now we can start building up the actual table, and this is what I'm gonna do. I'm going to add some borders all the way down to the very end. After adding the borders, are going to enter my first query symbol, which is just gonna be our cash position. And we'll do an example here where I do a stock. So I'm gonna add Air Canada or AC. Now for the exchange, this is going to be a column that you have to manually enter and every single time. So the exchange for a Canada is going to be the TSX. I'm going to enter TSE. Cash doesn't have an exchange. So leave this blank. Now for the currency column, I'm going to leave the cash currency as blank right now, or we're just kinda hard-coded to Canadian dollars. If you're American or if you're in another country, you can type in your currency manually. Now for all your stocks and stuff that are listed on Google Finance, you can have a formula that actually pulls a currency from Google Finance. This way you don't have to enter it manually every single time. Rather, you can just click on this blue dot and drag the formula down. What you're going to type in is the self for the exchange first, then you didn't and sign. This is basically a concatenate. The strings are basically put it together all into one string. And then after that we're going to enter a colon sign and followed by an enzyme. And lastly, we're going to select the equity symbol. And what this really equals L. If we were to concatenate this or just push the string together, I'll just type it out so you guys can see you would actually equal to TOC colon AC. And that's what this formula here would actually pop up into the Google Finance formula. But because we want this to be formula driven and not having to actually enter this every single time. This is why we use this formula here rather than hard coding and typing in TOC colon AC. So I'm going to delete that now and we're going to move on with the rest of this formula. Another tip, if you're stuck on what to enter into the formula, you can click on this question mark button. So the next thing that we want to type into this formula is the attribute. And the attribute that I'm looking for is TSC, colon AC, or air Canada's currency. So for currency you're gonna type in currency. And then we're going to close the bracket. And it's going to pop up the currency of TAC, TAC. Now for some formatting, we're actually going to center this and we're going to center the rest of these columns here. And that looks a little bit better. Next, acid type. So as the type is either cash or stock as I showed before. So isotype is actually a really manually entered server's going to type in cash and stock. Next we have the sector. And again, the sector is manually entered because we can't pull out from Google Finance. So for AAC, it's travel. In the cash column. I'm just going write cash. So cash, I'm just going to assign a low risk. Although some could debate that it's high risk if your country is printing more and more currency. But that aside caches generally lower risk. So we're gonna type in low. For Air Canada, I'm going to assign it a button, medium risk at this point. So the risk column again, you're going to have to manually assign just like I'm doing here. Now for current price, we can use a Google Finance formula. So we're gonna type in Google Finance and we can actually pull the ticker symbol, the same formula that we had in the previous cell under currency. So I'm gonna do that right now. Now that we've got the ticker, the attribute here is going to be the price. So this is going to give you the last closing price of the stock. So you can see Air Canada is $24.98. And if we go to Google Finance. We can see our Canada's currently trading at $24.98. Now the company name or again, we're going to use the same Google Finance formula, same ticker formula, and we're going to select the name. Now the order column is really just the order that you place the stocks in this table in case you want to use filters later on and you don't want to forget the order that you've entered the room. So if you're going to add more stocks, Just try your order down like that. Now that we've got this table pretty much built out, I'm just going to select these columns again just to some reformatting. So that's our first table built right now. The next tables that we're going to Bell, I'm just going to zoom through them and just show you what I'm doing. But in double time speed. Here we're going to resize the columns for the exact same width as the first column, column a. Now I'm gonna do some titles here. Going to change these all to center alignment. Now we're going to add some borders. Now if you want to get rid of these other grid lines, then what we can do here is go to the view and take off grid lines. So this looks a lot cleaner as a table. Now this might look a little bit ugly to sing all these order numbers here, but we can do some formulas which I'll show you right now to get rid of these as well, just write down these formulas. They populate every single time without you haven't had dried down every single time you enter a new row like this. So are we gonna do here is use a formula, an if statement. So the if statement will look like so we're going to use the a is blank, which basically this formula is saying if a certain cell is blank, then do this. Otherwise, do this. So we want to say at the equity symbol is blank, then we will also want to leave the cell blank. So here we're just gonna do another comma. So this expression here, the second argument is if this statement is true, then this will happen. Otherwise, it's going to populate a one. Now we can drag this formula down and likely this will also crashes well, because we don't want it to be a one. So we're going to do in the second column is actually say we want it to be this plus one. And now if we drag this down, you can see that if we add another ticker symbol here, like GMP, then you can see order three pops up. Now let's delete GMP and move on to the next cells. So again, we're going to use the same sort of format using the if is blank and we're just going to copy this. So I'll type it right here. If this is blank to equal this equal sign, then do nothing otherwise. Enter that. And now we can drag this formula down and we get nothing. And if I drag this formula down again, UCL is NA. But see if we add this if statement with a blank statement as well. You can see when I drag this down now, nothing pops up. Only if I type in the ticker symbol does something pop up. So you can see right away, these formulas are super powerful. When you delete this, they disappear as well. Lastly, we're gonna do the same thing for the currency column. And what we can do after this is just take these four columns with some sort of automation in them and drag them all the way down to the bottom slit. You never have to think about them ever again. Okay, perfect. So now let's do a test true here. If I enter ticker symbol like tell us for example, T. So you can see that the currency pops up and the current price and the company name pops up. But we haven't added the exchange, so we're going to add TSC and we can see it instantly changes to Canadian dollar, says this is Telescope operation and the current price is 25 dollars and eighty six cents. So we type into RCT again, $25.87. So looks like there may be a 17 discrepancy, but I'm documentary to Festival path. So now that I've shown you how to build up that database, Let's just label is the database. Now we'll move on to the next lesson where I built all the transactions tab. 5. Transactions Part 1: Data Entry: So we're building on the transactions tab. This is what it's going to look like. We're going to have symbol exchange, company name and so on. And this is just going to track each and every one of our transactions that we make in our account. Note that the transactions tab will be referencing the database tab. And I'll show you how we're going to do that right now. So to start off, we're going to do our headers just like last video. Now we're going to format this next winner freeze the top row so that when we scroll down, we won't lose this header. So now when we scroll down you can see the first row we'll never actually disappear. So we can understand when we're scrolling down through our multiple transactions in the future, what each of the columns represents. Here, I'm just gonna do some formatting things. I'm going to delete these rows just to make it look a little bit nicer. So now we're going to start populating the table and I'll show you some examples as we put in some starting positions. So first off, today's date is April 16th. So we're going to type in April 16th equity symbol, we're going to actually enter our cash position first. Exchange. There is no exchange, but we're going to add a formula here. So I'm going to type in the formula first and then I'll explain what the formula means. So the purpose of this formula is actually go back into our database and look up this equity symbol and find out what exchange it's n. So for cash, I've made an assumption which is why use an if statement? So if this column here in B column is equal to cash, which is actually this database B3 cell, then the result is going to be a dash. But if B2 is not equal a cache, then it's going to do this formula and return the value from VLookup. Now I'm going to talk about VLookup in what it does. Basically, VLookup looks with this key, which is B2. So it's looking for cash within this range of cells. Then it's going to look within that row and go to the second column and return that value. So if we do an example here with Air Canada, so AC, we can drag this formula down and we can see that it returns the exchange TSE. So VLookup, what it's doing here is looking for AAC within these columns B to j. So finds AC and AC is in the fourth row. So now it's going to look in the second column because there's a two symbol here. And it's going to return that value in the second column. So you can see the second column with in B2 j is cell C4. So that's why I returned TSC. Next, let's enter the formula for the company name. Again, we're going to use a very similar formula as we did for the exchange. So let's just try the silver and we're going to modify this formula. So again, we're going to use cell B2 and look for if it is equal to cash. If it is that we're going to have a dash sign. Otherwise, we're going to perform a VLookup. And again, we're going to use the same key when you use B2, which is the equity symbol. We're going to look up within the database of Nazi to k because CDK would be from here to here. We want to look up in the range of B to j. Now we don't want to return the second column because the second column is exchange, we actually want to return the company name. So the column is 1, 2, 3, 4, 5, 6, 7, 8 column. It. Now that we have just this as B2, we're going to hit Enter and we can see that a company named for cash is just a dash. Now we can see that cash did not return a company name because this statement was true, therefore entered a dash. But if we drag this down to Air Canada, then we can see they'll enter Air Canada, which is actually the eighth cell in the selection of B to j in the fourth row. We're just going to add a date here and copy and paste and assume today we bought this. Now we're going to enter the formula for the currency. And instead of using the esteem in, because cached does actually have a value in its currency tub, as we see over here, then we're just going to use as VLookup formula. So I'm gonna copy that here in pasted here. We don't want to actually use column 8. We want to end up using column three because from selection beta j, we can see that the third column over and voila, we can see a populated Canadian currency. And again for our candidate and also populated Canadian currency. Now what's interesting is, let's say I change this to USD on the database tab. So let me just show you that right now. So changing that to USD, we consider a change right here. I'm going to undo that though. So you can see that it's very easy to automate the spreadsheet if you change something in a database, it'll change here in the transactions tab. For the assay class column, we're going to use a similar formula using the VLookup because cash also has an asset class as well. So going to copy the formula, and instead of using column 3, we're going to find the asset class column. So the asset class column is column E or column four. We don't want this double equals, so let's get rid of that. And voila, we've got cash. We drag this down. We can see that Eric Cantor populated stock. Now Quantity Cost Per Share. These are two values that you're going to have to tune yourself because this is depending on how many shares of our company bought and the cost per share of it. So for cash, let's say a starting position is $200. So we're going to enter quantity of 1200. Here. We're going to format this real dollars and total costs. We're going to use an easy formula of multiplication, doing this cell times this, so here. And that gives us our total costs. Now I'm just going to show you an example for Air Canada, let's say about 50 shares at around $25 per share. Then we can drag this formula down and we can see that multiplied these two numbers to get a $125 as a total cost. The last column we have here is the action. So if you remember last video, we actually showed the type of actions we've got starting buying, selling, dividend, deposit and withdrawal. So we're actually going to references and make a drop-down menu. So to make a drop-down menu where you're going to do is go up to the Data tab. You're going to click data validation. In here. We're going to enter a range, our formula. So when I click over here to our database tab and just like all these cells. So what that does is it says that the selection dropdown is taking from all these different cells. Those are your options. And I hit Save. And you can see here now we have a drop-down tab. If I click on that, you can see all the different actions that we have. Now let's say we want to add an action because these aren't enough actions. So an action that you could potentially take is shorting. So we're going to type in short here. And what you can see here as a result is it's out of the option that you've shorted stock. But because I don't short stalks, I'm gonna take that off for now. And that's more of an advanced technique. Now that you've got this drop-down, you can just drag this all the way down to the bottom so you don't need to populate it every single time. Now you can see that for every line that you enter, all you have to do is click on this drop-down and select what the actual was. Somebody chooses the starting and chooses as buying. 6. Transactions Part 2: Formula's and Formatting: Now we're going to dry down all the columns. I have formulas. So we're gonna do the exact same thing as before. We're actually add another if statement so they don't populate anything. If there is nothing in that row. I'm going to drag these formulas down and show you nothing populates. If there's nothing in column B for that row. Now that I've dried the formulas down, when I enter a new row in the transaction I'm doing, let's say a bicycle or AC. You can see that all the data pops up and there's a few columns that we need to fill in. The final thing that I'm gonna do is show you how to format. This looks a little bit better in terms of color. So again, we're going to get rid of the grid lines because they're a little bit ugly. After that, we're gonna do some conditional formatting where we're going to scroll all the way down and select all the cells here. We're gonna go into Format and we're going to do conditional formatting. So we're going to be applying two formulas to the cells in order to get alternating rows that are white gray, white gray in terms of background. So the first one we're gonna do is, is empty. So the cells are empty, we just want them white. Now we're going to add another row such Arthur row is odd. It's going to be highlighted gray. So we're gonna start to color gray. And we're going to do a custom formula. And the formula that we're going to enter is odd. And the row. Now if the row is odd, we can see that a turn gray. Now let's add some more data here, just some more EC to see what happens. So you can see that these cells turn gray and the next row should be white. In the rural following that should be gray. Note that this conditional formatting, there's an order to the priority of these. So first off, that the cell is empty, it's always going to be white such that these cells here, because they're empty their white. If the cells partner m, t, Then the second formula will apply to them. So if the row is odd for that cell, then it's going to turn gray. Here you can see the formatting applies as I drag the dates down. And you can now see that every other row is colored gray as long as there's an item in that cell. Lastly, if you guys want, you can add a filter to this in try to play around with that. So all you have to do is create a filter. And this filter can do, let's say we add some other stocks here. So you can actually filter by things like a to Z. So it's an alphabetic order. You can filter by date. And again, a tizzy as really just from smallest to greatest or greatest, smallest. If you're going from Z to a, you can see what the latest date is first. In the earliest date is last. We can also filter by costs. So the smallest transaction to the largest transaction or vice versa. 7. Transactions Part 3: Rolling Cost Basis: So in the previous video we only had columns a to J. And I've added these columns K, L, and M to help us calculate cost basis in the portfolio summary tab. So what these columns, they're gonna find a rolling cost basis each time we make a transaction for that individual security. So I'm going to explain the formulas that I have instead of actually going through how to make these columns just because it's a lot faster. So first off, we're going to start with column K. And I'm going to explain each formula edge so that you guys understand what I'm doing and you can copy the formula on your spreadsheet. So this formula here is an if error statements. So basically if I get an error that I'm going to put a dash. And the reason why if error is because what I'm trying to do with this cell here is to determine the cost basis of this position prior to this transaction. So this row being my first transaction in the spreadsheet, I'm not going to have any prior cost basis for this position, which is why there's an error given. And therefore I'm putting a dash symbol. And the same with all these other rows here, they're all starting positions. And I had no previous transactions with these securities here. But now if we go into, let's say row 17 where I've had a prior transaction with Air Canada, which is in row 3, then we can see here it's actually performing this formula here, the VLookup, instead of putting a dashed line. And basically what I'm trying to accomplish with this VLookup formula is defined BY 17, which is the equity symbol in this range here, and reporting the 11th row back. So the reason why I'm looking for Air Canada in the previous transaction is because I want to get the cost basis at the time that I made that transaction, which is found in column L, which is a current cost basis after the transaction. So what I'm doing here with the sort function is a bit complex, but essentially I'm trying to flip this table upside down. So I'm searching from the bottom upwards. I don't want to search from the top because that's not going to give me the most recent transaction. So the sort function is if you guys don't know, there's a question mark here. It kind of gives you an explanation of each of the arguments. So what I'm doing with the sort function is I'm defining a range which is B1 to L 16. So it's basically these right here. Then I'm going to be sorting it by the sort column sum, taking column a and using this as a sort column because a is defined my dates. So I'm looking for the dates in a descending order. There's no other roads here that have any sort of useful information which I can sort by. So dates is really the bus. So this is going to do is take rho A1 all the way to a 16 and sort this entire range, the B one to L 16 based on the dates. And then it's going to sort them in a descending order, which is this argument right over here. 0 is for descending, one is ascending. Basically we've defined the range in as flipped upside down. Now the VLookup is going to be essentially going through this table here to find my B12 L 16 and a backwards fashion. And it's going to look for Air Canada, which we know here from looking at this is in row three. And it's going to return the 11th column, which is the L column, and it returned a 28.8. And if we see here, we indeed return $20.80. So the reason why I want the cost basis prior to this transaction is that we can calculate the cost basis that we had before the transaction. And the cost basis that we had after considering the transaction. Now I'm gonna go over column M before you go for column L and all makes sense. So column M is also calculating the current number of shares prior to this transaction. And that's how we get a cost basis prior to the transaction by multiplying the number of shares that we had by the cost basis prior to this transaction. And basically what this formula up here is doing is it's an if statement. If B 17 or the equity symbol and this line is equal to cash, it's just going to put a one for the current number of shares because cash doesn't have a number of shares. If it's not cash, it's gonna do this series of SUMIFS formulas. And what we're doing here essentially is summing the quantity section based on the action. If it is a buyer or sell, as well as if the equity symbol matches our current row. So we're going to sum, if we have a starting action and a buying action, and we're gonna subtract if we have a selling action. So the J1 to J 16 column is the action column. So trying to look for buy, sell, or starting. And you can see that denoted by this L3, L4, L5 over here, L3, L4, and L5. Then the second, third argument and the SUMIFS is looking at the equity symbol and seeing if it matches the current rows equity symbol. So that's returned three because we did by three shares of Air Canada prior on March 16th. So this is completely accurate. We have three shares at a cost pieces of $28.80. Now the column L over here is going to calculate the current cost basis with or including this current rows transaction. And the reason why I didn't change is because I did a cell action. Basically the formula works like this. It's an if statement checking case 17, if it's equal to a dash, this means that we had no prior transactions with this equity symbol. And we're just going to take this cornrows cost per share and plop it right in the cell as a current cost basis after this transaction. Otherwise, if it did find that we have a cost basis prior to the transaction, we're gonna go through the series of if statements. So J 17, we're looking at the action. We want to see if it's a buy-sell deposit starting, et cetera. So JSON teen equals to L four. And we know that Alpha over here is a buy. And what we're gonna do is we're gonna take K 17, which is the cost basis of the prior transaction, multiply by the number of shares that we had prior to the transaction. And we're going to add the cost per share and the total amount of shares that we had during the transaction because it is equal to a by. So basically this will give us the total cost that we paid for all of our shares that we currently own up for the transaction. And we're going to divide it by the total number of shares that we now own after this transaction. So let's taking G 17 plus the 17 cell and adding them together. So this will give us a new cost basis. But notice that this line here is a cell. So that's why my cost spaces didn't change. So let's next if statement is going to target the cell action. So J 17 is equal to a cell. I'm just going to put in case 17, which is a cost basis for the prior transaction. Because when we do a cell action that doesn't change our cost basis, but instead we do not a loss. Now after this, we're going to target the withdrawals and deposits. So J 17 is equal to l seven. We know the L7 as a deposit. Then wanted to take case 17, which is the cost basis of the prior transaction, and this only applies for cash. We're going to add that with the cost per share, an all cash transactions, you can see the cost per share and the total cost for the exact same. And if we have a withdrawal action in JSON TIN, then it's going to take case 17, which is the amount of cashflow deposited prior to this transaction. And we're going to subtract the amount of cash that we're withdrawing, which is in column H. And if it's none of the above, them are just kinda put k 17, which is the cost basis for the prior transaction. And that's essentially how column K, L, and M work, which is to calculate our cost basis. As you go through each and every transaction, you can't just add the starting and the bi's and subtract the cells in order to get your cost basis. Because when you do a cell auction, you have a net loss per net gain. If you're a purely just to sum up the starting by and subtract the cells, then you would have an inaccurate cost basis. And the next session we're going to be going over the portfolio summary and how you can actually construct that. 8. Portfolio Summary Part 1: From Transactions to Positions: So now we have the portfolio summary sheet here. I've already added the titles in there just because I'm not going to waste your time. You can copy them down. You can see I've scroll all the way down to column B and I've got a bunch of different titles that we're going to fill in right now. I'll probably split this up into a couple of lessons. First off, we're getting at the equity symbol. And this is all gonna be formulas based by the way. So you can just take one single cell and just drag all the formulas down and get all your cells populated without having to really touched anything else except for entering the first row. So for the equity symbol, we're gonna do is we're going to use an equal symbol and go over to the database. We're going to click on the first row in the equity table. And I hit Enter and we can see that cash is populated. Drag this all the way down. You can see other equity symbols are populated here. Now we're going to fill it the number, exchange, company name, sector, and asset class using pretty much the same formula that we've been using before. It's an if statement. If B2 is blank, then display nothing. Otherwise, we're going to do a VLookup on B2. So this is a key we're looking for. We're gonna go to the columns of B to J on the database tab, which are over here, B to j. And in the B column we're looking for the B2 symbol here, which is cash. Obviously caches here, that we're going to go to the ninth column and return that value, and we're not going to sort it, so we're going to put false here. The ninth column over here is the order which is the j column. Now what I can do is copy and paste this formula into the exchange company name sector as a class. But we're going to change the index which are pulling the data from. So for the exchange, the column are pointing from his column 2 for the company, the column that we're pulling from his column eight for sector, the column that we're pulling from is column number five. And for asset class reporting from column number four. Now what I can do here is highlight all these cells and drag the formula down. And what you'll see is base scholars data is being populated. I notice that my fonts are little bit different, so I'm just going to quickly change it right now. Okay, so that's better. Now we're going to work on the quantity and cost per share. So I'm gonna type in the formula for quantity and explain it. Okay, so this formula looks a little bit complex, but I'll break it down for you so it's a lot easier to understand. First off, we have multiple if statements nested together. The first if statement says if B2 is equal to cash, just put the quantity of 1. So every time we have cached in the B column, the quantity will always be one. Otherwise we're going to do another if statement here. If B2 is blank, we're going to put nothing in this quantity cell. Otherwise, if it's not blank, we're going to do this whole series of sumifs. Again, the SUMIFS look a little bit daunting. However, if I break it down, I'll make a lot more sense. So it could take each sub f and look at it individually. So for example, row 3 because it's not cash, we see that B3 is care Canada. So first of all, we're going to search through the transactions tab in row be to look for all the Air Canada lines. On top of that, we also have to be looking for if JJ or can column j is equal to database L4 and before L4 here is a Bye. So we need both these conditions to be met in order for that quantity in that row to be added to the summer. So here we can see in row three by column B is AC, so that meets the criteria. However, INJ, the action is starting not buy. So therefore this row, we're not be counted in this initial some if statement. But if we move further down into the sheet, we can see we do have a binwidth, Air Canada on line 21. So Air Canada matches in column B as well as in column J and matches the action of buying. So basically this sum of Steven here equals to 2. And if you didn't believe me, I'm just going to show that out to you. If we do that, it's equal to, to take with a dollar sign. Now the second sum of stamen here is looking for again, each row that does have in column B, it's equal to AC as well as the transaction. Or if column j is equal to starting, because starting as an L3 here. So basically on this transaction a, C as a starting transaction, this quantity would be added to this summary statement right here. Now this last summer statement over here is actually a minus. So this is for all our cell actions. So whenever we have the column be equal to this equity symbol AC, as well as the action equaling to a cell. We're going to subtract that. So here on the transactions tab you can see that we have another cell for our camera. So here in column B it's AC as well as in column J, it's a cell, so it's going to be subtracting one. So if we look at this, we're doing 3 plus 2, subtract 1, and the answer is four. So if I drag this down, this will fill in all the quantities taken from the transactions tub of my current stock portfolio. And I can even take these and drag them all the way down so that you can see all the different positions I have in how much quantity I have. So as you can see, these formulas are very powerful because it doesn't require me to go into the transactions tab and update the quantity every single time. The formula will automatically do that for you. So now for the cost per share, we're actually gonna go over back to the transaction tab, can steal this formula here as a template. So when we copy this and then we're going to paste it right here and modify it. So first thing right off the bat, we're going to change the key that we're looking for. We're looking for B2 here. For the second argument, we're going to use the sort function, but not on any data on this sheet. We're not actually go back to the transactions tab. So delete that first and we're gonna choose Columns be all the way to L. For the row. We're going to delete this and we're going to use row a. Next. We're going to use row B for this one over here. And the rest of this is pretty much correct. We're looking for a descending order as well as we're looking for the 11th column. If we found the key in that row, and we don't want this sorted, so we're gonna leave this as false. If there's an error, we're just kinda place a dash. So once we hit Enter, we can see that the cost per share for cash is $400. Now if we look at the last row of cash from the bottom-up, you can see that in column L or the 11th column is 400. So now if I drag this all the way down, we can see the cost basis of all the shares based on this transactions tab and the verifier that we have this cartridge done, we can just go through a couple of these rows. For Air Canada, the last transaction that happened was in row 21, and the current cost basis in that row was $27.85. Right here we have 27 dollars and eighty five cents. Now let's just pick another row to indeed validate that we have done this formula correctly. So in row 10 we've got real can or RER, and it's got a cost per share of $19.82. Now if you look at the last row where we have real CAN, this is basically on Row 10. It's $19.80. So it looks good, the cost per share. Next we're going to find the formula for current price. In. First thing we're gonna do is an if statement. And if the equity symbol is equal to cash, then we're going to just display the cost per share. If it's not, the more we can do here is actually using Google Finance formula. So Google Finance, and again, with the same tactics that are used in less than one or two, we're going to take the exchange, do an ampersand to colon, and then do another ampersand to concatenate. And we're gonna do the equity symbol. And then we're gonna go over here and do price. So this should report the current price. But for cash, because this is not actually on the stock exchange. We just reported the cost per share. And if we drag this down, this should update with all these different equities and the current price right now as a film, we're going to change this to a dollar sign. And there we go. We've got the current price of each of these stocks. And all we have to do is enter one formula and drag it all the way down. Now for book value, this formula is very easy. We're just going to take the quantity and multiply it by the cost per share. And this will give us our book value. Dragging this down and voila, you've got your book value. Now for market value, It's again, another pretty easy formula. We're going to take the quantity multiplied by the current price and hit Enter and drag this formula down. And there you go. We've got market value. Very easy again. Now, profit and loss, I should probably clarify that this is unrealized profit and loss. So the unrealized profit and loss is going to be the market value, subtract the book value. So the formula is as follows. Market value subtract the book value. And we can track that down and we can see our unrealized profit and loss. Now I'm going to add some formatting to this column L, so you can easily glance at it and use color codes to determine whether you have an unrealized profit or an unrealized loss. The format that we're using here is actually not selectable on this whole list. So we're gonna do our own custom number format. And this custom number format is basically looking for a positive number. If it is, it's going to change the text to color 50, which is a screen. And if it's a negative number, then it's going to change this to red. It's going to hit apply. And you can see right away that our unrealized profit and loss column has been updated such that the text color changes from green to red. If we go from positive to negative. Next we're gonna do our returns. So the return formula is very simple. We're gonna take our market value, subtract the book value, and put brackets around that. And then we're gonna divide that by the book value. Now will give us the percentage return. So we're gonna do this as a percentage and just drag this all the way down. Again. We're going to format this slow. You can tell that if it's a positive or a negative percentage return. So going over in a custom number format, we can look down here and just see if they have something available to us. And from the looks of it, it doesn't look like we have anything that suits your needs. So I'm going to create my own custom format. It's the custom format that we're going to use. Red here is again similar to the other one. So it's gonna look for all the positive numbers and it's going to change it to color 50. And the negative numbers, it's going to change it to red. It's also going to add a plus sign or a negative sign depending on if it's a positive or a negative return. The next column is one week performance. And I'm going to pull this formula from another sheet and I'll modify it for our sheet here. So b2 is not equal to cash and it's actually an equity symbol that we're going to find the one-week performance. So we're gonna take I2, which is the current price of the security, and subtract the price of the security or weak ego, which is this formula right here. So basically we're going to go into Google Finance intake, not d2, however, C2. And then we're going to concatenate that with the colon. And take can be too as a symbol. And that should give us our exchange colon equity symbol. And we want to find the price of it seven days prior to today. So basically this is the date. So today, right now as I'm filming, this is April 17th. So if we subtract seven days, it gives us April 10th, 2021. So the reason why we have the index here is because this formula over here that Google Finance one will actually return multiple cells. So I'll show you what that looks like in the cell next to it. So Google Finance reports for cells because it's going to report the date and the closing price for that security. So that's why I wanna do index to two over here, which is basically what these two numbers mean for this index formula. So it's looking for the second column in the second row. Next, we've got this other formula here that looks complex, but again, it's pretty much the exact same as this minus now we have to actually adjust the cells. So I'm gonna do that right now. So this over here is the exact same as this over here. So essentially what this formula right over here does is it's going to take the current price in I, subtract the price seven days ago and divide that by the price seven days ago to give us a one-week performance. So if we drag this actually all the way down, we can see the one we performance, but all of our stocks. Now you don't want this in dollar terms. We're going to change the formatting. I'm going to change it to the percentage. So here you can see the one we performance that all my different stocks by just entering one formula and dragging it down. 9. Portfolio Summary Part 2: Metrics and Other Stats: All right, Now we're going to be doing some more metrics towards each of our positions. One thing that I forgot to do is I'm going to actually freeze the first couple of rows. So we're gonna do view freeze and we're going to freeze up to two, sorry, columns. So now that when we scroll over, we can still see your equity symbols. And we're gonna minimize column a because we don't really care about the number. Now for the one-year chart, we're going to be getting an actual line graph that's in this cell. And it's pretty cool. And the formula is as follows, which are copied from another spreadsheet. We're going to have to adjust it for ourselves in so that they all match. It's going to Justice to B2. And I'm just going to copy this. Move it over here. And that should essentially work. So if I scroll this down, you can see the one-year charts loading. And that's pretty snazzy, isn't it? Now I'm going to explain the formula and how it works. So we have an if statement here. If b3 is not equal to cash, then we're going to try to get the graph line. Otherwise we're going to do a dash because cash doesn't have a one-year chart. So this formula for getting the graphine is using sparkline. So to explain this, I'm going to click on the question mark so we can see what sparkline actually does. So sparkline creates a miniature chart contained within a single cell. And basically all it's looking for is data. The data that we're going to provide sparkline is a historical prices for 250 days prior to today for an individual security. So what this is going to do is going to use a Google Finance formula. Look for accuracy symbol b3, which will also have to add the exchange to. So let me do that right now. We're gonna do an enzyme and we're going to do the exchange as well as we're going to do an and sign and we're going to do a colon. The Google Finance formula is going to pull up prices from 250 days prior to today, all the way up to today. So if you want to see what the output of this formulas, let me just drag this over and we can see. So you can see there's Google Finance formula is going to populate this table where it has a date column and a closing price column. And it went back all the way to May 4th, 2020. And it gives us a closing price of $17.63. And the last working days date is on April 16th. And the price of our camera was $24.86. And basically what the workday here is, it dismisses all the weekends. So we're actually going to take this back 365 so we can get a full year's capture of data and delete this. Drag this down. The reason for this index is because we want to pull only the second column of data. We don't want the date data. And the last part of this formula is how you're going to format this graph. So you can change the color of it to green and I can change the chart type to line. So if I change the color to red, for example, we'll see a red line show up. So I'll change it to green just so our consistent. This is how you get the one-year chart for all your stocks. So you can get a relative idea of how the stock price movement has been over one year. Now for the dividend yield column, I haven't found a way to actually use Google Finance to find the dividend yield. However, we can pull from Yahoo Finance just reading the HTML texts. So this formula is saying is that if b3 is not equal to cash, then perform the search for the dividend yield from Yahoo Finance. Otherwise, if it's equal to cash, them put a dash in the cell. And breaking down this formula, what we're gonna do is first off, find the website which we're pulling the data from. Yahoo Finance. Here we're going to concatenate all this text which is equal to what I have here in the URL. So you can see on this page, It's the Air Canada page. And we're going to be pulling data from this table here. Now moving out one formula further, we're going to be importing the HTML from the site. So I just copy and paste this. Let's just see what the openness. So the output is this table right over here, and it pretty much looks exactly like what we have over here. This is the second table. So what this formula is saying is, is pulling the table that's labeled number 2. And this is the second table or in over here. Moving on one formula further to this index, we're basically looking for row six, column 2. So from the HTML result, row six, column 2 is an a. Next Wednesday is a split function which basically takes the text within the cell and splits it based on wherever there's a space. So this would be split into two cells where one cell reads NA and the other cell reads bracket and a bracket and from the split function that yields two cells. So we're going to have to index that. And we wanna take the second cell and we want to take the absolute value of that. And if there's an error in any of this formula, we're just going to return a dash symbol. The reason why there's an error here is because the absolute value of NA is going to return an error. So if I just go over here and take this and just copy it, and we're going to paste it over here. You can see that we get an error. Now if I actually remove the absolute value and see what we get from here, you can see that it returns a cell and you can't do an absolute value of text. So essentially that's what the dividend formula does. So via drag the different formula down. Now we can see the yields for all the different stock to actually have a dividend yield. And we're going to change the formatting to percentage. So the formula for yearly income is as follows. If P2 is equal to a dash, then there's no dividend yield. So again, we have no yearly income. Otherwise, if it's actually has a number in there, then we're gonna take P2, which is dividend yield, multiply it by the current price of the stock, and multiply that all by the quantity or the number of shares that we own and that security. And that should give us the yearly income that we can get from this stock. So we draw this all the way down. This should actually generate some dollar values. So you can see from Algonquin Power corpse or EQN, every single year I'll be earning $6.21 based off of a dividend yield of 3.85%. The next column we have is whether the stock is a different stock or not. So this formula is fairly easy. The formula is as follows. If column Q is equal to a dash, then no, it's not a dividend stock. Otherwise, if there's a number in there, then it is, yes. We're going to drag this down and we can see that all our stocks actually have a yearly income. We have yes. And the column right to it, otherwise it's going to be a no. Moving on to that column, we're going to be summing up all the dividends that we've collected from each individual stock. For the dividends collected column, we're going to use a SUMIFS formula. And we're going to be summing the total cost column based on two criteria. Again, the first criteria is that this column here, the b column is ERA equity symbol. And the second criteria is the action is a dividend payment reform at this. So dollars, I'm going to drag this all the way down velocity. And I need to do with this formula is anytime a dollar sign. So this is always going to reference cell L6 as I drag the formula down. Now as I drag it all the way down, we can see that none of our securities have actually yielded dividend yet. But I'm just going to fake it here and show you what happens if we do collect a dividend. So I faked it such that we have a different from telescope twenty-five dollars and 50 cents for a single share and will lie. You can see right here on the telescope them until you've collected a dividend of $25.50. Now for the risk column, we're just going to copy a formula back here in the sectors. And we're going to modify to suit the needs for the risk. So if we go back to the database tab, we can see that risks are in column number 6. So we're gonna change this index here to six. Drag the formula down, and we have all the risks for stocks. Lastly, we've got the portfolio percentage for each security. We want to know how much of a percentage of the portfolio and mix up. So we're going to use the sum function and sum up all the market values. So we can go the tonal value of a portfolio and we're gonna take the actual market value of the current security that we're looking at and divide it by that sum, this formula down and we can see the percentage of the portfolio is populated. And we sum this all up, then we should get 100%. And yes, we do get 100%. The last thing that we would do is find the P E ratio for the PPE column where they go back to the current price copulas formula and modify in the P column. So instead of having price show up, we're going to actually put p0 and hit Enter. Again. If we've got cash, let's just actually change this so that we put a dash. Now, drag the formula down and we should get all the Ps of our stocks. However, there are some stocks with an NA. So what I'm gonna do here is modify this formula. We were going to use the IFERROR statement. So this if statement is going to check if this statement actually returns something or not. If it doesn't return anything useful, then it's going to actually display unprofitable. So we can see that right here, Air Canada is currently unprofitable. They don't have a PE ratio. Drag that down and we can see all the companies that don't have a PE ratio have unprofitable listed in the P column. Now for all these cells, I'm going to do some formatting here and just have them all center focused. I'm going to switch your company name slots left aligned, and drag this out a bit more. And that's pretty much good. The last thing that we're gonna do on this spreadsheet is actually change the cost per share for the cash column. The reason why we're doing this is because every time we do a transaction by cell, withdrawal deposit or get a dividend to cash position will change. So we're going to delete this in setup a new formula. So we're gonna set up this. If B2 equals to cash, we're going to be using a series of some Fs to capture all of our transactions and change the cash positions based on that. So the first argument and then the sum f is the range. And this is the range which is tested against this criterion. So we're going to go over to the transactions tab and find the range. We want to know if the actions range is equal to a certain action. So we're gonna do the buy action first. Change this to dollar signs. Slow doesn't change as I drag the formula. Then what we're gonna do is some, a certain column, and that column is over here, column number I. And this is the total cost. And we know from database L4 being a bi, we're actually going to subtract that. So every time that we see a reaction, we're going to be subtracting from our cash position. Now let's copy this formula and paste it over here and modify it, because it's pretty much the same thing. All you have to change as this database. So we know that L4 is equal to a by then we know that alpha is equal to a cell. So let's change this to L5 here. And it looks like it doesn't like this negative slope me just change this. L5 is a cell. That means we're actually gonna get cash. So we want to add that. Now we wanna go to L6 and its dividends. So every time we receive a dividend, we're going to be getting some sort of cash. So subtract that from there and make this L6. Next L7 we see as a deposit. So that's going to add cash to our balance. So changes to seven. And lastly, we have withdrawal which is going to subtract from our cash position. So that's L8. And this gives us our cash position currently. So you'll notice here that our cash balance is actually negative flowing over the transactions. We know that we have $400 of cash based on these two lines here. And we do not spend $400 on these stocks. And that's because the formula didn't actually take into account our starting position for cash. So we're going to have to add that into this formula here. So we're gonna do plus some F's. And the sum range is going to be the total costs. The criteria that we have, we have two criteria is, is time. First we want to know that column j is equal to starting. The next criteria is that the equity symbol must equal to cash. Now you can see our cash position is reflective of our starting cash position plus all the other transactions that happen after entering all the starting transactions. So this is how you can create a portfolio summary based on all the transactions and every time you add a new stock, I have to do is just drag all the formulas down to the next cell. I don't have a 14 stock, but if I just totally row 14 and go to row 13 and pull all these formulas down. And notice I wrote 14 populates instantly. I don't need to enter anything else. 10. Portfolio Visualizations: Starting off the portfolio visualizations, we're gonna have a quick summary chart right up here, just giving us some total portfolio metrics. We're gonna do some quick formatting first, and then we'll fill out this column right here. First off, we're going to calculate the total value of our portfolio soon as typing an equal sign over the portfolio summary tab. And we're going to type in sum that we're going to select all these market value sells to add up each individual's positions market value. This will give us the total value of the portfolio currently. Then we also want to do the total book value. So what we're gonna do here is a similar formula. We're going to sum over the portfolio summary, some of the book value column. And with that we ever book value and we have the total value of our portfolio right here. Next year, the total P&L. And there are various ways you can do this, but the simplest way is just taking the total value subtracted by the book value. We're gonna apply some formatting to this PnL slow we can easily tell at a glance with our portfolio is up or down. So if it's red, it's down. If it's green, then it's up. Percentage return. We're going to be using the total P and L and divide that by the book value. We're going to change this to a percentage and that should give us a total percentage return. And currently this portfolio is down by 0.07%. Lastly, the total yearly income, we could also label this the total yearly dividend income. And here we're going to make an equals sign. We're going to sum and we're going to select a dividend column right over here. And that gives us our total yearly dividend income from this portfolio currently. Next, we're going to make three different charts. So three charts, but we want to document here is the percentage of the portfolio that each position makes up. We're also going to see the percentage of our stocks that are dividend stocks versus non-dividend stocks. In lastly, we want to see the percentage of our portfolio that is low, medium, and high risk stocks. So these three are alchemy pie charts. So we're gonna go up here and insert a chart. And for the chart we're going to select the pie chart. Now we're going to add a label and we're going to have to select a range. So first off, we're going to do the risk chart. So I'm gonna select the risks that we're going to add another value here for the values and select the range. And we're going to select percentage of portfolio. We're going to click the aggregates and the aggregates, although low risk, high risk and medium risks into one slice of the pie for each risk level. We're going to change the title here and call this the risk allocation. And that chart is done. Next, we're going to insert another chart and create another pie chart here. So we're going to select again pie chart. We're gonna go to Add Label, select a range. We're gonna go over here to the dividend stock column, whether it's a dividend stock, we're not going to select the other range. And again, we're going to use percentage for portfolio. We're going to click aggregate analysis will tell you how much of your portfolio is a dividend paying stock versus not. So this portfolio is balanced, pretty much 5050. Again, going over here to the customization and we're going to add a title portfolio dividend, stock percentage. For the last visualization, we're going to create another pie chart showing the breakdown of each of our positions in how much of the pie each position has. So here I'm going to select the label, going to go over here to the equity symbol, select that. And then we're going to add a value here. Again, we're going to select the percentage there when they go to the customization, add a title and say portfolio breakdown. So here I've showed you how to create three different visualizations that are all pie charts, as well as a portfolio summary chart. There's definitely a lot more data that you can play around with here and create your own visualizations. So let you guys explore and use your own imagination for what visualizations you want for your own portfolio. After all, this is all personal finance. So every person is different. Maybe you want to be able to break down your portfolio in terms of sectors, in how much percentage of allocated to each sector. 11. Function Explanation: VLOOKUP: So I've been asked to do a bit more clarification on some of the functions that we use. So here we're gonna go through the function VLookup sofas, VLookup function. I'm going to do an example using this table I have here to do a lookup for the current price of a certain stock. So the goal of the VLookup is to find the stock in my table and then find the current price associated with it without doing an if statement and iterating through all the cells. So my VLookup signals start with VLookup. I can hit Tab complete to get VLookup in from here, I'm just going to open up the question mark to show you or so you can follow along a little bit more easily. So the first thing that we're going to enter is the search key. And essentially the search key is what are we actually looking for? So if we're looking for the symbol Ac, I'm going to type in AAC. So I can type in AC here, and that's my search key. So VLookup will go into my dataset and look for AAC. If I don't want to use ACF, I want to reference itself. I can actually click on a cell, insert and click before. So VLookup will go to cell B4, finance content, which is AAC user as a search key. So I'm just going to go back to AC because we don't want to click a cell in our table just to look up in a table what the prices. So if you were looking for a C in this VLookup, the new type in AC over here. The next thing that we're going to do is type in the range. So this is basically the range which we're looking in or the entire table which we want to look for are here. So if we go down here to the description, the range to consider for the search, the first column. And the range is search for the key specified in the search key. So this is basically telling me that the range that I specify, it's going to go down the first column only and look for this key. So I'm going to specify my range as this entire table here. So we're going to drag across from cell B2 all the way to J 16. So now that I've defined my range, VLookup is going to look for AAC in column B because that's the first column inside my range. Then the next term here is the index. So what is the index? The index is a column index of the value to be returned with the first column and the range is numbered 1. So essentially what happens here is VLookup look for AAC in column B. Once it finds a C, It's kinda look, or it's going to take this index input and return the contents of that cell within that row of the specified index. So that's little bit more confusing. Let me break this down a little bit further. So let's say a comma index as a three or sorry not three. We're going to use the current price as the index number. So that's index 1234567. So I'm gonna put down index seven. So what VLookup is doing here now is going down row or column B, looking for AAC, then returning the seventh column in the row where ACS found. So once it finds a C, It's kinda go over to column number 71234567 and return this value. Now the last thing that we need to enter is the is sorted. So is sorted indicates whether the column to be searched, the first column and a specified range is sorted, in which case the closest match the search key will be returned. So this is an optional thing. We're going to put false here because we don't want to search or we don't want to sort. So as you see 17 until the entire formula. Now, the VLookup has returned a C with a value of 25, 0.07. So it's searched this entire table, found that AC was in row 4, doesn't return for, but it returns the seventh index because we specified seven over here. So returns the price. Now if I do a different example and switches AC key to, let's say I switch it to VIV. Now at returns 92.56, you can see here it went to row 15, Solid V of v was there. And I return the current price of 92.56. I can also change up the index. And let's say we want to return the rest, which is in column 6. So no changes to sex. And you can see right away it changed to what the output is low. Now what if your value is not actually in VLookup? So let's put another value like No. Then you can see it returns an error. And the error reads, did not find the value NO and VLookup evaluation. So this is a summary of how VLookup works. 12. Spreadsheet Formatting Text and Cells: Lesson I'm going to go over how you can do some basic formatting to yourselves, like I have on the sheet here. So the first thing I'll go over is how to get rid of grid lines. So here if we go up to View, you can see, you can click on this grid line option. So clicking on it will add these gridlines back, which you can see here are these gray lines to remove them, go back to View and unclick them. The next thing we're going to go over is how to make your cells with different color for the background and cells like I have here on the column headers. So for the column headers, the reason why I made them a dark gray and how the texts as white is just to have them stand out more. If they were just a plain texts like this, you wouldn't know that the column headers. So that's why formatted like this. So the way to get a background or a different color, background yourself as you go up to this paint fill color icon. So you click on this icon and there's a whole palette of colors. I chose to use a dark gray, not exactly block, but that's just my personal preference. If you want, you can change it to red and it changes it to read like so. But remember when you're doing this, you want to make sure the contrast between the text and the background is, and there's enough contrast there. Otherwise this can be very difficult to read. So that's why use an almost blocked. The next thing I'll show you here is how to format text. So I just use a default tax, which is Arial, and you can find a drop-down menu here for all other types of texts. You can scroll through the list and select what you like. But personally, I just stick with the default. You can also change the size of the tax here. So by changing the 14, it becomes a lot larger. But I keep mine to about 10. For those of you with poor eyes, maybe you want to change it to 14 or even 16. Next is a few other icons here which we can change. Different things about the texts like the boldness are having italic or even adding a strikethrough. So here I've bolded all my column headers. And if I unfold them, you can see they've changed back to your skinnier type of font. So I'm gonna keep him a bold. You can also italicize your texts here by clicking this. And you can add a strike through here. And then this item over here is a text color. So I can change the text color to red if I wanted to. I can change it back to white. Changes the purple. So that's how you change the text color. Another classic option that's not listed here is the underlined. If you want to find that you can go up to format that you can click on underlined here. There's also a keyboard shortcuts and i'm, I'm OK. And I use Command B to do bold, command I for italic, command new for underlying, and Command Shift X for a strikethrough. Now I'm gonna show you how you can create the grid line over your chart. So you can go over here to this borders icon and then click on the borders icon. And this will show you all the different type of borders that you can add to your selection. So in order to apply a border, you must luck cells. So if I select all these cells, I can apply a border. So the first option here is applying all borders. So basically every single cell will get a board of its own and only apply a border to the inside. You can click inside borders. If you want to erase all the borders, you can clear all the borders. And there's also other options to do bottom borders, side borders loves top borders, as well as inside vertical borders and inside horizontal borders. I'll show you quickly a few of the options so I clear all the borders. I can do only insides seeking to the exterior don't have a border. I cleared again and only do the interior horizontal, then you can get this sort of effect, interior vertical as well as just the exterior. And you kinda get the whole gist of it. You can change the border color on the right over here, as well as he can change the type of barter. See, I've dotted lines and we can now double solid lines. You're going to have thicker lines and thinner lines. Now, sometimes you might find that your taxes either out of the cell and disappearing or it's just on the edge. So if you want to automatically have your cells resize to the largest width of texts for the cell, you can always double-click here. So I can resize to that. And if you want to manually adjust, just click over here and drag. Same thing can be done for the columns. You can click and drag or you can double-click to format to the width of the tax or the height of the text. In the case of the row. 13. Function Explanation: SUMIFS and SUMIF: In this lesson, I'm going to teach you how to use this functions sum F and some if's. So first off, starting off with summer. So you type in the function, so myth and out of bracket sign. And here we can see those three different inputs to the function. First is a range, then we have a criterion, and then we have a sum range. So the first range is the range which is tested against the criteria. So this is the range that we're looking down. So for this example, we're going to be going through this equity table and we're going to be looking for all the columns, or we are going to be looking at the risk column. And every time we see a medium risk, we want to take the current price and add that to the total. So basically what it'll look like is we're going to have the summer function add 25, 0.07 plus 20.18 plus 57.24 plus 20.52. Obviously it's kinda useless as some current prices in the context of a portfolio tracker. But this is purely to show you what the function sum F does. So if we do equals some F, we're going to select a range which we're looking for the criterion, which is risk. So we're going to look down this column and we're going to criterion. Our criterion is going to be a must equal two. And we're going to do an ampersand sign, and we're going to select Medium. Another way that you could write this out is basically just two equals medium and equal the same thing. Basically all I did earlier was take this string, which is basically the equals sign, and add the cells contents to it at the end of the string. So this, so this equals n V4 would also equal the same thing as this. So this is our criteria now basically want the cell to equal medium. And if we find a cell that's equal to medium, then we're gonna go over to the cell in the same row under the sum range and take that value and add it to the total sum. So for some range, it's going to be h. Now I close the parentheses and we get a value of 123, 0, 1. Now if we do it manually and just take all these numbers and some of them, we should get the exact same number as a sum f function. And voila, they're exactly equal. Now let's go over the sum Fs function, some Fs. And we see here it says it's SMS arranged depending on multiple criteria. So in this example, we're going to search through this table here, looking for every row, the hazard risk that's high, as well as the exchange is on the TSO E. And we're going to sum the current prices of those stocks. If we were to manually do this, we can see that the risk is high for row seven and it's on the CV. So we would discard this value. The next time that we see a high risk is over here in row 9 and it's traded on a TSE sewer. Take this value and submit to our total. And further down we see another high risk stock over here. And the exchanges also TLC. So in the end, the value that we should get from the summer statement is 150.50 plus $6.95. So we go in here and start typing in this function. We can see the first signal they require is a sum range. So the sum range is basically which column we're going to be summing. So it's column H. Next. Where are the first criteria here? So the first criteria range is going to be in biology. So we're going to look in risks and look for a high risk. So clicking on this range, the criteria for this is a must equal to high sorts of first range and criteria. Next we're gonna go to our second range, which is the exchange selecting that range. And then we put a criteria, it must equal to TSE. Now, that's essentially our to do to fellow the SUMIFS. If I close this bracket and hit Enter, we get $157.45, which is essentially the addition of those two cells that I pointed out earlier. We just take them over here and add them up. It should equal exactly what we have in the summer statement. And that is true. So that's how you do some F's in some F. 14. Function Explanation: GOOGLEFINANCE: In this lesson, we're going to be going over the function Google Finance. So to find the function Google Finance, you can just type in equals QC and hit Tab for. So for Google Finance, it fetches securities information from Google Finance. And there's a bunch of different things that you can do with Google Finance. But the best place of 30 we can do is going over to support dot google.com. There's so many things you can do with Google Finance, such you probably would rather go to Google and search up Google Finance and click on this link over here. And this actually laser all the different things that you can do with Google Finance. So the syntax word is, you have your ticker symbol As your first argument. Then you want to select an attribute, and this is optional. It's priced by default. Then you can put it in a start date and end date, or a number of dates or an interval. So everything in brackets are all optionals. So for all these optional attributes, you don't need to actually put them in. But if you want to further refine what you're doing and you can add them in as well. By default, attribute is price, and the rest of these are, don't have a default at all. You don't actually need them. So going through the list of attributes. So attributes, these are the following real-time data attributes that you can pull from Google Finance. And you get the price. It's delayed by up to 20 minutes. You can get the open price of the stock or a security or whatever you're searching up. It can even be a currency and get the high, the low volume market cap, et cetera. Other attributes that you can do for historical data. For historical data, you're going to need to enter in the start date and an end date or a number dates. So you can get the opening, the close price, high price, low price, the volume, as well as all of the above. I don't really search of mutual funds or GFA. I typically don't search a mutual funds on Google Finance. So personally, I've never actually used any of these. Then if you want to turn a start date, it's optional. And remember our start date is used for these attributes here. And for historical data. You can put in a date and the data just specified date formats. So probably just the day number, the month, as well as the year. And then over here, the next argument that you can put it in as an end date or number of days. So you can put the date when fetching historical data is to end or the number of days from the start date for which to return the data class. So you can put in an interval which is the frequency of return data, either daily or weekly. You can also alternatively specify it as one or seven. I don't believe you can use to to specify every other day or three to specify every third day. So going into Google Finance here, we're just going to do some quick examples. We're going to type in the symbol. And every time that you want to symbol, you should go over to Google Finance or go over to Google and search up a symbol. So if research up, Let's say we want if I Air Canada. So you can do Air Canada stock price. And we can see the symbol for Air Canada and a Google Finance, this TSC, colon AC. You can directly copy that from Google Finance and paste it into your Google Finance spreadsheet or the Google Finance function. And you wanna surrounded by quotation marks. Then the next thing that we want to put it in as an attribute. So by default, which is price. So you don't actually have to take compress. You can just put it in Google Finance and hit Enter and it should load the price of Air Canada. Okay, So there's an error here and it probably doesn't like this space. Yeah, so it doesn't like the space between the colon and the first letter of your ticker symbol. Now if we did comma price, it'll also show the same value. Now going back to the list of attributes, Let's type in price open. So this is opening price of Air Canada for the latest state, $24.77. And if we go over here and look at the opening price for Air Canada is $24.77. Another attribute that we could type in is high. So this is the current day's high price. So changing this to high, we get a value of 25 dollars and 99 cents. So going over back to this chart, $25.15, 25 dollars and fifteen cents. So I think it's probably because the high prices actually not recorded here in the shirt since it's every five minutes and it takes the average price of the five-minutes. So on Yahoo Finance, we can see the daily range is $24.60 in twenty-five dollars in $0.19. So the highest $25.19. And that's exactly what Google Finance by there. So we type in low here. We can see that the press reporter is $24.60, which is the exact same thing that Yahoo finance reports. At $24.60 for Air Canada is low of the day. There's other interesting statistics like market cap, trade time, data delay, volume average, P E ratio, earnings per share. So those values, some of them I've actually pulled onto this portfolio summary over here. So that's the usefulness of the Google Finance function. 15. Outro: Now that you've gone through the entire course, hopefully you've created your own personal portfolio tracker. So that's essentially the assignment for this course. So if you've been following along and creating your own, then you finish the assignment and you've mastered the portfolio spreadsheet tracker. I'd love for you guys to leave your comments or feedback on how this course one for you, as well as if you have any cool portfolio visualizations or metrics that you've added on top of what I've taught here. I'd love to hear about that as well. It's been a joy and pleasure to teach you guys this class virtually. I will be coming out with more courses on how to do stock market research. So stay tuned for that. For now. Happy investing.