Create an Excel Investment Portfolio Tracker Template | Excel Tutorial
HTML-код
- Опубликовано: 18 сен 2024
- With Microsoft 365, you have the exclusive "stocks" feature to import "LIVE" stock information such as Price, P/E, 52 Weeks High/Low, Company's profile, and much more. Instead of paying $$$ every month for a paid service to track your stock performance, why not build one to save you some money? In this tutorial, we will be learning how to build an Investment Portfolio Tracker application from scratch using Excel along.
Note: The stocks feature is only available for Office 365 subscribers. If you don't have a Office 365 account, feel free to use the link (affiliated link) below to sign up.
🔖 Excel template download link is in the post
learndataanaly...
🔖 Supported Exchanges list
learndataanaly...
► Buy Me a Coffee? Your support is much appreciated!
-------------------------------------------------------------------------------------
☕ Paypal: www.paypal.me/...
☕ Venmo: @Jie-Jenn
💸 Join Robinhood with my link and we'll both get a free stock: join.robinhood...
► Support my channel so I can continue making free contents
---------------------------------------------------------------------------------------------------------------
🌳 Becoming a Patreon supporter: / jiejenn
🛒 By shopping on Amazon → amzn.to/2JkGeMD
🗓 Get updated on new Python videos → / madeinpython
📘 More tutorial videos on my website → LearnDataAnaly...
📺 Also check out my 2nd channel Excel channel focus on sharing Excel tips: bit.ly/3B1DjSA
✉ Business Inquiring: RUclips@LearnDataAnalysis.org
#Excel #Investment #ExcelPortfolio #StockAnalysis
Explanation of threshold. seekingalpha.com/article/498121-rebalancing-portfolio-threshold-limits
How to change currency if you buy other country stock?
shouldn't threshold value be populated by actual percentage instead of target? 30:09
Helpful. Im learning a lot of 365 features.❤
Thank you for informing us about the subscription version. I will not waste my time. You have earnt a "LIKE" from me.
Cheers
Ditto on the 365 version I was so excited so i am looking for one you may have
Great video! What would help if you start the video with a 1 minute overview of the end product to show if it will cover what people are looking for before engaging into a 37 min vid.
Thanks for the feedback. Will definitely do that on v2 video.
@@jiejenn Brilliant. Additionally, I've got stocks Europe, Asia and USA and the 'table total' shows a sum of Yen, USD and EUR. Could you also address in the next vid how if there's an option to have the sheet recognize the currency and calculate it standard to USD?
Looking forward to the next vid!
Thanks, Jie Jenn for showing us how to make the spreadsheet. Excellent job.
Glad I came across your video 🙏
Thanks a lot, this made my day
Well done, you helped us a lot!
Can you make this with a way to track dividends? Id like to project my monthly and annual income of dividend stocks.
yea i want the same too
@@mikemangabat2914 Same
very well explained , i've subscribed.
What do you do if the stock is on a different currency exchange? How do you covert it all to one currency at any given time?
Thanks Jie! Excellent tutorial.
Very descriptive video. Thank you for your kind support.
Thanks. Great Job!
Very informative, Many thanks Jie for your time and effort. Much appreciated & very well explained. Great work. Uber kool, keep up the good work. I've subscribed. Thanks, Santosh.
Thanks Jie ! Much appreciated
Great video tutorial. Is there a way let it populate dividend payouts? Or do you already have a video tutorial that you can point me to?
THis was a very thorough tutorial, thank you for your assistance!
Excellent stuff. Learned a lot on how to create a portfolio sheet. Good be good if you can give us a lesson on creating a Pivot Table
Honestly you were so much help thank you
This helped me a lot, great work!
Thanks for you excellent work. Much appreciated!
Got a couple questions if anyone could help.
1. How would I convert multiple currencies into using sheets and importing stock data from google finance. For example having investments in USD, GBX and GBP. I need a formula that can distinguish what’s not GBP and convert using the current currency rate in order to achieve overall figures in one currency (GBP)
2. Whilst Creating a portfolio tracker using Google Sheets. What’s the best way to to either add previously purchased stocks or adding a newly purchased position on top of an older position of the same stock. Example, if I’d purchased 1 share of NKE at $100 and then months later bought another 1 share of NKE at $110 how would I show this when creating a new portfolio in addition to adding more shares of NKE in the future.
great video so far. but im having problems, at around 21.50mins into the video you do the formula for value, e.g 100 shares at a certain price. i cant seem to get it to work on my spreadsheet. any help would be great. thanks
very damn useful video....wanted to create this sheet from very long time
nice helpfull
Great spreadsheet. How do you enter cash holdings in a bank?
Beautiful template, thanks for the video!
thank you very much
This is cool, thanks!!!!
when you manually format the difference in your asset allocation chart to get it to show up in red with parenthesis, what do you type in? I can't read it. I really appreciate this video, thanks!
What should I do to know the best portfolio for ETF using cumulative abnormal returns?
I'm having trouble with the Data Validation segment here. Every time I try to set it up and label the stock categories, I get a tiny arrow pointing sideways and another one pointing up And when I open the drop-down menu, the only thing it lets me do is to rename the "category" column to US Stocks. I'm stymied here. Any help would be appreciated.
Fantastic..
Thank you for this!! Can i also use this on Google Sheets?
You can. But some of the functions will work a bit differently.
fantastic info thanks
cant believe this is actually free
Thanks Bud!!
ThankYou. if u look at the video 29.53, i cant get the formula for "Actual" in Asset Allocation.
Hello, I am running into a problem with inserting a new row. It will not follow the format already set in the table. Do you know why this is happening ? Do you know how to fix this error? Thank you for the video.
In Excel there is a feature called "Table Tables". Is your table converted to Excel table or just a regular table?
support.microsoft.com/en-us/office/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c
Thank you Jie. I use MS Excel 2016 and based in Nigeria. On my excel sheet, there is no "data type" icon and as such no where to get Nigeria Stock Exchange (NSE) information. How do I go about this?
I'm pretty sure at the beginning of the video he says you need Office 365 to pull live data - that is the version which includes the "data types" functionality
I have the solution, you only have to install english language: Files -> Options -> Language and introduce English language
perfect, thank's!
What do you suggest doing for closed positions as you no longer want the price to update, you want the price you closed the position at?
Can you give me an example.
Is crypto included in the stocks data?
Just tested, and yes, crypto currencies are included.
Hey @JIE JENN,
Can you explain how the threshold column works ? I am not able to understand that.
Thanks in advance
This article explains well seekingalpha.com/article/498121-rebalancing-portfolio-threshold-limits
Thank you for the instructions! I only wish I can remember it....can you pls create a Trading Journal for us poor souls who are useless with Excel please...pretty please!!!!!!! I have subscribed too btw
Same here
dear Jie Jenn I see you used the stock button ! My question is I work with Excel 2019 and I don't have that , do you create a button?
You will need to have Microsoft 365 subscription to have the Stock and Geography feature.
Are the stock quotes received in real time? or are they delayed.....or should only be used at the end of a trading session?
Live data.
Hi Jie
Could you please create one for futures trading please 😊👍
I am not familiar with futures trading. Do you have an example?
How if I want to make a swing trade history but the stock price will not changed after I click Refresh all connections. I don't want it changed because it is for my history data of trade.
Don't think Excel supports that, at least not currently.
Nice, but how do you set up one for multiple investors, like in a club?
How would you set up their distribution calculator? as if one person invested 50%, another 25%, another 10%, another 15%?
You will have to build a custom template that fit your specific needs then incorporate the stock feature to your Excel spreadsheet.
straight to the point. we are not here to learn how to make the spreadsheet pretty.
Robin B. - really, did you really think this was straight to the point?
@@jayd9203 read it again. I wrote it as I wished him get to the point rather than showing viewers how to make spreadsheet pretty.
Robin B. - Perhaps the “Straight to the point.” comment is not what you intended. Maybe “Get to the point.” would be more appropriate. Now that I understand your intent, I agree.
My add column button doesn’t pop up when is there a way to enable it
Is it O365? It will pop-up in O365 desktop or online.
Same problem. Did you find a way to fix it?
I dont have the Option „stocks“ in my data bar.. How Else can i Access the Stock data ?
If you have a portfolio and you receive investment how do you track the ROI for the individual investor?
Depending on what information you want to track, there are a few of ways. One is to create a database and have a separate table to track the customers and another table to store the investment data. Another approach is having each customer have their own individual Excel spreadsheet to store their investments.
Is there some option I need to get the "insert data" button to appear to add the menus. Everything else is fine but can't see that..
I don't think there is an option for that unfortunately.
well done
What is the threshold column for in the asset allocation table?
This post might help explaining stock threshold. yourbusiness.azcentral.com/threshold-inventory-quantity-28622.html
@@jiejenn So, if the difference between actual and target asset allocation is greater than the threshold rebalancing needs to occur?
The 'Add COlumn' icon does not appear in the top right hand corner when i go to add a new column. Does anyone know as to why this happens?
Do you have Office 365 subscription?
Jie Jenn I have Office Online
Should I be getting office 365
@@omerdemirel7208 Stock Data feature is only available to Office 365 subscribers, if you absolutely needs the feature, then yes.
I am not getting the bank symbol ? Please help I am using the latest version of office..... but it's not working
You need to to have Office 365 subscription.
I have subscription
Then I don't know. I can only guess you might need to upgrade your Office.
When I use the the data options button I don't get the little symbols next to the stock names. Does anyone know how to fix this issue?
Are you using Microsoft 365?
Jie Jenn is it the same as office 365 or are they different?
Should be the same. Check if your Excel has the latest update. If so, send me a screenshot at RUclips@LearnDataAnalysis.org and I can take a look at it.
Jie Jenn just sent it.
I saw the screenshots, to be honest, I don't have an answer. But the functionality is still there, which is a great relief.
thx youtube algorythm
What vision of excel do you use please?
Hi there. I'm using Excel 365. The live stock function is unfortunately only available for Excel 365 subscribers.
@@jiejenn So, how do you go about this if you are not an Excel 365 subscriber?
Bookmark: 11:56
Can we do for other exchange?
Do you mean other exchange provider?
@@jiejenn Yes
@@jeslynelow4239 Yes, I tried for Canadian exchange and it works. I typed as follows: RY Canada and it got me CAD price, TD Canada and excel automatically got me TD stock prices in CAD from Canadian exchange. Typing only TD or RY will get from US exchange in USD.
Some stocks may not work, in that case i tried XTSE:AQN and it worked. Hope this is what you were looking for.
How do you get the price to update in real time?
Unfortunately Excel is not meant to do things like that. But if you really want, you can add a macro to refresh the information every 30 seconds or so.
Isn't there an easy web query data insert which can update itself.
@@jiejenn how do you do that
What if Im in canada, the share symbol in excel only take info from NYS not TSX? How to change country?
Unfortunately, I don't think the stock price pull feature is currently available outside US exchange.