I have been investing on Motif Investing for just over a year and am a big fan of the new investing options on the website. Not only has Motif allowed me to create sample investment portfolios that are completely customizable but I've saved hundreds on investing fees.
The way Motif Investing works is that you pick up to 30 stocks and exchange traded funds (ETFs) to group together in one investment fund. You can then buy the entire group for just one commission and continue to pay just one commission any time you want to invest more money in the group.
I invest my own money in five groups of stocks and other investments on Motif. I thought this one-year mark would be a good time to update on the progress of these stock portfolios as well as revisit the investing theme for each.
Okay, you should be ready to go, so let's get into it. First things first – enter your headers. Before you enter any information about your stocks or any formulas for calculations, you'll want to lay the foundation of the spreadsheet by determining what information you want to see. The largest DeFi protocol to date is Aave. During the month, the total value of assets blocked in the protocol increased by more than 300% and has already exceeded $ 1.53 billion. MakerDAO and Curve Finance are in second and third places in the list with blocked assets for $ 1.41 billion and $ 1.15 billion, respectively.
Utilize the Stackfolio platform for free and buy, sell, and participate loans for up to 75% off what you normally pay. Let us help you get the most beneficial solutions to your loan trading needs for free. Real-time trade and investing ideas on 53 from the largest community of traders and investors. Rooms Trade App. Dec 12, 2017 Stockfolio 1.4.3 – Stocks and real-time stock portfolio. December 12, 2017 Stockfolio is an investment app for macOS that allows you to research and track interesting stocks.
Click through to the My Stocks page for full detail on the individual investment portfolios and stocks in each.
As always, I have to note here that I'm not promoting these example portfolios as good investments for everyone. I like sharing the investments I hold and think will do well. I encourage everyone to make the case for their own sample portfolios in the comments section below.
Overall Sample Investment Portfolios Performance
I created the first four Motif Investing funds between February 2nd through the 11th of last year while the fifth fund following pharmaceutical stocks wasn't created until late October. This is why the market return comparison is different for each fund.
I've collected just over 2.1% in dividends over the year, about the same as the dividend yield on the overall stock market as measured by the S&P500. I would like this to be higher but a lot of the investments in my portfolios are growth stocks or in beaten-down sectors that have had to cut their dividends.
For example, the stocks in my dividend portfolio pay an average 2.8% yield but only six of the stocks in my pharmaceuticals portfolio pay any dividend for a portfolio total of 0.66% yield.
I'm fine with this lower dividend yield for now. I am heavily invested in energy and healthcare, two sectors that have faced a tough couple of years and many companies have scaled back on their dividend payouts. I love the long-term potential in these sectors, more on this below, and think that eventually the dividends will increase well above the rate paid on the general market.
I'm extremely happy with the returns on each portfolio. Across the four example investment portfolios set up last year, the average return was 30% against a return of 27% for the overall stock market. While a couple of the portfolios are broadly invested, I think all can continue to beat the stock market over the long-term.
Overall, just one of my portfolios failed to beat the stock market return over the period. I'll look at each investment portfolio return versus the market separately.
As for fees on the investments, I've paid just $50 to buy all the stocks and funds within the five investment portfolios. That's $50 total to buy 57 stocks and 18 exchange traded funds that diversify my portfolio across thousands of stocks.
My American Future Investment Portfolio
This is my favorite portfolio of the group and is invested in the sectors I think will drive the American economy into the future. These sectors are energy, agriculture and healthcare.
America has nearly reached energy independence and broken the OPEC oil cartel that has controlled prices for almost 50 years. While the price of oil has only recovered to half as much as it fetched in 2014, the outlook for the sector is excellent. The other two sectors, agriculture and healthcare, have both faced their own problems over the last couple of years but share in this strong long-term potential.
I detailed why these three sectors are the best long-term investments you can make in a prior article about the portfolio
The energy sector rebounded quickly after I set up the portfolio and it's beaten the stock market non-stop over the year. Weakness in oil prices recently has cut the portfolio return to 31.4% but that's still more than 6% over the market return.
Agriculture stocks have started to pick up as well and contributed to the high portfolio return. It's really only healthcare that has been holding the portfolio back with a 13.7% return on the sector ETF and weak returns in the individual stocks.
I still love this investment theme going forward and will hold it for decades as the idea plays out. Government regulation or not, there is no stopping the demand for healthcare and the sector will eventually become a cash machine.
While oil prices have come down a little and I don't think OPEC will be able to keep its production cuts, there is still a lot of long-term potential in energy stocks as well. Dividends are starting to come back up and it won't be long before I'm making a huge yield on my original investment.
Warren Buffet's Sample Investment Portfolio
I've followed Warren Buffett's investment portfolio for more than a decade and read his annual report to shareholders every year. The Oracle of Omaha always offers great insight into investing and his Berkshire Hathaway has returned an average 20%+ for decades, way higher than the return on the rest of the market.
But I haven't always agreed with Warren Buffett's individual investments and wanted to create an investment portfolio that picks just those I believe have strong upside potential. I also didn't want to weigh down the portfolio with nearly 100 stocks.
Tracking Warren Buffett's best investments, the 13 stocks and two exchange traded funds (ETFs) are blue chip favorites for value and dividends. Just as with all my portfolios, I invest a large chunk in core ETFs that give me broader exposure and then invest individually in stocks I think have more upside potential.
This investing strategy is called the core-satellite approach and it's one of the best ways for stress-free investing.
A few runaway stocks in the group really helped the portfolio return with the group posting a gain of 28.6% over the last year against a return of 25% for the stock market. A strong housing market has helped shares of USG Corporation nearly double and four of the stocks have returned 40% or more.
Warren Buffett Sample Investment Portfolio
I like following Warren Buffett's stock picks but the portfolio isn't one of my favorites. It does well but there just seems to be less reasoning behind his recent investments, especially chasing shares of Apple higher and investing in airline stocks. I like the stocks in my portfolio and will hold them for another year but may trade some out for other investments Buffett has made.
Dividend Stocks Sample Portfolio Update
I love dividend investing and use the dividend yield to screen most of my stock picks. Any time I go to invest in a specific theme or group of stocks, I'll usually narrow the list down by those paying out a quarterly dividend.
With this sample investment portfolio, I started by selecting three ETFs that would pay out strong dividends. I then looked through each sector to pick the highest quality companies with good dividend yields.
One important point in dividend investing is not to just buy stocks with the highest dividend yields. Your investments have to be quality companies or they might not continue generating the cash flow that pays for those dividends.
The return on the portfolio has been one of the best in my example investments, gaining 36.6% over the past year against a 30.7% stock market return. Beyond the portfolio return, I've also collected a great dividend yield that has also beaten the yield on the stock market.
Apart from the investment in utilities companies, I'm extremely happy with this dividend stock portfolio. Were it not for the utilities ETF and the energy company in the portfolio, the return would have been higher still.
Dividend stocks have beaten the overall market for decades and will continue to do so, providing consistent cash flow and appreciation. I'll be putting more money into this investment portfolio this year and for years to come.
Drug Stocks Investment Portfolio Example
I started this portfolio later than the others so we don't quite have a year's worth of returns but it is doing well so far. Just like the overall healthcare sector, I believe strongly that drug manufacturers benefit from unstoppable forces that will mean higher returns.
The returns on my pharmaceuticals portfolio have been good, beating an 11.4% gain in the stock market with a 12.7% gain since October. There have been a few laggards but I'm confident in the group as a whole.
Sample Investment Portfolio Drug Stocks
But what about those high drug prices? Why do Americans pay so much for pharmaceuticals compared to the rest of the world?
Yes, I agree that Americans pay too much for life-saving drugs but it's only because the rest of the world isn't paying its fair share. It takes years and billions of dollars to research and develop new drugs. It's only through patent protection and fair market prices that companies are able to afford to constantly develop new drugs.
If the U.S. government were to push drug prices artificially lower as is done in other countries, we would see a huge drop in new drug development. I don't think that will happen and I think pharmaceutical stocks will bounce back quickly from the investor fear that has held them down over the last year. Acrobat reader download 64 bit windows 7.
Retirement Investment Portfolio Review
This is my most broadly-invested portfolio and includes investments in bonds and real estate beyond stock investments. It is meant to be an example of a diversified portfolio including multiple asset classes that can stand up well even if the stock market falls.
Investors are desperately under-invested in bonds with many investors holding no assets in fixed income. Spreading your investment risk with diversification means a lot more than just buying stocks of different sectors. Every investor needs to hold bonds and real estate investments as well.
Will an investment in bonds hold back your total portfolio return, especially when the stock market is booming? Of course it will but that diversified portfolio will also keep you from freaking out when the next stock market crash hits Wall Street.
I'm not going to try predicting the next stock market tumble but there will always be one coming. When it happens, only investors that have investments in the safety of bonds and other asset classes will be able to sleep at night.
This retirement investment portfolio is the only one to not beat the overall stock market over the last year but for good reason. Bonds and real estate have both lagged the stock market as investors snap up stocks at higher and higher prices. Even with a loss of 1.1% on the bond market fund, the portfolio still managed to gain almost 24% last year and paid out a 3% dividend yield.
I'm continuously surprised at how high investors are willing to chase stock prices. The S&P 500 is now trading at over 27-times earnings made by companies over the last year, one of the most expensive multiples over a century of records.
The bull market in stocks isn't going to last forever and I'm relying on my investments in bonds and real estate to soften the blow when the next bear comes.
The power of Motif Investing goes beyond saving money on commissions though that's a huge benefit compared to other sites. The ability to set up your own sample portfolio investments means you worry less about individual stocks and don't stress out as much when one investment falls. Setting up your investment portfolios this way helps you reach your investing goals by not freaking out during the next stock market crash and not making the bad investing decisions that lose money.
Save
Save
- Using Google Sheets or Excel, you can build a custom spreadsheet that will allow you to see the information about your investments that matters most
- There are many tools online for investors to monitor their portfolios
- They might not have information formatted in the way you want or illustrated in the way you want
- Spreadsheets allow you to make a portfolio analysis tool that is exactly the way you want it
Save some time and download a copy of the Portfolio Spreadsheet here!
Don't feel like DIY-ing it?
Complete the form below and click Submit.
Upon email confirmation, the workbook will open in a new tab.
How to make a stock portfolio in Excel, Google Sheets, or any other spreadsheet software
This 'how-to' can be followed along in either Excel or Google Sheets. Really, any spreadsheet software will do. The formulas should be the same. Also, formatting and charting options should be very similar.
Would you rather watch a video than read a tutorial? Check out this post:
TRACK YOUR STOCK PORTFOLIO RETURNS USING GOOGLE SHEETS (OR EXCEL) – STEP BY STEP TUTORIAL
I will be using Google Sheets in this tutorial. I like Excel and use it often. Particularly with some of my more 'intensive' models. In this case, however, I think that Google Sheets is a better option. First and foremost because of its GOOGLEFINANCE functionality which will automatically update certain fields for you (price, volume, PE, EPS, and on, and on…). Second, since Google Sheets is cloud-based, you can access it anywhere – including your mobile device.
Microsoft does have a cloud-based version of Office (Excel), but I would not recommend it. I am a Microsoft fan in general and a big Excel fan in particular. But, I thought Office 365 (or whatever it's called) fell way short. Just my opinion though, use whatever you're most comfortable with.
In order to follow along in Google Sheets, you'll need a Google account. If you don't already have one, click here for instruction on how to set one up.
Once you have your Google account set up, go to Google Drive and select 'New' in the upper left-hand corner. Click on 'Google Sheets > Blank spreadsheet'.
Okay, you should be ready to go, so let's get into it.
First things first – enter your headers
Before you enter any information about your stocks or any formulas for calculations, you'll want to lay the foundation of the spreadsheet by determining what information you want to see.
For this example, things were kept relatively simple. The amount of information you can glean from an investment's current or historical data is almost limitless.
But, since this how-to is meant to serve as a starting point, I tried to keep things elementary. Here's what we'll analyze in this example:
- Company (fund) name
- Ticker symbol
- Purchase date
- Purchase price
- Shares
- Purchase cost
- Current price
- Gross current value
- Total dividends received
- Net current value
- Total gain or loss in dollars
- Total gain or loss percentage
- Annualized gain or loss
Enter these headers in the cells along the top of the spreadsheet. Personally, I like to leave a little space at the top and the left-hand side of my spreadsheets. So, I'll start in B5 and enter across to N5.
Additionally, I like to know the as-of date for when I last updated a workbook such as this. Therefore, in B2, I'll enter 'Portfolio updated:' and I'll bold everything I just entered (Ctrl+B). Espresso 5 3 2 0. Here's what that looks like:
Input some basic stock data
As mentioned earlier, this portfolio spreadsheet will consist of information you already know and information that you need to calculate.
The Name, Symbol, Purchase Date, Purchase Price, and Shares fields are all information you should already have. So, go ahead and enter that.
All of this information, in my example, was chosen at random. You may have more stocks or fewer stocks.
Google Sheets and Excel can certainly handle everything you have in your portfolio.
It might take some digging on your part to unearth this information – even if you have an online broker. Particularly the Purchase Date. But, if you want an accurate calculation of your Annualized Gain/Loss do your best to find it.
Want to know how to add quality stocks to your portfolio? Read this post:
DETAILED STOCK VALUATION SPREADSHEET WITH WALK-THROUGH
What if I have different Purchase Dates for different lots of the same stock?
That's a bit of a conundrum. To be completely honest, you might want a more sophisticated portfolio spreadsheet. But, there are a couple of ways you might work around it.
First, you could just list each lot separately. The only potential problem here is when it comes to allocating dividends (if any) to the different lots.
Second, you could group all of the lots together. But, unless you calculate a weighted-average Purchase Date and Purchase Price, a lot of your calculations are going to be erroneous.
Personally, I would separate the lots out and then group all of the dividend information under the lot you purchased first – for simplicity's sake.
Here's what we have so far:
Formula time! Purchase Cost
We'll start off simple.
Purchase Cost = Purchase Price × Shares
At the risk of oversimplifying things – I'll clarify for those of you who are completely new to spreadsheets…
Every formula begins with an equal sign (=). That's how Google Sheets and Excel know to perform a calculation.
So, in cell G6, type '=E6*F6' and press Enter. The asterisk (*) means multiply.
Framer studio 124. In my example, for stock symbol ZF, the result is $1,990 ($15.31 Purchase Price times 130 Shares).
'The Simply Investing Portfolio has gained over 322% since 1999 versus the stock market return of 146% over the same period.
500+ happy investors in over 25 countries.'
Click here or click the image below ▼
Don't type more than you need to, copy down!
Now, you need to duplicate this formula for every stock in your portfolio.
But, don't go to row 7 and type '=E7*F7'. Then row 8 and type '=E8*F8'. And so on…
Either copy the formula in G6 (Ctrl + C) and then paste (Ctrl + V) in cells G7 through G50 (or whatever row you end on).
Or, alternatively, Google Sheets and Excel give you the ability to duplicate what's in a cell (the formula) by clicking on the little square in the lower right-hand of the active cell, holding the mouse button down, and dragging it where you want it to copy. This is the quickest and easiest way to do it.
Automate Current Price and Gross Current Value in Google Sheets
One of the reasons I elected to use Google Sheets for this tutorial is because of the GOOGLEFINANCE function. I know that Excel used to have MSN Money functionality. But, if they currently have something similar, I'm not familiar with it.
What is GOOGLEFINANCE?
It gives you the ability to connect to Google Finance through a formula and populate Google Sheets with information about an individual stock.
For instance, if, in cell H6, you type '=GOOGLEFINANCE(C6,'price')' the Current Price of the stock entered on row 6 will be populated and automatically updated. Note the equal sign (=), and the quotes around 'price'. Disk drill v2 4 435 download free. Don't type the very first and very last quotes, though. Only type what's in bold.
With the GOOGLEFINANCE function, you don't have to worry about looking up the price (and other basic information, if you wish) and then manually typing it into Google Sheets! Always accurate and always up-to-date (though not real-time).
Copy the GOOGLEFINANCE function down for all of your stocks. If you're using Excel, or otherwise opt not to utilize the GOOGLEFINANCE function, you'll have to enter the Current Price manually.
With the Current Price, you can now calculate a Gross Current Value. Why 'Gross?' Because, later, we'll add dividends in order to get a Net Current Value.
Gross Current Value = Shares × Current Price
In cell I6, type the following formula: '=F6*H6'. Copy that formula down for all of your stocks.
Here's what it should look like thus far:
Dividends are an important part of your returns – be sure to include them!
Unfortunately, there's no way (that I'm aware of ) to automatically import dividend data for the stocks you hold. Updating this information is by far the most labor-intensive step in this tutorial.
Yes, the GOOGLEFINANCE function can import the yield percentage for a given stock. But, since this stock portfolio spreadsheet is focused on total returns, that isn't going to help much.
Want to identify stocks that pay (quality) high yields? Read this post:
HIGH DIVIDEND STOCKS – CHARACTERISTICS OF QUALITY YIELDS
If you own your shares through an online brokerage, as most people do, you should be able to access dividend payment history for the individual stocks you own.
For example, TD Ameritrade allows you to display dividends paid for a specific stock in your transaction history.
If this information can't be easily retrieved from your brokerage, then you'll have to do some homework. A good place to start would be the Nasdaq Dividend History page. Here, you can find the Payment Date and per share amounts of dividends paid for every stock. You'll have to multiply the per share amounts by the number of shares owned to get the full dividend paid.
Total your dividends received for each of your stocks and enter that information under the Total Dividends Rec'd heading (column J).
For the sake of accuracy, make sure you only include dividends paid to you while you owned the stock. Also, be sure to update this information every time a stock pays a dividend.
It's more trouble than it should be, for sure. But, as I said, dividends can make a huge contribution to the returns received for a particular stock. If this seems like too much trouble, you can forgo including dividends. Just know that your return numbers won't be 100% accurate.
Totaling all of your returns
With dividend information gathered, you can now calculate the Net Current Value. This will total your returns from capital gains and from dividends and give you an accurate picture of the stock's performance.
Net Current Value = Gross Current Value + Total Dividends Rec'd
In cell K6, enter the following: '=I6+J6'. Then, copy that formula down for the rest of your stocks.
Only a few more columns to go! Here's what your stock portfolio should look like now:
How are your stocks really doing?
The last three columns will be used to calculate the returns of each stock. Let's focus on the first two columns first.
Total Gain/Loss $ = Net Current Value – Purchase Cost
This is the difference between the value of the stock now (including dividends received) and what you paid for it.
In cell L6, enter the following formula: '=K6-G6'. As usual, copy that down for the rest of the stocks.
Total Gain/Loss % = Total Gain/Loss $ ÷ Purchase Cost
In cell M6, type the following formula: '=L6/G6'. Copy it down…
This formula compares your Gain/Loss in dollars to what you paid for the shares of stock you own. The result is a percentage and it tells you what the total performance of your stock has been – thus far.
But, what's a good Total Gain/Loss %? That's difficult to answer. A 5% gain is good over the course of one day or one week. A 50% gain isn't so good if it's over the course of twenty years.
Looking to analyze your investment portfolio? Read this post:
ARE YOU SATISFIED WITH YOUR INVESTMENT PORTFOLIO?
How to compare gains and losses – apples to apples
That's why I think you should calculate the Annualized Gain/Loss.
I'll warn ya, the formula is a little complicated. But, when you pull it off, it'll provide valuable insight into the true performance of each stock. It will allow you to better compare stocks against one another.
Type the following in cell N6: '=(K6/G6)^(1/(YEARFRAC(D6,$C$2)))-1'.
Yikes!
You don't have to necessarily know why it works. Just know that it does. Don't let it scare you off, this will make more sense if I break it down.
Before we do that, make sure you have a Portfolio updated date in cell C2. This date is used to determine the amount of time that has passed since the stock was purchased and is critical for calculating Annualized Gain/Loss.
The first part of the formula, '(K6/G6)', compares the Net Current Value to the Purchase Cost. It calculates the relationship between the value now and when you bought the stock.
The next part, '(1/(YEARFRAC(D6,$C$2)))' looks complicated, but, for the most part, all it's doing is figuring the amount of time that has passed since you purchased the stock. What's critical, though, is that you include the dollar signs ($) in front of the 'C' and the '2'. This ensures that the formula is always comparing a stock's Purchase Date to the same Portfolio updated date. The dollar signs ($) lock that cell into the formula so it doesn't change when you copy it down.
Finally, the '-1' at the end turns the result of the equation into a percentage that makes sense.
That's it! Again, the formula for cell N6 is: '=(K6/G6)^(1/(YEARFRAC(D6,$C$2)))-1'. Type that in. Copy it down. And, that nasty bit of business is over!
Here's what it should look like with all of the information completed for each stock:
We've analyzed each stock – what about the overall portfolio?
While you could quit here and (with a little formatting) have a perfectly good stock portfolio spreadsheet – let's push on just a bit further and put the final touches on this thing.
For most of our fields (except Current Price), we can analyze the portfolio in aggregate. That way you can compare your individual stocks against each other and understand how they contribute to the overall portfolio's returns.
Stockfolio 1 536
For most fields, this is pretty easy. It's simply a matter of totaling the entire column. Others will use the exact same formula you used for individual stocks. One last field is a bit tricky to calculate for the entire portfolio – Purchase Date. But, let's not get ahead of ourselves, let's start with the easy ones…
Fields that are summarized with SUM
Purchase Cost, Gross Current Value,Total Dividends Rec'd, and Net Current Value amounts for the whole portfolio are simply sums of the amounts for each stock.
In order to sum these amounts, we'll use the SUM function in Google Sheets/Excel. Start in cell G4 (above Purchase Cost) and enter the following: '=SUM(G6:G50)'.
You don't have to stop on cell G50. If your list of stocks goes beyond row 50, then you can go to G100, G500, G1000, or however far down you need to. Just make sure that you are including the Purchase Cost for every stock in the equation. It will not hurt to include blank cells in the formula.
Enter the SUM function for Gross Current Value, Total Dividends Rec'd, and Net Current Value in row 4. E.g. '=SUM(I6:I50)', '=SUM(J6:J50)', and '=SUM(K6:K50)'.
Fields that are summarized with an equation
The Total Gain/Loss $, Total Gain/Loss %, and Annualized Gain/Loss fields use the same equations to calculate for the whole portfolio as they did for the individual stocks within.
Remember how you copied those formulas down rather than re-entering them for each stock? The same thing can be done with the portfolio calculations to save time.
Click and drag (highlight) across cells L6, M6, and N6, then press Ctrl+C (copy) on your keyboard. Then click on cell L4 and press Ctrl+V (paste). Viola! The spreadsheet will automatically use the previously calculated totals to determine Gain/Loss amounts for the entire stock portfolio.
One last formula…
Maybe you're wondering how an Annualized Gain/Loss can be calculated for the entire portfolio if there's no Purchase Date for the portfolio as a whole?
How the hell would you go about settling on onePurchase Date for a bunch of different stocks?
In order to pull this off, and get an accurate Annualized Gain/Loss for the entire stock portfolio, we need to calculate a weighted-average Purchase Date for the entire stock portfolio.
What will the Purchase Dates be weighted by? By Purchase Cost.
This is done by using a function in Google Sheets/Excel called SUMPRODUCT. No need to get into the particulars about how/why this works. Just know that it does. The result is a single Purchase Date for your entire portfolio.
In cell D4, enter the following formula: '=SUMPRODUCT(D6:D50,G6:G50)/G4'. Remember – if your list of stocks goes beyond row 50, change D50 and G50 to D100/G100, D150/G150…whatever you need to.
Don't be alarmed if the result of that formula is a big number and not a date. That's simply an issue of formatting and we'll get into that next.
Here is what your (almost completed) stock portfolio spreadsheet should look like now:
This seems like good information, but it looks like crap!
Yep, you are correct. Time to spruce things up a little bit and make this information more readable.
Formatting is all a matter of preference. There's no single right way to do it.
I'll walk you through some of the things I like to do in terms of formatting and you can copy what you like. Also, feel free to explore on your own. Most of the formatting options are available below the main menu at the top of the spreadsheet. Mouse over the little icons, and you'll see text pop up explaining what each option does.
I'd definitely say you want to format your Purchase Dates as dates – if they're not already. Select those cells and click on the icon that has '123▼' on it. Lower down on the list, you should see a couple of options for a date format.
Another thing I almost always do when formatting a spreadsheet is to color in the cells that have formulas. That serves as a visual reminder, for me, not to type in them. The cells that are left white are the variables that can be changed. The little paint can icon is what you use to change cell color.
You can format the numerical data as numbers or currency. Show more or fewer decimals with the icons that have '.0←' or '.00→' on them.
I'd also change the Total Gain/Loss % and Annualized Gain/Loss to a percentage format. Use the '%' button on the menu.
Lastly, I like to bold my headers, and, usually, my totals.
Again, formatting is a matter of preference, so there are no wrong answers. Play around with it and make it look how you want. If you ever want to wipe the slate clean – select the cells you want to change, click on 'Format' in the main menu, and then 'Clear formatting.'
Below is how I opted to format my stock portfolio spreadsheet. You'll notice the charts on the right-hand side. Which, we'll get into next…
Use charts to better understand your portfolio and returns
You can chart anything on your spreadsheet. You could even create a bar chart comparing Purchase Prices or Shares if you wanted. Though, I'm not sure why you would?
In my spreadsheet, as you can see above, I opted to chart two things.
- A comparison of the Annualized Gain/Loss among all of my stocks using a simple column chart
- A pie chart that shows the composition of my portfolio – by stock
Creating these charts was quick and easy in Google Sheets. Doing so in Excel is also easy ('Insert', then 'Charts'). But, there will be some differences between the two visually.
Stockfolio 1 53 Catamaran
To make the first chart, simply highlight all of your stock Symbols (not the header, C5). Then, holding down the Ctrl button, select all of the Annualized Gain/Loss percentages (again, not the header, N5). The Ctrl button allows you to select multiple cells that are not next to each other.
From the 'Insert' menu, select 'Chart.' You should see a column chart pop up with the Symbols in the x-axis and the percentages in the y-axis.
Click and drag the chart where you want it. If you double click it, you'll see the Chart editor. After selecting 'Customize' and then 'Chart & axis titles' you can enter a title in the 'Title text field.' As you can see, I just named mine Annualized Gain/Loss.
If you select the Symbols and Net Current Values and insert a chart, you'll probably get another column chart. I think a pie graph better represents this information. Fortunately, changing the chart type is easy. Double click this new chart and in the Chart editor click 'Setup' and change the 'Chart type' by selecting a pie chart from the drop-down menu. It might be under the 'SUGGESTED' heading.
Stockfolio 1 53 =
There we go. A portfolio spreadsheet with well-formatted, useful information.
How to make a stock portfolio in Excel (or Sheets)
Spreadsheets allow users to analyze their portfolios and returns in just about any way imaginable. The GOOGLEFINANCE function in Sheets automates updates for a lot of frequently referenced information about stocks and mutual funds.
I tried to explain how my example stock portfolio was made in detail. What (if anything) did you get hung up on, though?
Is there additional information you would want to have in your stock portfolio spreadsheet? What would you want to analyze on an ongoing basis? Music keyboard software for pc.
