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.
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.... 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.
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.
Thank you. I watched a previous video you did on building dashboards and thought I’d try it. Somehow I got lost and luckily I found this video. It was great practice. Again thank you.
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!
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
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.
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!
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! :-)
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.
@@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.
What an amazing video. It just made me realize the power of my Excel subscription. Definetly, I will be using this dashboard for my stocks. Thanks! Regards from Mexico City :)
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.
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!
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.
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!
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!
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!
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!
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.
I don't expect a response as long ago as this video is but ... A wonderful video and file BUT ... is there anyway to get information such as DIVIDEND YIELDS, etc. That don't seem to be included in the rich data format stocks?
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.
Hi thanks for sharing how this can be done. Does it work with any stock exchange? If my portfolio includes stocks from different countries, will it still work in the same dashboard? Thanks again!
There are loads of supported exchanges. You can see if your country's exchange is supported here: support.microsoft.com/en-au/office/about-the-stocks-financial-data-sources-98a03e23-37f6-4776-beea-c5a6c8e787e6
Great tutorial! But I'm having trouble with the sorting from the ledger to the dashboard. At first it was working just fine, but after I kept adding more stocks and selling some, it would start to show the same stock twice (once for the time I bought it and another for the time I sold it) and I don't know how to fix it.
Thanks, Francisco! I suspect there is a slight difference in the stock names, maybe a space after the name that isn't apparent on the face of the cell, but when you edit the cell you can see the cursor doesn't blink at the end of the text.
Thanks for a fantastic video. Just one question - How can I handle different currencies? For example I have bought one stock in USD and another one in NOK(Norwegian Krone). The total value of my portfolio will be wrong due to this. I was just wondering if there is something I can implement to automatically detect and exchange these numbers into NOK. Thanks anyway!
Thanks, Henrik. If you trade in different currencies, then add a column to your Ledger that converts the transactions into the currency you want to report in and then reference that column in your dashboard formulas.
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
Hi There, Thank you for the tutorial. Just a question: For the Gain/(Loss) column - this calculates the unrealized Gain or Loss for any SOLD shares and for any holding shares - so some of it is realized. How can we old show Gain/Loss on currently holding share? E.G.: I could have bought 2 shares from XYZ - I sold 1 and kept one - the formula gives me the Gain from selling the 1 share and the unrealized gain on the share I still have. I hope this makes sense. Thank you!
Hi Virginia, you need to add a column to the Ledger that tags the sold stocks so that they can be excluded from the calculation using SUMIFS. e.g. sum where status "Sold"
Excellent tutorial. You're the best. Now I have to figure out how to deal with the exchange rate. For example, your dasboard is in Aus Dollar. But what if you buy a U.S. stock listed on U.S. stock exchange? The share price will then be in USD.
Glad you liked it! You can add a field in the ledger that converts the purchase currency into your local currency using the prevailing rate available from the Currency data type.
Thanks for taking the time to educate us! I have started to use your methods to track my (minor) portfolio. All my holdings are on the ASX. One of the shares I hold does not show up in Refinitive, it brings in data from overseas. That makes me wonder if Refinitiv only tracks ASX 200, or only larger cap companies? Thanks, Phil
Well, I found the answer! I sold the shares in Aug 2021, the (API) company has since been taken over by Westfarmers. I would like to add the info to the sheet, is in this financial year. I will find another way to do it! Thanks....
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.
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
I was curious at 12:26 when you added the data bars, the numbers bolded over the data bars and remained normal in the white space. I noticed this happened automatically but I couldn't determine what setting that is for that to happen. Thanx for a great tutorial!
@@MyOnlineTrainingHub Ok, thanx! I've been using Excel for decades and have tried to figure a way to do that and thought "cool, finally!", but no :( Again, great presentation, thanx!
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.
Love this dashboard and video! I posted this question in the excel forum a few days ago... is there a straight-forward modification that would sort the dashboard by market value instead of alphabetically? Thanks! Mark
Thanks, Mark! I answered your forum question. When you reply to your own forum questions they are easily missed because they are no longer listed as unanswered :-)
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.
Is there a Formula or Function to cancel out buys/sells. When I put in all my Buy/Sell Data on Ledger, all stocks including ones im not holding pop up.
Great video! Id love to know the formulas how you work around different currencies? For eg I have, £ & $. Where the GBX on Excel is a pain, as it's in 4 decimals rather than the easy to use dollar.
You could add an IF formula to check if the currency is GBP and then multiply it differently to the non GBP currency values to bring them all into a common base.
Thank you for the excellent tutorial. I have a question with the 12-month history charts. I've set up the same formulas for my own portfolio, however, I have holdings in 2 ETF's that have only been open for about 4 months. When I run the formula on these two entries, the first cell (T-365) returns "#N/A" with a note that there is no data for this date (as you would expect). However, starting in the second cell, it begins filling in the data from the inception date of the fund, so essentially it is showing the value for day 1 of the fund, which is actually something like T-120, on T-364. From there it enters the daily rate for each subsequent cell up to the point that today's closing price is displayed, which currently ends up being June 5th,2020, then the rest of the table is left blank. Any idea why the data starts filling in with the second cell in the row rather than displaying "N/A" up until October when the fund started, then filling in data from there until today??
Hi Shannon, that sounds like a possible bug, but maybe it's by design. I've not come across that scenario before. If you want to email me with a sample file I can ask Microsoft: website at MyOnlineTrainingHub.com
This is absolutely brilliant. Created something sophisticated yet easy to manage stocks with. My question is how do I add stocks to my existing watch list? Will that automatically create a new tab for new companies like when we ran the Pivot Table Option? Also, if I replace the name of one company with another in the Watchlist Tab, should it auto-populate the data on the company-specific tab?
Mynda, thank you so much. Such an amazing and great stuff. You make it easy to understand for a new to excel like me. However, I can't find stock data type. Any help if I need to install any plugin etc.?
I'm able to fix the stock data type issue. But I can't find the Pakistan Stock Exchange symbol is 'PSX'. Do this data type deal with all stock markets around the globe? How to find out which markets are supported? Any help pls, thanks.
You can see what exchanges are supported here: support.microsoft.com/en-us/office/about-the-stocks-financial-data-sources-98a03e23-37f6-4776-beea-c5a6c8e787e6
Thanks a lot Mynda, great video. I been playing with the "Watch List area" of Dashboard trying to add a line chart of several indexes... hope it works. Any idea how we can add different security types (bonds, options)?. Again, thanks for sharing your knowledge.
Glad to hear that, Largo. Perhaps this tutorial will point you in the right direction for scraping data from the web: ruclips.net/video/VkDpwSGWPmk/видео.html
@@MyOnlineTrainingHub Hi, great tutorial, thank-you! Like Largo mentioned above, is there any way you can do a tutorial specifically to retrieve info from Income Statement / Balance sheet / Cash flow ? Thanks.
Absolutly stunning tutorial. Thank you for sharing your knowledge :) I'm wondering, why the formula (4:52) still works when leaving 0 out. Somehow the SUMIF statement returns only values 0 on its own?!
Thanks so much! SUMIF returns zeros for stocks that have been sold. Zero is the equivalent of FALSE and any other number is the equivalent of TRUE for the purpose of the FILTER criteria. So essentially it has the same effect as 0. i.e. SUMIF without 0 returns: {162;162;162;162;35;0;0;0;0;0;0;56;56;56;56;56;56;56;56;4275;695;1376} The zeros are treated as FALSE and the other numbers are treated as TRUE. SUMIF with 0 returns: {TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE} Hope that clarifies things. Mynda
Super video, Thank you Mynda! Is there any way to group the order of the Unique/ Filter display by Sector? I have more stocks than the example, so displaying according to alphabetical order adds to clutter.
@@MyOnlineTrainingHub Thanks for the tip. I'm looking at how to combine SORTBY with UNIQUE/FILTER but the table hasn't been filtered yet, Is there a way to use these functions in the same line?
Hi i am having issue with the formulae =SORT(UNIQUE(FILTER(LEDGER[STOCK],SUMIF(LEDGER[STOCK],LEDGER[STOCK],LEDGER[UNIT])0))) as its not linking . please advice
There are several comments here about a stock appearing multiple times in the Dashboard when there have been multiple purchases in the Ledger. The advice has been that there must be some difference, like a trailing space, in how they were entered in the Ledger. I have had that problem often, and I find no difference in how the stock were entered; however usually copying an earlier Ledger Stock entry and pasting it into the later entry solves the problem. But even that does not always solve it. In those cases, I sort the Ledger by Stock and use Excel's Drag-Copy (drag the dot in the lower right corner of the Stock cell) to copy the Stock entry to the duplicate Stocks. That has not failed yet.
Thanks for that I can finally get my shares under control and see clearly how they are doing. One question I have is how to best account for stock splits in the spreadsheet without making it mega-painful?
Do you have a video on how to do the yellow squares that you use to highlight certain sections of the spreadsheets graphs? Also, how do you maximize a certain section temporarily on your screen? Thank you for your content!
Thank you for this video. It has helped me a lot. Quick Question: Is there a way to edit the formula in Dashboard cell B6 to not pick up my "cash" entry in the ledger?
Great tutorial. Thanks a lot. A question though. If you have stocks from different exchanges (for example XNYS and XBRU), the exchange have different days that the stock markets are closed/open and the timeline of stockhistory is not the same. Would there be a way to force the values to use a unique timeline ?
Thank you for this fantastic tutorial. Would this work for a large data set? For example I have data spanning over 12 years with a table of nearly 2,800 rows (and growing). Thanks in advance. Cheers
Glad it was helpful. Yes, 2800 rows is quite manageable for Excel, but you'll have to consider how to aggregate the data in such a way that it's useful to view in a dashboard. You might need multiple dashboards for different views.
Your videos are so helpful. You make everything seem so easy. I'm wondering if you might have any insight on the best way to Export Options Chains for Day trading?
How do we get the historical data for our portfolio's total holding market value (in cell F2)? And if it can't be done in retrospect, is there a way of having it automatically done in the future?
Hi Mynda thank you so much for such an amazing video thank you so much, One question I have invested in the saudi market and whenever I try the Stock option from Data, no information can be found, could you please guide me?
Glad you liked it. Not all exchanges are supported. You can see the supported exchanges here: support.microsoft.com/en-us/office/about-the-stocks-financial-data-sources-98a03e23-37f6-4776-beea-c5a6c8e787e6
Very useful one. However, I got a question in case some stock which has no data in excel or ETF that has no industry specify. How should we adjust the sheet? Appreciate your answer
Thanks, Joe! I can't reproduce the issue with the Industry. Perhaps the stock market you're referencing doesn't have industry information. You can test this theory with my stocks to see if you can see Industry for them..
@@MyOnlineTrainingHub Thanks for the feedback. I was using Mutual Funds and ETFs. Apparently these do not have “Industries”. I tried a stock and it worked. Thanks again for the quick follow up.
Hi, Great video with easy to follow information and formula writing. The one thing I can not do but I can not find the key combination to remove the initial copyright box. I don't know if I'm just old, due to using a mac, or online one drive. 🤷🏻♂️
I don't have a Mac, but I guess you'd hold the command key while left clicking the outer edge of the box, then press the Delete key. If you're still stuck, reach out via email and we can help you further: www.myonlinetraininghub.com/contact-us
I am so glad I stumbled on this RUclips. Very thorough and well explained. Working through this now and it will take a while to personalize, but you have given me a great head start. If I get too stuck, I will look into your courses. A few questions. What is the a source for stock quote info? Microsoft doesn't seem to support commodities or TBill rates, Fx etc, while Yahoo Finance does. 1) I would prefer to work with one data source, but maybe that is not possible if I want that data (which I do). Thoughts? 2) I assume that it would be reasonable to have multiple dashboards in the same spreadsheet - one for current investments and one for prospective investment?
Glad you liked my video! STOCKHISTORY uses this source: support.microsoft.com/en-us/office/about-the-stocks-financial-data-sources-98a03e23-37f6-4776-beea-c5a6c8e787e6 You can get FX rates, but I don't think it supports commodities etc. You can certainly have multiple dashboards in the one file.
@@MyOnlineTrainingHub I think you are right even though Refinitiv does support commodities and more. I may have to mix and match with data from Yahoo Finance. Cumbersome, but maybe doable. I have looked at a few more of your video "lessons" - wow I have been using Excel for longer than I wish to admit, but you have opened my eyes - lots to learn and incorporate as I try to set up investment analysis upon retirement but no longer access to Bloomberg. Thanks again.
Great video thank you so much. Quick question, How can I get the industry type for ETFs and currency? The formula only gives me access to individual stocks.
Love the video! It motivated me to download the dasboard and create some order in my portfolio management. There is only a slight hiccup though, I can't get your formula on the current holdings working properly (dashboard, B6 and downwards). I didn't alter any of the names but I did alter the ledger data towards my own portfolio. Is there a very simple reason why the formula might not work? Love to hear from you!
Assuming you have Microsoft 365 and Dynamic Array functions, then not without seeing the file. Please post your question and Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub Thanks for the reaction! Found the issue 30 minutes later, the reference to cell B6 was gone for some reason. Thank you for the great vid and the proper forum! Have a nice day & weekend :)
Hi guys, I have set this up and it is working great! Thanks! Is there a way to add fund tracking as well? I have most of my investments in index funds or ETFs and want to include those. Thanks in advance
Glad it was useful, Carl! If there isn't a ticker for the fund then you'd have to bring that data into Excel another way. You might be able to use Power Query to scrape it from a web page.
Hello Minda, I would like to thank you for your contributions and work on RUclips. Would you please have any other material in excel that you don't have on RUclips to share sales versus forecast accuracy? Thank you so much
Hi Mynda! Thanks for the informative tutorial! But I bumped into an ''excel ran out of resource'' error when running the =sort(unique(filter(sumif)))) function. Perhaps you got any idea? Cheers!
Excellent work! Absolutely Amazing! I am contemplating the automatic calculation of Weighted Average Purchase Price of a stock. I did a bit research and find it difficult to achieve without VBA. It is a bit like Inventory System I guess. Can you please do a video about WAP or Inventory? Thank you!
@@MyOnlineTrainingHub Thank you! I will look forward to your FIFO inventory video . The difficulty I found about the WAP is that once all units of a stock are sold entirely, the WAP will need to recalculate from the next purchase.
Glad I found this video, though it's been 3 years ago and thanks a lot. Now, applying it to my portfolio, I noticed that the Gain/(Loss) reflects the sum of all transactions of a stock in the ledger, meaning both realized and unrealized Gain/(Loss), am I correct? Also, how do I dynamically display the different brokerage accounts in the dashboard's current holdings, where a stock maybe traded in different brokerage accounts?
Glad you can make use of it. From memory, the gain/loss includes all transactions, but you can filter out sold stocks if you prefer to exclude them. You can also add a field/column for the different brokerage accounts and then you'll have that field to aggregate by.
Very good job. Love it. I learnt a lot about Excel. Thank you. There is one last thing I am not able to do with stocks tools : earnings per share. If you have a tutorial for that I'm in ^^.
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.
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!
Can you give me the sheet template for Indian stock market
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?
You are so good at teaching! Simply incredible :D
Thank you! 😃
Thank you. I watched a previous video you did on building dashboards and thought I’d try it. Somehow I got lost and luckily I found this video. It was great practice. Again thank you.
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!
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.
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 😊
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.
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.
Thank you! Everything was very well explained! :)
Great to hear!
What an amazing video. It just made me realize the power of my Excel subscription. Definetly, I will be using this dashboard for my stocks. Thanks! Regards from Mexico City :)
Awesome to hear!
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.
your effort and quality of video is phenomenal. i followed you all along. thank you
Awesome to hear 😊🙏
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.
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 👍
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.
I am impressed... you are the best xls. Teacher in the world .. so useful.. thanks so much..
Thanks for your kind words, Quentin!
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
23:52 Chart formatting can be completely copied from the first chart using copy formatting function, so you don't need to adjust again individually.
Yes, I use that technique in some of my other Dashboard tutorials. Don't want to give away all my tricks in every video 😉
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 😊
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 😊
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!
You're giving out solid gold out for free on this video. Thank you.
Glad the video was helpful, Mauro!
you just made what seemed impossible, possible, thank you for such clear instructions and demonstrations
Glad it was helpful!
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!
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 😊
you are so good at teaching simply incredible love to learn more from you
So nice of you, Dharmesh!
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 :-)
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!
Thank you so much for the detailed video. I just discovered your page searching for Excel Stock data type.
Glad it was helpful, Kevin!
I don't expect a response as long ago as this video is but ... A wonderful video and file BUT ... is there anyway to get information such as DIVIDEND YIELDS, etc. That don't seem to be included in the rich data format stocks?
Neither STOCKHISTORY or the Stock data types give dividend data. You'd have to import this some other way.
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!
Hi thanks for sharing how this can be done. Does it work with any stock exchange? If my portfolio includes stocks from different countries, will it still work in the same dashboard? Thanks again!
There are loads of supported exchanges. You can see if your country's exchange is supported here: support.microsoft.com/en-au/office/about-the-stocks-financial-data-sources-98a03e23-37f6-4776-beea-c5a6c8e787e6
amazing lecture, clear and can be followed. Thank you, one of my best learning excel
Thanks so much!
Great tutorial! But I'm having trouble with the sorting from the ledger to the dashboard. At first it was working just fine, but after I kept adding more stocks and selling some, it would start to show the same stock twice (once for the time I bought it and another for the time I sold it) and I don't know how to fix it.
Thanks, Francisco! I suspect there is a slight difference in the stock names, maybe a space after the name that isn't apparent on the face of the cell, but when you edit the cell you can see the cursor doesn't blink at the end of the text.
@@MyOnlineTrainingHub Thank you so much! I decided to copy and paste the stocks instead of writing them manually and it's been working just fine :)
Great video. Really like how you make the dashboard coming live so easily and beautifully.
Thank you very much!
Thanks for a fantastic video. Just one question - How can I handle different currencies? For example I have bought one stock in USD and another one in NOK(Norwegian Krone). The total value of my portfolio will be wrong due to this. I was just wondering if there is something I can implement to automatically detect and exchange these numbers into NOK.
Thanks anyway!
Thanks, Henrik. If you trade in different currencies, then add a column to your Ledger that converts the transactions into the currency you want to report in and then reference that column in your dashboard formulas.
Thank you ! It’s totally amazing ! The best teaching and sharing totally impressed! Thank you
Wow, thank you, Josephine!
Thanks Mynda. Super useful and creative dashboard! Thumbs up!!
Glad you like it, Wayne!
Thanks mudam. You're awesome. This dashboad will help me a lot.😊❤
Glad to hear that 🙏
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
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!
Amazing! The best I have seen by miles.
Thanks so much, Simon!
Hi There,
Thank you for the tutorial. Just a question: For the Gain/(Loss) column - this calculates the unrealized Gain or Loss for any SOLD shares and for any holding shares - so some of it is realized. How can we old show Gain/Loss on currently holding share? E.G.: I could have bought 2 shares from XYZ - I sold 1 and kept one - the formula gives me the Gain from selling the 1 share and the unrealized gain on the share I still have. I hope this makes sense.
Thank you!
Hi Virginia, you need to add a column to the Ledger that tags the sold stocks so that they can be excluded from the calculation using SUMIFS. e.g. sum where status "Sold"
Excellent tutorial. You're the best. Now I have to figure out how to deal with the exchange rate. For example, your dasboard is in Aus Dollar. But what if you buy a U.S. stock listed on U.S. stock exchange? The share price will then be in USD.
Glad you liked it! You can add a field in the ledger that converts the purchase currency into your local currency using the prevailing rate available from the Currency data type.
Thanks for taking the time to educate us! I have started to use your methods to track my (minor) portfolio. All my holdings are on the ASX. One of the shares I hold does not show up in Refinitive, it brings in data from overseas. That makes me wonder if Refinitiv only tracks ASX 200, or only larger cap companies? Thanks, Phil
Well, I found the answer! I sold the shares in Aug 2021, the (API) company has since been taken over by Westfarmers. I would like to add the info to the sheet, is in this financial year. I will find another way to do it! Thanks....
Glad you found my tutorial helpful, Phil.
Fantastic job!! Very easy to follow and build my own dashboard. Just superb! Keep up the good work!
Thanks so much, Herbert :-)
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!
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 😊
I was curious at 12:26 when you added the data bars, the numbers bolded over the data bars and remained normal in the white space. I noticed this happened automatically but I couldn't determine what setting that is for that to happen. Thanx for a great tutorial!
I think it's an optical illusion of the black font on top of the data bar colour. The font isn't bold.
@@MyOnlineTrainingHub Ok, thanx! I've been using Excel for decades and have tried to figure a way to do that and thought "cool, finally!", but no :( Again, great presentation, thanx!
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!
Love this dashboard and video!
I posted this question in the excel forum a few days ago... is there a straight-forward modification that would sort the dashboard by market value instead of alphabetically?
Thanks!
Mark
Thanks, Mark! I answered your forum question. When you reply to your own forum questions they are easily missed because they are no longer listed as unanswered :-)
@@MyOnlineTrainingHub Thanks for the reply in the forum. It answered my question.
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!
Is there a Formula or Function to cancel out buys/sells. When I put in all my Buy/Sell Data on Ledger, all stocks including ones im not holding pop up.
Hi Devin, I account for this in my formulas with FILTER that uses SUMIF to only include stocks 0.
Great video! Id love to know the formulas how you work around different currencies? For eg I have, £ & $. Where the GBX on Excel is a pain, as it's in 4 decimals rather than the easy to use dollar.
You could add an IF formula to check if the currency is GBP and then multiply it differently to the non GBP currency values to bring them all into a common base.
Thank you for the excellent tutorial. I have a question with the 12-month history charts. I've set up the same formulas for my own portfolio, however, I have holdings in 2 ETF's that have only been open for about 4 months. When I run the formula on these two entries, the first cell (T-365) returns "#N/A" with a note that there is no data for this date (as you would expect). However, starting in the second cell, it begins filling in the data from the inception date of the fund, so essentially it is showing the value for day 1 of the fund, which is actually something like T-120, on T-364. From there it enters the daily rate for each subsequent cell up to the point that today's closing price is displayed, which currently ends up being June 5th,2020, then the rest of the table is left blank. Any idea why the data starts filling in with the second cell in the row rather than displaying "N/A" up until October when the fund started, then filling in data from there until today??
Hi Shannon, that sounds like a possible bug, but maybe it's by design. I've not come across that scenario before. If you want to email me with a sample file I can ask Microsoft: website at MyOnlineTrainingHub.com
This is absolutely brilliant. Created something sophisticated yet easy to manage stocks with. My question is how do I add stocks to my existing watch list? Will that automatically create a new tab for new companies like when we ran the Pivot Table Option?
Also, if I replace the name of one company with another in the Watchlist Tab, should it auto-populate the data on the company-specific tab?
If you make changes or additions, new PivotTables and charts won't automatically be created, you'd have to do that manually.
Mynda, thank you so much. Such an amazing and great stuff. You make it easy to understand for a new to excel like me. However, I can't find stock data type. Any help if I need to install any plugin etc.?
I'm able to fix the stock data type issue. But I can't find the Pakistan Stock Exchange symbol is 'PSX'. Do this data type deal with all stock markets around the globe? How to find out which markets are supported? Any help pls, thanks.
You can see what exchanges are supported here: support.microsoft.com/en-us/office/about-the-stocks-financial-data-sources-98a03e23-37f6-4776-beea-c5a6c8e787e6
Thanks a lot Mynda, great video.
I been playing with the "Watch List area" of Dashboard trying to add a line chart of several indexes... hope it works.
Any idea how we can add different security types (bonds, options)?.
Again, thanks for sharing your knowledge.
Glad it was helpful! There's no support for bonds or options, sorry.
Learned a lot of new skills !! Thank you!!
Glad to hear it, Eshwar!
Love this dashboard. Thank you! Is there a way to have holdings sorted by weight? I know it’s usually a struggle with arrays…
Possibly. Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
Very helpful, thanks! Learned a lot!
Perhaps you can make a tutorial to extract/import financial statement/fundamentals from usual finance website
Glad to hear that, Largo. Perhaps this tutorial will point you in the right direction for scraping data from the web: ruclips.net/video/VkDpwSGWPmk/видео.html
@@MyOnlineTrainingHub Hi, great tutorial, thank-you!
Like Largo mentioned above, is there any way you can do a tutorial specifically to retrieve info from Income Statement / Balance sheet / Cash flow ? Thanks.
Thank you for your clear explanations. greetings from France 🇨🇵
Glad it was helpful, Joseph!
Absolutly stunning tutorial. Thank you for sharing your knowledge :)
I'm wondering, why the formula (4:52) still works when leaving 0 out. Somehow the SUMIF statement returns only values 0 on its own?!
Thanks so much! SUMIF returns zeros for stocks that have been sold. Zero is the equivalent of FALSE and any other number is the equivalent of TRUE for the purpose of the FILTER criteria. So essentially it has the same effect as 0. i.e. SUMIF without 0 returns:
{162;162;162;162;35;0;0;0;0;0;0;56;56;56;56;56;56;56;56;4275;695;1376}
The zeros are treated as FALSE and the other numbers are treated as TRUE.
SUMIF with 0 returns:
{TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE}
Hope that clarifies things.
Mynda
@@MyOnlineTrainingHub Wow! Thank you for your explanation! This makes a lot of sense now.
Super video, Thank you Mynda! Is there any way to group the order of the Unique/ Filter display by Sector? I have more stocks than the example, so displaying according to alphabetical order adds to clutter.
Yes, you can use SORTBY to specify a sort field: www.myonlinetraininghub.com/excel-functions/excel-sortby-function
@@MyOnlineTrainingHub Thanks for the tip. I'm looking at how to combine SORTBY with UNIQUE/FILTER but the table hasn't been filtered yet, Is there a way to use these functions in the same line?
Hi Adeline, Please post your question and sample Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub Thank you for your generosity!
Hi, Nice tutorial. Very professional. Congratulations.
Maybe this question was already answered but here I go: How to add dividends?
Glad you liked it, Ronald! You would have to enter dividend data manually as they aren't available with STOCKHISTORY.
It is so comprehensive Mynda! Thanks!
Glad you think so!
Hi i am having issue with the formulae =SORT(UNIQUE(FILTER(LEDGER[STOCK],SUMIF(LEDGER[STOCK],LEDGER[STOCK],LEDGER[UNIT])0))) as its not linking . please advice
Please post your question and sample Excel file on our forum where someone can help you further: www.myonlinetraininghub.com/excel-forum
There are several comments here about a stock appearing multiple times in the Dashboard when there have been multiple purchases in the Ledger. The advice has been that there must be some difference, like a trailing space, in how they were entered in the Ledger.
I have had that problem often, and I find no difference in how the stock were entered; however usually copying an earlier Ledger Stock entry and pasting it into the later entry solves the problem. But even that does not always solve it. In those cases, I sort the Ledger by Stock and use Excel's Drag-Copy (drag the dot in the lower right corner of the Stock cell) to copy the Stock entry to the duplicate Stocks. That has not failed yet.
Interesting. Thanks for sharing your experience and workarounds.
Great video, helped very much. Just one question, if i want to add [Stock].dividends how can i do that?
The dividend information isn't available via the Stock Data Types, so you'd have to use an external source for that information.
@@MyOnlineTrainingHub ok. Thank you!
Learn a lot from this video. Thank you very much.
Glad to hear that!
Thanks for that I can finally get my shares under control and see clearly how they are doing. One question I have is how to best account for stock splits in the spreadsheet without making it mega-painful?
Glad you can make use of it, Peter. No simple way to split stocks other than making two or more entries in the ledger.
@@MyOnlineTrainingHub Many thanks for that - keep up the good work
Do you have a video on how to do the yellow squares that you use to highlight certain sections of the spreadsheets graphs? Also, how do you maximize a certain section temporarily on your screen? Thank you for your content!
They are features of my video editing software, Camtasia Studio.
Thank you for this video. It has helped me a lot.
Quick Question:
Is there a way to edit the formula in Dashboard cell B6 to not pick up my "cash" entry in the ledger?
Hi Asher, you can use a SUMIFS formula and set one of the criteria to "Cash"
Thank u so much
Great tutorial. Thanks a lot. A question though. If you have stocks from different exchanges (for example XNYS and XBRU), the exchange have different days that the stock markets are closed/open and the timeline of stockhistory is not the same. Would there be a way to force the values to use a unique timeline ?
Yes, you can provide the date range by referencing dates in cells.
Love your ease of your knowledge here.
So nice of you, Dale :-)
Thank you for this fantastic tutorial. Would this work for a large data set? For example I have data spanning over 12 years with a table of nearly 2,800 rows (and growing). Thanks in advance. Cheers
Glad it was helpful. Yes, 2800 rows is quite manageable for Excel, but you'll have to consider how to aggregate the data in such a way that it's useful to view in a dashboard. You might need multiple dashboards for different views.
Your videos are so helpful. You make everything seem so easy.
I'm wondering if you might have any insight on the best way to Export Options Chains for Day trading?
Great to hear 🙏 I’m not familiar with option chains, sorry.
Awesome Video, thank you. I have a question, is there a way of making a line chart for your own portfolio's performance?
Yes, of course. You just need to plot the data over time i.e. dates on the horizontal axis, values on the vertical.
How do we get the historical data for our portfolio's total holding market value (in cell F2)? And if it can't be done in retrospect, is there a way of having it automatically done in the future?
great video!! just wanted to ask what function to use instead of stockhistory when making in google sheets
I don't think Google Sheets has an equivalent function.
Hi Mynda thank you so much for such an amazing video thank you so much, One question I have invested in the saudi market and whenever I try the Stock option from Data, no information can be found, could you please guide me?
Glad you liked it. Not all exchanges are supported. You can see the supported exchanges here: support.microsoft.com/en-us/office/about-the-stocks-financial-data-sources-98a03e23-37f6-4776-beea-c5a6c8e787e6
Very useful one.
However, I got a question in case some stock which has no data in excel or ETF that has no industry specify.
How should we adjust the sheet?
Appreciate your answer
You could use the IFERROR function to handle errors returned by missing data.
Great video. Easy to follow and setup. The “Industry” does not appear in the stock function list. I am using Office 365. How do I get this to work?
Thanks, Joe! I can't reproduce the issue with the Industry. Perhaps the stock market you're referencing doesn't have industry information. You can test this theory with my stocks to see if you can see Industry for them..
@@MyOnlineTrainingHub Thanks for the feedback. I was using Mutual Funds and ETFs. Apparently these do not have “Industries”. I tried a stock and it worked. Thanks again for the quick follow up.
Hi, Great video with easy to follow information and formula writing. The one thing I can not do but I can not find the key combination to remove the initial copyright box. I don't know if I'm just old, due to using a mac, or online one drive. 🤷🏻♂️
I don't have a Mac, but I guess you'd hold the command key while left clicking the outer edge of the box, then press the Delete key. If you're still stuck, reach out via email and we can help you further: www.myonlinetraininghub.com/contact-us
@@MyOnlineTrainingHub ok, I’ll give it another crack
I am so glad I stumbled on this RUclips. Very thorough and well explained. Working through this now and it will take a while to personalize, but you have given me a great head start. If I get too stuck, I will look into your courses. A few questions. What is the a source for stock quote info? Microsoft doesn't seem to support commodities or TBill rates, Fx etc, while Yahoo Finance does. 1) I would prefer to work with one data source, but maybe that is not possible if I want that data (which I do). Thoughts? 2) I assume that it would be reasonable to have multiple dashboards in the same spreadsheet - one for current investments and one for prospective investment?
Glad you liked my video! STOCKHISTORY uses this source: support.microsoft.com/en-us/office/about-the-stocks-financial-data-sources-98a03e23-37f6-4776-beea-c5a6c8e787e6
You can get FX rates, but I don't think it supports commodities etc.
You can certainly have multiple dashboards in the one file.
@@MyOnlineTrainingHub I think you are right even though Refinitiv does support commodities and more. I may have to mix and match with data from Yahoo Finance. Cumbersome, but maybe doable. I have looked at a few more of your video "lessons" - wow I have been using Excel for longer than I wish to admit, but you have opened my eyes - lots to learn and incorporate as I try to set up investment analysis upon retirement but no longer access to Bloomberg. Thanks again.
Thank you for this wonderful resource. I've gained lot of excel skills from your videos.
Great to hear, Mitesh!
Great video thank you so much. Quick question, How can I get the industry type for ETFs and currency? The formula only gives me access to individual stocks.
It doesn't support ETFs. You can use currency pairs like so: =STOCKHISTORY("GBP:AUD",DATE(2021,8,20),TODAY()-1)
Love the video! It motivated me to download the dasboard and create some order in my portfolio management. There is only a slight hiccup though, I can't get your formula on the current holdings working properly (dashboard, B6 and downwards). I didn't alter any of the names but I did alter the ledger data towards my own portfolio. Is there a very simple reason why the formula might not work? Love to hear from you!
Assuming you have Microsoft 365 and Dynamic Array functions, then not without seeing the file. Please post your question and Excel file on our forum where we can help you further: www.myonlinetraininghub.com/excel-forum
@@MyOnlineTrainingHub Thanks for the reaction! Found the issue 30 minutes later, the reference to cell B6 was gone for some reason. Thank you for the great vid and the proper forum! Have a nice day & weekend :)
Great explanation. Very helpful in my daily work.
Awesome to hear 😳
Hi guys,
I have set this up and it is working great! Thanks! Is there a way to add fund tracking as well? I have most of my investments in index funds or ETFs and want to include those. Thanks in advance
Glad it was useful, Carl! If there isn't a ticker for the fund then you'd have to bring that data into Excel another way. You might be able to use Power Query to scrape it from a web page.
Hello Minda, I would like to thank you for your contributions and work on RUclips. Would you please have any other material in excel that you don't have on RUclips to share sales versus forecast accuracy? Thank you so much
Glad you find my videos helpful, Rogerio! I don't have any other sales v forecast examples that aren't already on RUclips.
@@MyOnlineTrainingHub Hi, Thank You Very Much
Is there a way to pull the dividend history or most recent dividend with a formula in 365?
No, the dividend data isn't available.
@@MyOnlineTrainingHub aw dang! thanks for the reply though :)
Hi Mynda! Thanks for the informative tutorial! But I bumped into an ''excel ran out of resource'' error when running the =sort(unique(filter(sumif)))) function. Perhaps you got any idea? Cheers!
Not sure what would be causing this.
Excellent work! Absolutely Amazing! I am contemplating the automatic calculation of Weighted Average Purchase Price of a stock. I did a bit research and find it difficult to achieve without VBA. It is a bit like Inventory System I guess. Can you please do a video about WAP or Inventory? Thank you!
Thank you! I'm not planning on doing any inventory dashboards, but WAP should be easy to do; total cost / total units purchased.
@@MyOnlineTrainingHub Thank you! I will look forward to your FIFO inventory video . The difficulty I found about the WAP is that once all units of a stock are sold entirely, the WAP will need to recalculate from the next purchase.
Glad I found this video, though it's been 3 years ago and thanks a lot. Now, applying it to my portfolio, I noticed that the Gain/(Loss) reflects the sum of all transactions of a stock in the ledger, meaning both realized and unrealized Gain/(Loss), am I correct? Also, how do I dynamically display the different brokerage accounts in the dashboard's current holdings, where a stock maybe traded in different brokerage accounts?
Glad you can make use of it. From memory, the gain/loss includes all transactions, but you can filter out sold stocks if you prefer to exclude them. You can also add a field/column for the different brokerage accounts and then you'll have that field to aggregate by.
Very good job. Love it. I learnt a lot about Excel. Thank you. There is one last thing I am not able to do with stocks tools : earnings per share. If you have a tutorial for that I'm in ^^.
Thank you! Unfortunately, STOCKHISTORY and Stock Data types don't report EPS.
Just amazing skills! Thank you for sharing. Deeply appreciated!!!
My pleasure!