👉 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!
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! 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.
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.
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)
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
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
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
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
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 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
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
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, 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
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
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
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
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
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: ruclips.net/video/jPziXoUHZuo/видео.html Lets me know if that helps
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.
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?
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
UK stocks should be supported! You might need to consult the official Microsoft Office support documentation or community forums for specific guidance.
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
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?
![Eminem - Somebody Save Me (feat. Jelly Roll) [Official Music Video]](http://i.ytimg.com/vi/Vwa0HenQMi4/mqdefault.jpg)
👉 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
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
I am trying to get into stocks and this has been very insightful
I appreciate that Dave!
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.
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
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
THANKS MAN
You're welcome!
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
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
very helpful thanks. How would you include trading fees, and the effects of FX at time of trade/current/time of sell?
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
You sir are amazing!
Thank you David, I appreciate it!
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
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!
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 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
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!
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, 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
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
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
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.
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
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.
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:
ruclips.net/video/jPziXoUHZuo/видео.html
Lets me know if that helps
@@RyanOConnellCFA awesome! Thanks for the quick response 😁
@@hemspear My pleasure!
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?
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: ruclips.net/video/tR_s960we7g/видео.html
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!
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
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?
Nice
Thank you
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?
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
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!
Hey sir in my excel stock doesn't show so how we do to show my stock
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?
Is there a way to sort in the decreasing order of allocation?
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
Time Stamp 21:05, i think there is an error while calculating the total gain , can you please recheck
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: ruclips.net/video/tR_s960we7g/видео.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
Is the # called a pound sign in the US? Different to the £ sign?
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.
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
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!
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
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!
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
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
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: ruclips.net/video/-AEultcUAs4/видео.html
Avg price should be calculated in fifo fashion . I think the avg price you calculated is wrong