👉 CORRECTION: I made one mistake @15:03. C4 in the denominator (# of shares purchased), should actually be E4 (# of shares sold). You should also remove the double quotes ,"") at the end of the IF statement from that same formula. 💾 Purchase the file created in this video here: ryanoconnellfinance.com/product/investors-toolkit-live-stock-tracker-in-excel/ 📈 Need help with a project? ryanoconnellfinance.com/hire-me/
When i change that, my total gain/loss in the stock portfolio dashboard changes to #VALUE!. I don't get why because my range from the VLOOKUP only goes to column D, the same as in the video. Do you have any idea how i can solve this issue ? Thanks in advance for your help!
Great video! I have been using a similar sheet for my portfolio for quite awhile now. However, there is a few problems with your model. The most obvious and problematic one is in a scenario where you have sold a stock, and then bought it again later. The purchase price from your first order would then be included when calculating price per share. EDIT: Didn’t watch the whole video when commenting, so I see that you wanted to include realised gains / loss in the return for each stock.
No, I don’t think so. Personally, I’d like my holdings to show the purchase price. And I’d like to show the return for just this trade in a stock, and not include returns I made on the stock in the past. Let’s say you sell all your Amazon stocks with a nice profit. A few years later you buy Amazon again. With this sheet, the previous purchase price would be included when calculating a purchase price for your new position in Amazon. The same goes for the return.
experiencing same problem. I have stock I bought and sold then I buy again with bigger amount, The total gain/loss on dashboard is projecting incorrect figures.
Yes, I have same problem, this is a issue with this model, its calculating and adding soldout transactions with new purchases and changing the prices of new transactions specially new average price. I thing there should be a third scenario beside BUY, SELL and that is SOLD. so it wont effect new same stock transaction or you have to delete the old transaction but than you will lose the total gain and loss history. Please advice if any one came up with solution to resolve this issue, Good luck guys.
Thank you Ryan!! Amazing video, I followed your steps to build the tracker and it is so so helpful! I was having real challenges trying to come up with one on my own. So glad I found your channel :D
Great video! Any idea on how to deal with stock splits? For example, Google split 20:1 in 2022. In my transactions this is represented as two consecutive sell/buy transactions. However, the value of the purchases and sales pre-split are obviously not the same as post.
Thank you! I think it would be best to retroactively deal with this in the transactions sheet by multiplying the original amount of shares in the transaction by the split factor (20 in your example) and dividing the price by the split factor as well
Great video Ryan. Thanks you. How can this be used for keeping track of option trades (single options and spreads) and stocks in the same Excel sheet? Thank you.
Thank you! And good question. Excels in built stock API does not support option prices right now unfortunately. I hope they add that feature in the future. There are 3rd party plugins that you can use to retrieve option prices in Excel but that would get a little complicated and go outside the scope of this dashboard
I am working through the exercise and have a question. I originally made the Dashboard Individual Holdings a table. I could not get the name to work without spill errors and could not figure it out. I finally did some internet searches on spill arrays and It looks like the Spill formulas like UNIQUE will not work in a table. So I had to remove the table and just format the cells to look like yours. So the Stock Portfolio Dashboard is not a table just regular cells.. Unless I am missing something. There is room up to row 18. What if you have more than 30 active investments. Do we have to add more rows and force the Lower portion the portfolio summary off the screen. ?? Just wondering what will happen as we cannot use a table there.
Good question! You can just insert more blank rows underneath the table to make sure they fit into the table. You will then have to scroll down to see your pie charts however as everything will not conveniently fit onto one screen anymore
An excellent video, I purchased your template. But how do I differentiate between currencies? If I buy a stock in one currency (say, EUR instead of USD), how will the Transaction Log deal with it and how can I ensure the Log recognises the currency?
This is awesome!!! Tks!!! Now what if you want to calculate the performance in % over time (TWR)? Say you need to store monthly performance of this changing portfolio.
My pleasure! For that, you would need the historical daily adjusted close prices for each stock for the period you held them. You could use Yahoo finance for that. Trying to calculate the performance for each month complicates the problem further however. I'm planning to make a video on this exact topic in the future so stay tuned
@@RyanOConnellCFA i would store the monthly evaluation somehow. Then I would calculate twr with the simplified Dietz formula. Shouldn’t be that difficult using power query.
Hi Ryan. Liked your Excel sheet. Can you tell how do you take care of stock splits, automatically. I'm bit inquisitive about it. Sorry! I realised that you have already given your thought about stock splits in some of the answer of the comments. Thanks
Excellent video Ryan, very helpful and your skill with Excel is top notch! Do you have any suggestions on how I might incorporate Option trading transactions into the Realized P&L tab to come up with how to factor profits from options into the Total Return or Realized Gain of a portfolio? Love to hear your thoughts on that. Thanks again as this gives me great assistance for how I can tweak what you have done to point it in the ways I invest!
Hey Jerry, thank you! Unfortunately, that would be very difficult because the Excel API does not support option data. You would likely have to go find a sperate 3rd party plugin that supports option pricing and add that functionality in which I havent done before
@@RyanOConnellCFA Understood, and thank you Ryan. I really only invest in option selling looking for the premium to add to my income either by only selling covered CALLS or covered PUTS. I'm thinking that perhaps treating the premiums similarly to the way you handle dividend income in your spreadsheet would suffice. Then, my thinking would be in cases where my selling options don't expire but get assigned, the underlying sell assignment or buy assignment of the stock would eventually show up on the P&L once all the buy/sell transactions have been completed. Would that give a reasonable P&L assessment once all transactions become finished and finalized?
Thank you! In addition to recording the dividends as cash, also record the dividends as "Buy"s and put in the number of shares you got on the reinvestment. This will make your portfolio correctly reflect your current holdings but also allow your dividends to be included in returns
Hi Ryan, I just purchased the template and am happy with it :) Just wondering how you would add transaction fees to the template to correctly calculate returns? Thanks
Hey Jason, thanks for purchasing the file! I think the easiest way to account for transaction fees without having to change the infrastructure of the file would be too simply add the transaction fees to the price per share that you are inputting as you paid for the shares in the transaction sheet. For example, lets say you bought 10 apple shares for $100 each, and you incurred a transaction fee of $5 when you purchased them. I'd add $.50 cents ($5/10 shares) to the price of each as paid and say I paid $100.50 for each share. This would reflect the transaction fee in the returns
Ryan, Sharp to obtain the combo charter. :) Great video! . I love excel and how it can be utilized for personal finance. Take your dashboard, customize it by adding a scenario analysis, and incorporating the use of the data analysis tookpak (solver, correlation, covariance, and descriptive statistics) needless to say, it makes for easy use to manage an individual or family portfolio.
Hi Ryan, thanks for the video. Very useful. How would you go about tracking a NAV for the portfolio such that you can see the performance over any specific time period? Thanks
Hey Alejandro, its my pleasure. The reason that I didn't add this feature is that you would need adjusted close prices which account for dividends and stock splits and unfortunately the Microsoft Excel API doesnt support adjusted close stock prices. So in short, there is no accurate way to do it with the Excel API. You can do it if you get the values from Yahoo Finance into Excel but it is more convoluted
Thanks for the video, I had a question on how I can deal with the dashboard when the spill increases (i.e when I add new stocks in the transaction log) and intercepts the portfolio summary below. Is there anything I can do about this? Thank you.
You can insert rows in between the main dashboard table and the pie charts to create more room to allow for more stocks. The downside is, you may not be able to see the entire dashboard on a single page
i like you video and includes the transaction sheet for buy and sell transactions. A thumb up for you video👍 probably the equivalent of the google sheet would nicely complement (if it is not available)
Hi, great video. But what if I want to compare my portfolio returns vs an index, how do I do that? Given that i have invested at irregular intervals since the start.
Hey there! Yes, from my udnerstanding, the Excel functions for fetching and analyzing stock history, such as the "StockHistory" function and the Excel Stocks API, generally operate the same way on both Mac and PC
Hey Ryan! This dashboard is awesome. Just set up mine... I know it's not a table, but is there a way to sort the weightings from larger to smaller? Right now, they're all over the place. Thanks
Thank you! I'm not sure off the top of my head that there is a way to filter by weights without messing up the formulas. . Hopefully we will have an Excel super power user come along in this comment section in the future to let us know if there is any possibility haha
Any ideas on what to do if a lot of holdings are CEFs and the fund which are returning an industry, is there a better way to add those without having to do it all manually? They are pulling in fine with ticker names.
How do you deal with different currency? I have my stocks all entered in the transaction log in £GBP but have stocks us $USD, £GBP and €Euro pulling through automatically from excel in their countries currency so getting wrong readings! Loving the vids! I'm trying to get more organised!
I'm glad you're loving the videos! For this purpose, you may want to use Google Sheets and follow the methodology I laid out in this video here: th-cam.com/video/jPziXoUHZuo/w-d-xo.html Lets me know if that helps
Hi, I just bought this spreadsheet. I have a few accounts with various brokers...how would I go about entering the transactions for each account? Would I need 1 sheet per account ?
Thank you for buying the file! You could do it the way you proposed (creating a separate copy of the file for each account you have), or you could put all of your transactions from all your brokerage accounts into a single copy of the file. It is completely up to you and both should work
Great video again. 2 remarks. With the IFERROR when I use the ,””) I get a value error if no sale has taken place. Better to use ,) which is blank. Secondly, average sale price is dividing by total buy shares and not sell shares. Now as a brain teaser I am struggling with, how to deal with stock splits. In that case you need to know if a split e.g. took place before or after you sell some shares. Any ideas how to solve that? Thanks!
Really good catch on the price per share sold! I will edit the pinned comment and the description so that hopefully other viewers will not make the same mistake. That is interesting, I've always used the iferror function the way I did in this video without any problems but your way is a quicker way to write it. As for stock splits, it would get pretty complicated as you would need to keep a table with all the stock splits and all of their dates, including how many for how many. Then you would need to keep another table with the dates in which you bought and sold shares. Then you would need to write formulas with nested if functions accounting for the dates. That is how I would approach it
I have a question. With the part of the video talking about Realized and Unrealized Gains and losses, do you have to have shares sold on the ledger for this formula on excel to work? I got as far as this section ok as far as following along and making my own copy but every time I buy shares, that total gains remains blank while when I sell a stock, it is a negative number.
Error- Realized P&L sheet has mistake. E4 has zeros and ACCORDING to your correction when changing denominator C4 to E4, you dividing price in column F by zeros, it’s giving error. You have to keep double quotes IFERROR “”, in the formula in column E to get rid of ZEROS. Please make a correction in the correction you ask for. Nice job, loving it. I do have question, how you getting dashes in F column under price per share ?
Hi, I bought the template explained in this video. It was working fine but a the Transaction Log page I had a problem. At the template: The transaction Log page has a B2 the Stock ticker category name. After adding transaction at this page when I tried to add one more transaction at B33 area immediately all the pies and data did not transfer anymore to to the Dashboard page. Are there any limitations in the template with the number of transactions added or the number of the total tickers at the portfolio?
Hi Ryan, I have few questions with the tracker. I am trying to replicate one that you have made however I would require a few more functionalities for eg. 1. I am handling multiple accounts and I have created a validation above to select the account and have added the account column in the transaction log to represent which account does the trade reflect for. After doing this I am not able to incorporate it in current holdings, I have tried to use sumifs in the formula for 2 conditions (account, current holdings) however I am getting error. I have also tried to work it with AND operator and still I seem to make some error. Can you please help with this?
UK stocks should be supported! You might need to consult the official Microsoft Office support documentation or community forums for specific guidance.
Hi, my name is Kamran and I am based in Dubai. I am watching your videos and impressed with your excel skills. I was wondering if you can help me with one formula to automatically calculates cost of shares sold on First-in-First-out basis. I regularly trade in stock market so volume of transaction is quite large. Hence, it is not possible to manually check cost of each share. Hence, I am looking to automate my file, however, I am not able to arrive at cost of shares sold. I took help from AI tools, watched youtube videos etc but remained unsuccessful. Problem comes if I make a split purchase and a split sale i.e. first buy quantity of any share on different dates and then sell the quantity in small chunks on later dates. I would be grateful if you have or you can suggest any solution for this. KR
Hey Muhammad, this is outside the scope of an Excel dashboard as it is a bit more complicated. I have been working on an app for portfolio management like this for a client of mine. I will revisit this comment and let you know when it is completed. It will account for this situation
Sadly, after inputting all the data I only now realised that Microsoft requires the annual subscription for the live tracking, I somehow missed that detail. I have paid for a lifetime one-time access, which doesn't feature this option. I am highly suspicious of 365 privacy and security. Is there another app for Mac which would enable the same functions as you teach in this video?
The purchased file does not work. Cleared the Transaction log in order to place my own stock symbols. Ex. AAPL . Then in Dashboard the "Total/Gain Loss" Showes #VALUE! Formulas are working Ok. Please HELP. Currently using MS365
When i correct the mistake you mentioned in the comments, my total gain/loss in the stock portfolio dashboard changes to #VALUE!. I don't get why because my range from the VLOOKUP only goes to column D, the same as in the video. Do you have any idea how i can solve this issue ? Thanks in advance for your help!
Hi, great video. I've bought one of your spreadsheets thinking I could change it with my own stocks and etfs after following your video instructions, how wrong I was😅. I really tried for 10 minutes on the pc but after that my head was hurting so much that I had to turn it off. I'm not a fan of using the pc and my work requires literally 5 minutes every week and then I don't need it so I'm not really an experienced user of Google sheets and excel. Is there a way to just put the names of the stocks/etfs, the date of purchase, the amount of stocks purchased and the program sorts everything on its own?
Hahah I can understand that Carlos! It looks like you bought a spreadsheet for a different Excel portfolio tracker than the video you are currently commenting on. I can send you the one for this video which has the capabilities you are talking about. I would encourage you to watch this video as well to make sure you know how to update it properly
@@RyanOConnellCFA Hi Ryan, I watched that one as well but it doesn't include the transactions tab which would be quite helpful for keeping tracking of buys and sells and to keep track of which stocks I actually own now especially with all the buys and sells I do. Thanks!! Your videos are amazing
Hi Ryan, I bought your template, two questions please... 1) how to make it work with EM stocks? like how to teach the template to know which exchange is linked to the ticker we are putting in? 2) how to work with stock dividends? Thanks
Hey there, thank you for purchasing the file! When I discuss the dividends in the Transaction sheet section, is there anything you're missing on what you need to do with dividends? To work with Emerging Market (EM) stocks using the stock API in Microsoft Excel, you may need to append the exchange identifier or country code to the ticker symbol, ensuring Excel recognizes the correct exchange. For example, if you're looking at a stock listed on the Bombay Stock Exchange, you might use "RELIANCE.BO" where "RELIANCE" is the ticker and ".BO" specifies the exchange.
A lot of the stocks I have in my portfolio aren't picked up by Microsoft's Data API. FOr example if you hold btc in the tracker you'll get a "#FIELD!" error for the market cap size and other figures. Is there any way to fix this so that my port's figures are accurate?
Hello BTC is bitcoin which is a crypto currency and not a stock so it will not work with Microsoft's stock API unfortunately. Unfortunately, cryptos won't work with the method shown in this video
I think I possibly solved it. Would you not just take the value in cell M21 ("Total Gain/ (Loss) $") and divide that buy all the buys in the transaction log?
Hi Ryan, thanks for the video. However, I noticed that the average cost computation in the realized P&L differs when we sell some of the shares we bought. How can we address this issue? Time stamp 14:23
Unfortunately I made on mistake in this video which I have put in the pinned comment and the description! Here is the correction: "👉 CORRECTION: I made one mistake @15:03. C4 in the denominator (# of shares purchased), should actually be E4 (# of shares sold). You should also remove the double quotes ,"") at the end of the IF statement from that same formula."
@@RyanOConnellCFA Hi Thank for the reply. How can we modify the calculation for the average price per share of purchased assets to account only for shares remaining in the portfolio, excluding those that have been sold? As the current formula is considering all the shares purchased till date.
You need to make it clear that you are using Excel 365 for this video. Older versions as well as Excel 2021 ( the latest version of Excel if you don't want to rent 365) DO NOT include the "Stock" functions/catagories. Do you have a video on creating a dashboard that includes new share purchases?
👉 CORRECTION: I made one mistake @15:03. C4 in the denominator (# of shares purchased), should actually be E4 (# of shares sold). You should also remove the double quotes ,"") at the end of the IF statement from that same formula.
💾 Purchase the file created in this video here: ryanoconnellfinance.com/product/investors-toolkit-live-stock-tracker-in-excel/
📈 Need help with a project? ryanoconnellfinance.com/hire-me/
When i change that, my total gain/loss in the stock portfolio dashboard changes to #VALUE!. I don't get why because my range from the VLOOKUP only goes to column D, the same as in the video. Do you have any idea how i can solve this issue ? Thanks in advance for your help!
@RyanOConnellCFA ?
@@gregoryverlinden6508 I changed the "" in the IFERROR to 0, and it worked
After I changed C4 to E4 it messed up the Dashboard calculations, are you sure this correction is wright ?
Amazing, is does not only work perfectly but i learned a lof of new stuff in excel while seeing your tutorial, thanks!
Great to hear!
Great video! I have been using a similar sheet for my portfolio for quite awhile now. However, there is a few problems with your model. The most obvious and problematic one is in a scenario where you have sold a stock, and then bought it again later. The purchase price from your first order would then be included when calculating price per share.
EDIT:
Didn’t watch the whole video when commenting, so I see that you wanted to include realised gains / loss in the return for each stock.
Hey, just saw your edit! So after completing the entire video, you do not see any problems with it?
No, I don’t think so. Personally, I’d like my holdings to show the purchase price. And I’d like to show the return for just this trade in a stock, and not include returns I made on the stock in the past. Let’s say you sell all your Amazon stocks with a nice profit. A few years later you buy Amazon again. With this sheet, the previous purchase price would be included when calculating a purchase price for your new position in Amazon. The same goes for the return.
experiencing same problem. I have stock I bought and sold then I buy again with bigger amount, The total gain/loss on dashboard is projecting incorrect figures.
Yes, I have same problem, this is a issue with this model, its calculating and adding soldout transactions with new purchases and changing the prices of new transactions specially new average price. I thing there should be a third scenario beside BUY, SELL and that is SOLD. so it wont effect new same stock transaction or you have to delete the old transaction but than you will lose the total gain and loss history. Please advice if any one came up with solution to resolve this issue, Good luck guys.
Thank you Ryan!! Amazing video, I followed your steps to build the tracker and it is so so helpful! I was having real challenges trying to come up with one on my own. So glad I found your channel :D
Great video! Any idea on how to deal with stock splits? For example, Google split 20:1 in 2022. In my transactions this is represented as two consecutive sell/buy transactions. However, the value of the purchases and sales pre-split are obviously not the same as post.
Thank you! I think it would be best to retroactively deal with this in the transactions sheet by multiplying the original amount of shares in the transaction by the split factor (20 in your example) and dividing the price by the split factor as well
Great video Ryan. Thanks you. How can this be used for keeping track of option trades (single options and spreads) and stocks in the same Excel sheet? Thank you.
Thank you! And good question. Excels in built stock API does not support option prices right now unfortunately. I hope they add that feature in the future. There are 3rd party plugins that you can use to retrieve option prices in Excel but that would get a little complicated and go outside the scope of this dashboard
I am working through the exercise and have a question. I originally made the Dashboard Individual Holdings a table. I could not get the name to work without spill errors and could not figure it out. I finally did some internet searches on spill arrays and It looks like the Spill formulas like UNIQUE will not work in a table. So I had to remove the table and just format the cells to look like yours. So the Stock Portfolio Dashboard is not a table just regular cells.. Unless I am missing something. There is room up to row 18. What if you have more than 30 active investments. Do we have to add more rows and force the Lower portion the portfolio summary off the screen. ?? Just wondering what will happen as we cannot use a table there.
Same problem
Good question! You can just insert more blank rows underneath the table to make sure they fit into the table. You will then have to scroll down to see your pie charts however as everything will not conveniently fit onto one screen anymore
An excellent video, I purchased your template. But how do I differentiate between currencies? If I buy a stock in one currency (say, EUR instead of USD), how will the Transaction Log deal with it and how can I ensure the Log recognises the currency?
very helpful thanks. How would you include trading fees, and the effects of FX at time of trade/current/time of sell?
Can you include cash held in the portfolio, for example on Fidelity the money market is SPAXX?
I hold my cash in the same money market! Unfortunately that ticker will not work with Excel's API
This is awesome!!! Tks!!! Now what if you want to calculate the performance in % over time (TWR)? Say you need to store monthly performance of this changing portfolio.
My pleasure! For that, you would need the historical daily adjusted close prices for each stock for the period you held them. You could use Yahoo finance for that. Trying to calculate the performance for each month complicates the problem further however. I'm planning to make a video on this exact topic in the future so stay tuned
@@RyanOConnellCFA i would store the monthly evaluation somehow. Then I would calculate twr with the simplified Dietz formula. Shouldn’t be that difficult using power query.
@@macshock632 Good idea! I think it will largely depend on the data one has access to as well
Hi Ryan. Liked your Excel sheet. Can you tell how do you take care of stock splits, automatically. I'm bit inquisitive about it.
Sorry! I realised that you have already given your thought about stock splits in some of the answer of the comments. Thanks
My pleasure, thanks for diving deeper in the comment section to get your answer!
Two suggestions:
- Filter for different strategies or different portfolios.
- Option tracking
I have a problem with both.
Awesome simple dashboard tho
Thank you, I can look into building a dashboard with these features in the future!
@@RyanOConnellCFA thank you for the answer. I hope you can figure it out
I am trying to get into stocks and this has been very insightful
I appreciate that Dave!
Excellent video Ryan, very helpful and your skill with Excel is top notch! Do you have any suggestions on how I might incorporate Option trading transactions into the Realized P&L tab to come up with how to factor profits from options into the Total Return or Realized Gain of a portfolio? Love to hear your thoughts on that. Thanks again as this gives me great assistance for how I can tweak what you have done to point it in the ways I invest!
Hey Jerry, thank you! Unfortunately, that would be very difficult because the Excel API does not support option data. You would likely have to go find a sperate 3rd party plugin that supports option pricing and add that functionality in which I havent done before
@@RyanOConnellCFA Understood, and thank you Ryan. I really only invest in option selling looking for the premium to add to my income either by only selling covered CALLS or covered PUTS. I'm thinking that perhaps treating the premiums similarly to the way you handle dividend income in your spreadsheet would suffice. Then, my thinking would be in cases where my selling options don't expire but get assigned, the underlying sell assignment or buy assignment of the stock would eventually show up on the P&L once all the buy/sell transactions have been completed. Would that give a reasonable P&L assessment once all transactions become finished and finalized?
Awesome tool very useful. is there a way to make it work with partial stocks. lets say i had .5 stock in Walmart or whatever?
Yes, you should be able to just type in partial stocks into the transaction log!
Amazing Video! I may have missed it, but what happens if I reinvest the dividends instead of taking the cash value?
Thank you! In addition to recording the dividends as cash, also record the dividends as "Buy"s and put in the number of shares you got on the reinvestment. This will make your portfolio correctly reflect your current holdings but also allow your dividends to be included in returns
Hi Ryan, I just purchased the template and am happy with it :) Just wondering how you would add transaction fees to the template to correctly calculate returns? Thanks
Hey Jason, thanks for purchasing the file! I think the easiest way to account for transaction fees without having to change the infrastructure of the file would be too simply add the transaction fees to the price per share that you are inputting as you paid for the shares in the transaction sheet. For example, lets say you bought 10 apple shares for $100 each, and you incurred a transaction fee of $5 when you purchased them. I'd add $.50 cents ($5/10 shares) to the price of each as paid and say I paid $100.50 for each share. This would reflect the transaction fee in the returns
Great video! Thank you! Is this possible to do also in Google Sheets?
Thank you! I do have a similar video for Google Sheets already here: th-cam.com/video/tR_s960we7g/w-d-xo.html
excellent , thank you for your time
My pleasure, thank you for watching!
Ryan,
Sharp to obtain the combo charter. :) Great video! . I love excel and how it can be utilized for personal finance. Take your dashboard, customize it by adding a scenario analysis, and incorporating the use of the data analysis tookpak (solver, correlation, covariance, and descriptive statistics) needless to say, it makes for easy use to manage an individual or family portfolio.
Thank you for the feedback! That is an excellent recommendation to make the tool an all-in-one portfolio tracker. Great ideas there Jason
Hi Ryan, thanks for the video. Very useful. How would you go about tracking a NAV for the portfolio such that you can see the performance over any specific time period? Thanks
Hey Alejandro, its my pleasure. The reason that I didn't add this feature is that you would need adjusted close prices which account for dividends and stock splits and unfortunately the Microsoft Excel API doesnt support adjusted close stock prices. So in short, there is no accurate way to do it with the Excel API. You can do it if you get the values from Yahoo Finance into Excel but it is more convoluted
Thanks for the video, I had a question on how I can deal with the dashboard when the spill increases (i.e when I add new stocks in the transaction log) and intercepts the portfolio summary below. Is there anything I can do about this? Thank you.
You can insert rows in between the main dashboard table and the pie charts to create more room to allow for more stocks. The downside is, you may not be able to see the entire dashboard on a single page
@@RyanOConnellCFA Thank you!
Ryan, will this work for ETFs and Mutual funds, as well? Thanks!
Hey! It will definitely work for ETFs! Although, I think it will not work for Mutual Funds as I don't believe they are supported by the API
i like you video and includes the transaction sheet for buy and sell transactions. A thumb up for you video👍
probably the equivalent of the google sheet would nicely complement (if it is not available)
Thanks for the feedback! I just added your idea to my list of future videos to make! Thank you
Hi, great video. But what if I want to compare my portfolio returns vs an index, how do I do that? Given that i have invested at irregular intervals since the start.
Hi Ryan,
Before I purchase, I have a Mac. Do all the formulas and APIs transfer over to Apple's Number Program?
Hey there! Yes, from my udnerstanding, the Excel functions for fetching and analyzing stock history, such as the "StockHistory" function and the Excel Stocks API, generally operate the same way on both Mac and PC
Hey Ryan! This dashboard is awesome. Just set up mine... I know it's not a table, but is there a way to sort the weightings from larger to smaller? Right now, they're all over the place. Thanks
Thank you! I'm not sure off the top of my head that there is a way to filter by weights without messing up the formulas. . Hopefully we will have an Excel super power user come along in this comment section in the future to let us know if there is any possibility haha
@@RyanOConnellCFA I honestly don’t think it possible in this setup haha. But thank you so much!
@@joshuarasam2162 My pleasure! Maybe someone in the future will prove us wrong haha. Lets hope for it
Hey Ryan your formula for price per share started at 12:12 keeps giving me a value error. Any way you would know how to fix this?
Any ideas on what to do if a lot of holdings are CEFs and the fund which are returning an industry, is there a better way to add those without having to do it all manually? They are pulling in fine with ticker names.
Are you able to pull in the the data for those close ended funds with the tickers alright?
How do you deal with different currency?
I have my stocks all entered in the transaction log in £GBP but have stocks us $USD, £GBP and €Euro pulling through automatically from excel in their countries currency so getting wrong readings!
Loving the vids! I'm trying to get more organised!
I'm glad you're loving the videos! For this purpose, you may want to use Google Sheets and follow the methodology I laid out in this video here:
th-cam.com/video/jPziXoUHZuo/w-d-xo.html
Lets me know if that helps
@@RyanOConnellCFA awesome! Thanks for the quick response 😁
@@hemspear My pleasure!
How can I get a function to pull NGX data from the web into my spreadsheet as I cannot find it with google/yahoo finance?
Hi, I just bought this spreadsheet. I have a few accounts with various brokers...how would I go about entering the transactions for each account? Would I need 1 sheet per account ?
Thank you for buying the file! You could do it the way you proposed (creating a separate copy of the file for each account you have), or you could put all of your transactions from all your brokerage accounts into a single copy of the file. It is completely up to you and both should work
@@RyanOConnellCFA Thanks for your response. However, I am getting a "spill" error when constructing Portfolio Dashboard.
Great video again. 2 remarks. With the IFERROR when I use the ,””) I get a value error if no sale has taken place. Better to use ,) which is blank. Secondly, average sale price is dividing by total buy shares and not sell shares. Now as a brain teaser I am struggling with, how to deal with stock splits. In that case you need to know if a split e.g. took place before or after you sell some shares. Any ideas how to solve that? Thanks!
Really good catch on the price per share sold! I will edit the pinned comment and the description so that hopefully other viewers will not make the same mistake. That is interesting, I've always used the iferror function the way I did in this video without any problems but your way is a quicker way to write it.
As for stock splits, it would get pretty complicated as you would need to keep a table with all the stock splits and all of their dates, including how many for how many. Then you would need to keep another table with the dates in which you bought and sold shares. Then you would need to write formulas with nested if functions accounting for the dates. That is how I would approach it
You sir are amazing!
Thank you David, I appreciate it!
I have a question. With the part of the video talking about Realized and Unrealized Gains and losses, do you have to have shares sold on the ledger for this formula on excel to work? I got as far as this section ok as far as following along and making my own copy but every time I buy shares, that total gains remains blank while when I sell a stock, it is a negative number.
Is there a way to sort in the decreasing order of allocation?
Error- Realized P&L sheet has mistake. E4 has zeros and ACCORDING to your correction when changing denominator C4 to E4, you dividing price in column F by zeros, it’s giving error. You have to keep double quotes IFERROR “”, in the formula in column E to get rid of ZEROS. Please make a correction in the correction you ask for. Nice job, loving it.
I do have question, how you getting dashes in F column under price per share ?
Hi, I bought the template explained in this video. It was working fine but a the Transaction Log page I had a problem. At the template: The transaction Log page has a B2 the Stock ticker category name. After adding transaction at this page when I tried to add one more transaction at B33 area immediately all the pies and data did not transfer anymore to to the Dashboard page. Are there any limitations in the template with the number of transactions added or the number of the total tickers at the portfolio?
THANKS MAN
You're welcome!
How do you record transactions when you reinviest the dividends?
You would need to type this in as a separate transaction as a purchase where the amount you paid is the value of the dividends
Hi Ryan,
I have few questions with the tracker.
I am trying to replicate one that you have made however I would require a few more functionalities for eg.
1. I am handling multiple accounts and I have created a validation above to select the account and have added the account column in the transaction log to represent which account does the trade reflect for. After doing this I am not able to incorporate it in current holdings, I have tried to use sumifs in the formula for 2 conditions (account, current holdings) however I am getting error. I have also tried to work it with AND operator and still I seem to make some error. Can you please help with this?
can i use this for uk stocks and shares?
UK stocks should be supported! You might need to consult the official Microsoft Office support documentation or community forums for specific guidance.
Hi, my name is Kamran and I am based in Dubai. I am watching your videos and impressed with your excel skills. I was wondering if you can help me with one formula to automatically calculates cost of shares sold on First-in-First-out basis. I regularly trade in stock market so volume of transaction is quite large. Hence, it is not possible to manually check cost of each share. Hence, I am looking to automate my file, however, I am not able to arrive at cost of shares sold. I took help from AI tools, watched youtube videos etc but remained unsuccessful. Problem comes if I make a split purchase and a split sale i.e. first buy quantity of any share on different dates and then sell the quantity in small chunks on later dates. I would be grateful if you have or you can suggest any solution for this.
KR
Hey Muhammad, this is outside the scope of an Excel dashboard as it is a bit more complicated. I have been working on an app for portfolio management like this for a client of mine. I will revisit this comment and let you know when it is completed. It will account for this situation
Sadly, after inputting all the data I only now realised that Microsoft requires the annual subscription for the live tracking, I somehow missed that detail. I have paid for a lifetime one-time access, which doesn't feature this option. I am highly suspicious of 365 privacy and security. Is there another app for Mac which would enable the same functions as you teach in this video?
Do you track short sales? If I understand, your excel is based on long positions and not shorts.
Hey David, yes this stock dashboard is long only! I can look into developing one that accounts for short sales in the future.
The purchased file does not work.
Cleared the Transaction log in order to place my own stock symbols. Ex. AAPL . Then in Dashboard the "Total/Gain Loss" Showes #VALUE!
Formulas are working Ok. Please HELP. Currently using MS365
When i correct the mistake you mentioned in the comments, my total gain/loss in the stock portfolio dashboard changes to #VALUE!. I don't get why because my range from the VLOOKUP only goes to column D, the same as in the video. Do you have any idea how i can solve this issue ? Thanks in advance for your help!
Yes, I have same problem,
Hi, great video. I've bought one of your spreadsheets thinking I could change it with my own stocks and etfs after following your video instructions, how wrong I was😅. I really tried for 10 minutes on the pc but after that my head was hurting so much that I had to turn it off. I'm not a fan of using the pc and my work requires literally 5 minutes every week and then I don't need it so I'm not really an experienced user of Google sheets and excel. Is there a way to just put the names of the stocks/etfs, the date of purchase, the amount of stocks purchased and the program sorts everything on its own?
Hahah I can understand that Carlos! It looks like you bought a spreadsheet for a different Excel portfolio tracker than the video you are currently commenting on. I can send you the one for this video which has the capabilities you are talking about. I would encourage you to watch this video as well to make sure you know how to update it properly
Is the # called a pound sign in the US? Different to the £ sign?
What hardware are you running your pc?
This is most of my build here: pcpartpicker.com/list/L6qNrD
Thanks for the info! I'm looking at upgrading mine for better excel performance@@RyanOConnellCFA
Can you please make one for Google Sheets since these formulas don't work
Hello Lexi, I have made a video for a similar dashboard in Google Sheets here: th-cam.com/video/tR_s960we7g/w-d-xo.html
@@RyanOConnellCFA Hi Ryan, I watched that one as well but it doesn't include the transactions tab which would be quite helpful for keeping tracking of buys and sells and to keep track of which stocks I actually own now especially with all the buys and sells I do. Thanks!! Your videos are amazing
Great Video1
but when I am adding new data its not updating other files
Please help
I may need more details and context surrounding your problem to help
Hey sir in my excel stock doesn't show so how we do to show my stock
Time Stamp 21:05, i think there is an error while calculating the total gain , can you please recheck
Hi Ryan, I bought your template, two questions please...
1) how to make it work with EM stocks? like how to teach the template to know which exchange is linked to the ticker we are putting in?
2) how to work with stock dividends?
Thanks
Hey there, thank you for purchasing the file! When I discuss the dividends in the Transaction sheet section, is there anything you're missing on what you need to do with dividends?
To work with Emerging Market (EM) stocks using the stock API in Microsoft Excel, you may need to append the exchange identifier or country code to the ticker symbol, ensuring Excel recognizes the correct exchange. For example, if you're looking at a stock listed on the Bombay Stock Exchange, you might use "RELIANCE.BO" where "RELIANCE" is the ticker and ".BO" specifies the exchange.
Hey! Could you email me the name you used to buy the file again, I will process the refund once I know your name. Sorry about the delay
@@RyanOConnellCFA Hi Ryan I'm still waiting for the good news. [Order #1428] (February 3, 2024)
@@pghomies
I have just processed your refund! Sorry for the delay but I wasn't able to tell which order was yours
Thanks! You've refunded both orders so I feel a little bad about it. I hope to continue to support you in other ways!
How or where do you buy the template
Hello! You can purchase the file created in this video here: ryanoconnellfinance.com/product/investors-toolkit-live-stock-tracker-in-excel/
Thank you!
A lot of the stocks I have in my portfolio aren't picked up by Microsoft's Data API. FOr example if you hold btc in the tracker you'll get a "#FIELD!" error for the market cap size and other figures. Is there any way to fix this so that my port's figures are accurate?
Hello BTC is bitcoin which is a crypto currency and not a stock so it will not work with Microsoft's stock API unfortunately. Unfortunately, cryptos won't work with the method shown in this video
Avg price should be calculated in fifo fashion . I think the avg price you calculated is wrong
Nice
Thank you
how do i find SPY ETF?
You can just type SPY in there in the ticker column! It should be no different than any other stock
Great video, and very user friendly output. I am curious, how would you express the total gain/ (loss) in percentage terms?
I think I possibly solved it. Would you not just take the value in cell M21 ("Total Gain/ (Loss) $") and divide that buy all the buys in the transaction log?
Hi Ryan, thanks for the video. However, I noticed that the average cost computation in the realized P&L differs when we sell some of the shares we bought. How can we address this issue? Time stamp 14:23
Unfortunately I made on mistake in this video which I have put in the pinned comment and the description! Here is the correction:
"👉 CORRECTION: I made one mistake @15:03. C4 in the denominator (# of shares purchased), should actually be E4 (# of shares sold). You should also remove the double quotes ,"") at the end of the IF statement from that same formula."
@@RyanOConnellCFA Hi Thank for the reply. How can we modify the calculation for the average price per share of purchased assets to account only for shares remaining in the portfolio, excluding those that have been sold? As the current formula is considering all the shares purchased till date.
You need to make it clear that you are using Excel 365 for this video. Older versions as well as Excel 2021 ( the latest version of Excel if you don't want to rent 365) DO NOT include the "Stock" functions/catagories. Do you have a video on creating a dashboard that includes new share purchases?
Hello, you can find my version of this video for people that don't have Office 365 here: th-cam.com/video/-AEultcUAs4/w-d-xo.html