Excel Stock Portfolio Dashboard - FREE File Download
VloĆŸit
- Äas pĆidĂĄn 14. 06. 2024
- Build your own personal stock portfolio dashboard using the built in tools available in Excel for Microsoft 365.
đ» DOWNLOAD the Excel Stock Portfolio Dashboard here: www.myonlinetraininghub.com/p...
đ LEARN MORE: Join my Excel Dashboard course here: www.myonlinetraininghub.com/e...
đ SUBSCRIBE if youâd like more tips and tutorials like this.
đŹ Please leave me a COMMENT. I read them all!
đ§ââïž CONNECT with Mynda on LinkedIn: / myndatreacy
đ SHARE this video and spread the Excel love.
Or if youâre short of time, please click the đ
đŹ EXCEL QUESTIONS: Get help on our Excel Forum: www.myonlinetraininghub.com/e...
âČ TIMESTAMPS
0:00 Introduction
0:46 Dashboard Overview
2:37 Stock Data Types
3:54 Current Holdings Summary
7:54 Sparklines with STOCKHISTORY
10:40 Headline Figures
14:41 Portfolio Summary by Industry
16:39 Shares Watch List - VÄda a technologie
I've been busy with other things, but in the coming years, I will be spending a significant amount of time learning from Mynda. It's everything I need to know.
:-) glad you enjoy my videos, Edward!
Incredible how good you are at teaching. Itâs so clear when you explain what each function does as you type it in, makes it very easy to follow and understand whatâs going on. Thank you!
Wow, thanks for your kind words! Itâs great to hear you find my tutorials helpful đ
@@MyOnlineTrainingHub I think it was ironic. Starting at min 4.48 it gets really difficuklt I think and you could add explaination.
@@alexanderblock101 I don't think it was ironic - it's honestly a great tutorial, just not too easy to do if you're not familiar with coding.
Thank you very much ! Great lessons, all of them, I have learned formulas and "tricks" that I could not imagine. Thanks to you, Excel now is a new one to me.
Great to hear, Fabio!
You are so good at teaching! Simply incredible :D
Thank you! đ
Thank you! Everything was very well explained! :)
Great to hear!
I already built most my stock portfolio but I didn't know about the 'stockhistory' function in Excel. Now I can easily graph my %gains/losses of each stock according to my buying date value. Also I pulled my currency exchanges from an external source, way easier like you did in Excel itself. Thank you so much :) learned a lot!
Awesome to hear!
Did u built it in Microsoft 365?
I am loving it already, I have my own excel data sheet already and will improve it 100%! My best Minda! ....remember...you're the oneeee....
best from Barcelona
So pleased to hear that, Igancio!
Thanks a lot Mynda! One of the top Excel Master! I'm learning so much through your various tutorials
So pleased to hear that, Shinsuo :-)
Really great stuff, very useful and great to learn some new techniques. Would be awesome to see an example where the template supported stocks with different base currencies e.g. AUD and USD. as this will impact calculating portfolio value and overall gain/loss.
Cheers, Rick! You could definitely incorporate stocks in different currencies and then use the Stocks data type to get the conversion rates.
Thank you very much for sharing your knowledge. I am starting to create my portfolio tracker and this video was very useful. I just wish you had inserted purchases of stocks in other currencies as well since, personally, in my portfolio I purchased some stocks in USD and CHF but the overall totals are in euro because the bank is in EUR
Glad it was helpful! If you have multiple currencies, you can add a column for the currency and then another to convert them to whatever currency you want to report on. You'll then reference this converted column in your dashboard.
Wow... you just gave a lot of insights to solve my stock spreadsheet problems, thank you! I have been consuming your videos in the last few days and it's been amazing learns. Great work!
You're very welcome, Felipe!
Awesome excel handling. Love all the the formulas used, super practical and smart. Loved it. I did my own dashboard with macros, cracy coding, now you just showed super simple for everybody, NICE!
Glad you liked it đ
does she even excel, though? :))
in all seriousness, I've been following Mynda for a couple of years now and she's probably the most well versed Excel professional on youtube. I've seen many great content creators but she just adds an extra touch.
Aw, thanks so much, Raz! Made my day :-)
@@MyOnlineTrainingHub How do we account for stock splits (ie. Apple, Tesla) in the ledger and your worksheets?
My life would be complete if you could make a similar spreadsheet which also tracks Cryptocurrency. I have spent several years at university and wish my lecturers would explain things as clearly as you do! Great video and a wonderful spreadsheet, thank you! :-)
Thanks for your kind words, Gary! Microsoft's STOCKHISTORY and stock data types don't support cryptocurrency properly yet.
@@MyOnlineTrainingHubHello, and Good Day; this response answers my inquiry (cryptocurrency), which I do not have to post separately now.
Kind Regards.
Thank you ! Itâs totally amazing ! The best teaching and sharing totally impressed! Thank you
Wow, thank you, Josephine!
I want to thank you for your informative, well done and excellent step by step videos. You have demonstrated being a humble individual in sharing your knowledge and freely providing us all with your files. This is second to none! Thank you and I will be following closely.
Wow, thank you!
You've been a life saver! Great work in all your videos! They've been really helpful! Would the prices be live or do you have to manually update them each time?
Thanks, Zack! The prices must be updated manually by clicking the refresh all button. There is typically a 20 minute delay between the price in Excel and the market. It's not ideal for live trading, but rather a mechanism for reporting on your portfolio's value.
@@MyOnlineTrainingHub Great, thank you.
Thanks for an excellent tutorial! I have built a similar dashboard for myself, but I was still able to learn new things. Unfortunately, I see a potential problem with how you compute the gain/loss column. From what I understand, the dashboard is showing a list of all your current holdings, but the gain/loss column is showing the total gain/loss on both open and closed positions for that holding. If you have multiple buy and sell transactions for the same stock, you donât see the current gain/loss on that open position, but an average of the current open and all of the past closed positions, and this might be very misleading. Ideally, when I open a new position, it shouldnât matter if I had or had not traded the position in the past (provided all transactions were closed). Is this correct? Is there an easy way to update the formula so that the gains/losses on historical closed positions donât skew gain/loss on the current holdings?
In my case I don't have any partially sold holdings, so it's not affecting my total gain/loss calculation. If you will have partially sold holdings then you can add a column to your ledger that marks a holding as sold and then modify the total gain/loss SUMIF to a SUMIFS and exclude any transactions that are 'sold'.
@@MyOnlineTrainingHub Thanks for your answer. Thatâs a good idea. Actually, now that I think of it, I think it should be possible to have a formula that would calculate the open units for each buy position. All of the transactions have dates, so for each buy I just need to count the units open prior to this transaction and subtract the units sold after the current transaction. This will assume all of the positions are being closed in FIFO order, but should be fine for me. Thanks!
@@mctb32 hi mac, I am facing similar issue. If you have been able to apply the fifo functionality, can you kindly share the excel working. Thanks
@@MyOnlineTrainingHub Can you explain it in more detail?
@@mctb32 Hi, Have you solved the FiFo issue and could you share your solution/ template? Thanks
Thank you Mynda such a great collection of Excel sheets and lot of new knowledge for me. Appreciate the hard work đ
Glad it was helpful!
It is so comprehensive Mynda! Thanks!
Glad you think so!
If you Sell some but not all of a stock, how do you separate out Realized Gains from unRealized Gains using the Ledger?
Hi Kris, you'd need to split out the initial purchase into two transactions, one for the sold shares and one for the retained shares.
This is a real deal!!! I've watch many portfolio tracker tutorials but only cover minor technic on excel. Thanks so much for sharing.
Glad it was helpful, Nelson!
Love your ease of your knowledge here.
So nice of you, Dale :-)
Learned a lot of new skills !! Thank you!!
Glad to hear it, Eshwar!
I cannot express how gobsmacked I am by this spreadsheet/excel's new functions, and i am already quite familiar with excel...
MS should sponsor this vid (if they arent already) cuz i bet other people are now going 'finally a worthwhile reason to get 365'. Lots of thanks for the quality content, keep it up!
So pleased you found it helpful, Anti đ
Another great video. My favorite part of your posts is the tips along the way. Really great intel that always improves my workflow and knowledge.
So pleased to hear that, Ben đ
Fantastic job!! Very easy to follow and build my own dashboard. Just superb! Keep up the good work!
Thanks so much, Herbert :-)
I am impressed... you are the best xls. Teacher in the world .. so useful.. thanks so much..
Thanks for your kind words, Quentin!
WOW.... I WAS IN SEARCH OF THIS. I HAVE FALLEN IN LOVE OF YOUR VIDEOS.
I WILL REPLICATE IT TO INDIAN STOCK MARKET.
THANKS A LOT SISTER. THE WAY YOU EXPLAIN IS INDEED AWESOME.
Most welcome!
you just made what seemed impossible, possible, thank you for such clear instructions and demonstrations
Glad it was helpful!
you are so good at teaching simply incredible love to learn more from you
So nice of you, Dharmesh!
Thank you for your clear explanations. greetings from France đšđ”
Glad it was helpful, Joseph!
Wow thanks! This tutorial is amazing!
Glad you like it!
You're giving out solid gold out for free on this video. Thank you.
Glad the video was helpful, Mauro!
your effort and quality of video is phenomenal. i followed you all along. thank you
Awesome to hear đđ
Just amazing skills! Thank you for sharing. Deeply appreciated!!!
My pleasure!
Thanks Mynda. Super useful and creative dashboard! Thumbs up!!
Glad you like it, Wayne!
A huge thanks from south korea! Although it seems the video has been uploaded for more than a year, I've started learning excel lately and found this recently. Its true there are a lot of helpful contents in the internet but by far this video was one of the most helpful one applying my purpose for learning. Thanks again. I am very excited to further study with your tutorials!
So pleased to hear that đ
Great video. Really like how you make the dashboard coming live so easily and beautifully.
Thank you very much!
Learn a lot from this video. Thank you very much.
Glad to hear that!
Hi Mynda!Really Impressive Tutorial,Great To Pick Up Some Helpful Tips And Tricks...Thank You :)
Glad you enjoyed it, Darryl!
Thanks for the incredible video and explanation !!!
Glad you liked it!
Great instruction. Thanks!
Glad it was helpful!
Muchas gracias por el tutorial Mynda!! estĂĄ muy bueno!
Gracias, Miguel!
Amazing! The best I have seen by miles.
Thanks so much, Simon!
Bravo. thank you for your time and your efforts
My pleasure!
Great video, loads and loads of tips and trick, keep making such amazing videos
Thanks, will do!
Great explanation. Very helpful in my daily work.
Awesome to hear đł
Thank you for the amazing walkthrough. It makes total sense with sharpening up one's Excel acumen. I am using this skill to also develop dashboards amongst other business intelligence software like Tableau, and Power BI.
Glad it was helpful, Michel!
Mynda that was epic! Great video
Wow! Thanks so much, Chris!
amazing lecture, clear and can be followed. Thank you, one of my best learning excel
Thanks so much!
Always love your videos
Thanks, Abdullah!
Thank you for making my job,lot easier.
Glad I could help :-)
Great tutorial! Thank you!
Glad you liked it đ
Thank you for the tutorial! I have too many stocks on my watchlist for the watchlist section to be useful ahaha and since I mostly invest in stocks and ETFs to hold for a long time and collect dividends, I added a dividend section to my tables!
Great to hear you modified it to suit your needs đ
I liked the video quite instructive!
Great to hear đ
Thank you so much for the detailed video. I just discovered your page searching for Excel Stock data type.
Glad it was helpful, Kevin!
Great video/content! đThank you very much đ
Glad you enjoyed it!
Love it đŻđ„đ„đ„!! Great undiluted tutorial for those of us w/ an advance background in excel.
Glad you liked it, Hubert!
Thank you! It was very helpful!
I'm so glad!
Thanks for teaching us.
My pleasure :-)
This is very helpful. Thank you.
You're very welcome!
Marvellous - thank you đđœ
Glad you enjoyed it!
Will do. Thank you.
This was very well done!
Glad you like it, Ray!
Great Video! Great Work!!! Great Help!!!
Great to hear!
this spreadsheet looks smoking sick!
Glad you like it!
Great work...! Nice tutorial...!
Thank you, Shakthi!
Just Perfect. I've been waiting for that for a months. Thanks a lot. I would like see there one thing more: Script with Auto-Refresh to avoid do it manually.
Thanks so much!
Really2 great video, So appreciate with your kind share!
My pleasure đ
Great explanations I have ever sen.
Glad you think so!
It's a great video. Thank You so much .
Glad you liked it, Ed!
you are a wonderful person
Youâre very kind
Great Video!
Glad you enjoyed it đ
Great Dashboard!
Thanks, Sean!
Thank you so much, this is really helpful
You're very welcome!
Lynda that was such an awesome video, super informative and you make it look like a walk in the park lol...
One thing I really want on my dashboard is a line chart that that shows my inital deposit plus the portfolio growth over time, plus the regular deposits / withdrawals I make... any chance you have made a video on this?
Thanks again for all the work it took to make that video. đ
Glad it was helpful, Kyle! If you get stuck with your line chart, you're welcome to post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
SUPER useful, watched the full video. My stock charts have a similar functionality but I just learned about the specific conditional formatting relating to the up and down arrows and the % of market cap for the portfolio. I wish Microsoft would add 'Dividend Yield' into their fields available for use -- such a basic function should be easy to extract from their data sources. Bye from Abu Dhabi, Charles.
Glad it was helpful!
Awesome - thanks!
You're welcome, Stephen!
I like it! Thanks đ
Cheers, Geoff!
You are supper amazing!!!! Thanks
Glad you enjoyed it!
Great Dashboard !!
Thanks so much!
Thank you Treacy â€
Pleasure!
you are excellent babe :) I expect power bi videos more .. thanx for all.
I'll try :-)
Thank youÂĄ It was very explained.
Glad it was helpful, Juan!
Thank you for this wonderful resource. I've gained lot of excel skills from your videos.
Great to hear, Mitesh!
Great Mynda đđ»
Cheers, Graham!
This will be really helpful for me... Thank you...
Glad to hear that, Saheer!
Thankyou Mynda, it works really well and is nearly perfect for me with my trading. Just one problem is bugging me. If I have bought and sold a particular stock at an earlier time the resulting profit/loss is retained and this of course is calculated into my Unrealised Gain/Loss, even though it is actually realised. I have had to move all prior transactions to a separate ledger to fix this. But I'm thinking there must a more elegant fix to this problem so I can keep just one ledger. Any ideas?
this is what i have been looking đ
Awesome to hear!
You deserve more subs and likes
Aw, thanks so much! Please spread the word :-)
Thank you very much đđđŒ
Youâre welcome đ
love it!
Great to hear đ
Thank you.
You're welcome!
Thanks a lot for this video ma'am
Learned something new today
Glad to hear that :-)
@@MyOnlineTrainingHub ma'am I was trying to make this dashboard, but I faced one problem. Like if a company is registered in USA stock exchange and Indian stock exchange both, then USA values are reflected in the sheet is there a way to change it to my country.
Thanks for helping
An authentic Goddess of excel...
I'm trying to get the sheet working, with monthly maintenance expenses for brokers, buying and selling expenses and changing currency ;), for personal use... thank you very much
So glad you found my video helpful đ
you're the best!!
Glad you enjoyed my video :-)
Looking fwd to this! Waiting frm a gmt+8 timezone...
You can watch it now. No need to wait.
Love to see this in Excel 2019
Yeah, unfortunately it won't be available in Excel 2019. You'd need to upgrade to Microsoft 365.
@@MyOnlineTrainingHub Thanks for the reply. I feared as much, but worth asking.
This is fantastic. I was looking for something like this. Then I can take the data and link it to my budget workbook for total values and networth. Any chance of doing double entry Debit and Credit date example? Sorry to keep asking, but your videos are on another level and I am sure many people would be grateful to learn this with your fantastic teaching
Thanks for your kind words. For double entry data, assuming it's in separate columns for Debit and Credit, I would add a column that puts the values into a single column, flipping the sign for the credit values. You can then work with the data in a tabular format.
there is no way I could have build anything resembling this dashboard. Amazing.
I just downloaded the file (thank you) and will now go play with it. I am certain I will get into trouble and that is part of the fun in learning Excel.
Great to hear, Alain! Have fun with it đ
@@MyOnlineTrainingHub Hi "Mynda" ( not sure how you write your name sorry!) . I am having tons of fun with that spreadsheet, I am completely lost! Haha.
That led me to dive into your you tube channel!
Impressive stuff you have there.
I am not sure how I got the formulas to work (8:55... 10:25) so I will re-visit that but it is working for now.
Curiously any of my stock that are an ETF the current holding 'industry' section and therefore the Pie Chart doesn't recognize it and I either have a blank cell or a ref! error.
So lots to love lots to learn, very glad to have stumbled on your channel!
You got my name right đ if you get stuck with anything you can post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub .
.
Hi Mynda,
I am totally enjoying your tutorials, I have learned so much (and it is totally for my personal enjoyment since I am semi-retired).
In regards of this particular spreadsheet "Stock Portfolio Dashboard", I find it so simple and useful (no clutter, no ads or unsolicited 'advices') that I often use it as my "go-to" stock portfolio glance performance reference!
Unfortunately,
I still manages to crash it on a regular basis... And it can take me a few days to figure-out what I did incorrectly!
So my dilemma is: I like using this as my "go-to" reference but I find it conflicting to have my "need" for this information relying on my limited ability as an Excel amateur/hobbyist.
I came to wondering if you would do a 'custom' project, for a fee of course.
So this "Stock Portfolio Dashboard " would be the same but a bit more "robust" with some "idiot proof" features and a few more (maybe) capabilities.
There, the question is out!
Cheers,
a.