Create a Stock Portfolio Tracker on Excel Using Live Data

[@KenjiExplains]

Skip the waitlist and invest in blue-chip art for the very first time by signing up for Masterworks: masterworks.art/kenji Purchase shares in great masterpieces from artists like Pablo Picasso, Banksy, Andy Warhol, and more. See important Masterworks disclosures:

[@bryanscott2126]

Hey Kenji are u from Spain or where are u from???

[@its.tranquility.]

Im a junior in high school right now, and i have to do a stem project. i chose to build an algorithm to pull real world data and implement it into an excel sheet. just as i was thinking "how am i possibly gonna do this, there's no way there's just a video titled stock portfolio tracker on excel using real time data", i found this video. i actually started laughing out loud because this video is literally all i needed! thank you bro

[@rinaldofonti]

Successful people don't become that way overnight, What most people see at a glance wealth, a great career and purpose is the result of hard work.

[@Gabrielle..]

Firstly, start working with a professional investment manager if you have any, if you don't, I'Il recommend Expert Williams T. Kevin , He's really one of the best manager I'd trade with, he's going to guide you and give you the rundown on how to get started on a profitable investment journey.

[@c.moeller]

It's. fascinating to see someone that employ Williams T. Kevin investing services. he has been helping lot's of [people recover their losses from the crash, he's all we need right now. Investing has so much advantages as it is volatile, this is a perfect time to partake in it.

[@OliverFoote]

Extremely useful video. Been meaning to do something like this for 5 years, I broke down individual accounts and then made a "Mega" dashboard with everything on it, I also split up my CAD/USD trades with currency exchange. I really appreciate the solid baseline that your video created and taught me how to use a lot of very useful excel functions. Thanks!

[@anditonis2963]

this is EPIC!!!!!!! i am new to excel and i cannot reiterate how helpful this video was. thank you!

[@Rob_DK]

I am mindblown with the combination of features you used in this video. I will definitely use this in my spreadsheets! I am having some trouble though when putting different kinds of bonds into the mix (stocks + obligations + etfs). Do you have a video on how to make an overview for this? Keep grinding!!

[@kadagalaprakash]

Great work Kenji and even sold items also showing in Dashboard

[@OliverFoote]

Absolute legend. Exactly what I needed

[@thierrymartinez7722]

It

[@thierrymartinez7722]

his Te-le-gr am @A_aaron7

[@kylerear629]

[@yoanngirardi8106]

No one, NO ONE, can argue Aaron strategy at least currently, the best out there, even when Bitcoin is looking bearish with the market sentiment swinging low. But they kept us grounded with their real analysis, just the facts in the charts.😎

[@sarahmeagan8688]

Aaron's strategy has helped a lot of traders & newbies out there, and with their program one can recover losses from crash so quickly.

[@tamelaloretta8985]

The economic hardship, recession, unemployment and the loss of job caused by covid pandemic is enough to push people into financial ventures.

[@ruitavares3792]

Nice work Kenji 👍👍👍👍

[@WarioNeila]

Very nice portfolio! I'm copying this on Google Sheets! Thanks!!!

[@marcellusholley]

Great video. How would I add a split in this document or track dividends?

[@vigneshramji5112]

This was a brilliant video and spreadsheet ! Wonderful work. I wish you had shown how we can show a list of booked profits as well in this sheet.

[@adamboyle4954]

Legit excel skillz

[@KenjiExplains]

Thank you Salvador Dali 🧐

[@vendecy7351]

I’ve noticed a flaw in the excel if you bought 10 shares of a stock and sold 5 the excel doesn’t know what you’re purchased price is there a fix?

[@theodorenott3510]

Great video Kenji. really enjoy it.

[@willzinner8813]

stockhistory is a new one for me...cool one thanks

[@KenjiExplains]

Cheers!

[@eirikgjosund6410]

can you explain or make a video about how to include the cash position in such a portfolio (typically the portfolio breakdown)

[@chrisfencl8352]

This is a good video but the excel download that goes with this video doesn't have the company and ticker names in the transactions tab.

[@awanisharmaofficial]

Yes please address this issue Kenji.

[@williaminnes6635]

You guys figure out how to get stock type data to work in Excel without investing in 360? I'm logged in to my account. I can get the plug in that displays a stock price to work. Stock type data, however, just doesn't show up on the ribbon.

[@danteramirez1223]

Thank you Kenji! 🐐

[@KenjiExplains]

Haha thanks Dante!

[@digitaldood5252]

Is there a way to also have this track Cash when selling stocks, Dividends being reinvested, etc?

[@nicopiotton5975]

Fantastic work, thx a mil ! How do we set up the minimum of the chart to be dynamic according to the stock price? If the stock ranged into 100 to 110 for 6months let's say, the chart will start at $0 ...

[@adrianbock3576]

Very useful but needs ability to include realized gains to be able to work TRR etc

[@solomonbhandari-young4154]

Thank you Kenji

[@mbilly3013]

great video.. was looking for this, to pull the data from the transaction table to the dashboard using unique and filter. probably need to sort out in alphabetical order (I guess need to do it myself :-) ). actually want to know how to sort company name, market values, allocations, etc.

[@jlmcconchie]

I sure hope I have not missed the answer to my question and thus wasted your space in this excellent set of comments. As an income investor, I am most interested in the total return of a security, meaning its unrealized capital gain and income earned during a relevant period. Namely, what was the return on my investment taking unrealized capital gains and realized income payments (interest on bonds, interest on GICs, etc..)? How would you go about building a page for this? Thanks so much for any help you can give me.

[@kennylim4299]

hi Kenji, why do you do a Day Change column for? How is it useful?

[@imrandaud478]

Love your videos

[@KhanAtique]

Please make video that what Financial analyst do in regular job

[@Sunicharisma24]

Hi.. great videoes👍

[@andreugiro2807]

Excellent Kenji! 😄

[@KenjiExplains]

Thank you!

[@mathiastituana6540]

Well explained! Thanks for the vid and the template...!

[@KenjiExplains]

No problem!

[@jonathansingh1758]

Hi Kenji. This excel file isn't letting me insert recommended charts such as the pie chart. Any solutions?

[@rrtx250]

Awesome vid and channel, vids are so informative on financial topics

[@leonbeuth]

This is great how ever id like to add an “initial investment” column, calculated by multiplying the cost of the transaction by # of shares, but cannot for the life of me work out the formula. I have a similar transactions sheet. Any help?

[@sophies7854]

How can I incorporate dividends being reinvested into this sheet? My total gain is off because of fractions of shares being purchased over time! Thanks!

[@michaelquach7046]

The live stock data import is next level!

[@KenjiExplains]

I know so cool!

[@420ducats]

good content, thanks

[@sainigaurav11]

What about Realised (P/L)? What would happen to shares which are sold out (Buy Quantity = Sell Quantity). This portfolio doesn’t cover it. Thanks Kenji

[@michaelkuhn6328]

Excellent video

[@KenjiExplains]

Thank you very much!

[@philips7314]

Another great video Kenji, really enjoy your content. Was hoping to use your model as a basis for my own portfolio analysis tool, but want to include dividends and cash balance and some other analysis/statistics. However, it would be great to include a historic chart of the portfolio balance per day, just as a STOCKHISTORY but for the current portfolio instead of a particular stock. Any tips on how to approach that? Don't want to manually save the end-of-day balance, struggle to find a dynamic solution for this... Thanks

[@KrishnaShah-hp7su]

Yeh much required

[@trannhatvy92]

Yes! I loved the video and adapt for my portfolido but I found the same point with you, plus if you trade short term there's no tool to track/record the gain/loss actually realized after selling a part of all of a particular stock among the portfolio.

[@kennylim4299]

Any updates from here? lol. i"m also looking for that function.

[@joebowman4733]

How would you account for stocks that are traded within different markets that are in £,$, Euros, and even UK pence. Is there a way to automate this? I ahve had problems

[@KrishnaShah-hp7su]

Unique function doesn't works for my excel.. What should I do?

[@Sunicharisma24]

Can you please teach us tableau from beginning.

[@tristanontheway]

wow I want to invest in art!!

[@KenjiExplains]

nice! check the link in the description :)

[@charlescunliffe5427]

What will happen when a stock splits or reverse splits? Will it update or do I have to manually work that out?

[@KhanAtique]

Please make video on how to get data from bloomberg

[@KhanAtique]

Please make valuation of Apple from scratch to end as the Financial analyst do

[@thunderbird513]

I can't see "Data type" in my Excel 2021 LTSC . Do this feature available in excel 365?

[@nicolasdenis7094]

You must install english language pack for the data tab to become visible (file>option>language)

[@R32er]

i think this is only a 365 feature. i have 2021 too and see now data types

[@francescopellegrini221]

Is it possible to add liquidity? USD or EUR I have in the allocation

[@edwardsherman6309]

Think these features are only available in Microsoft 365?

[@LadyJulez]

Do you have a video on bonds portfolio need help asap

[@jdonis2932]

Thanks for your content this is better than taking an actual class.

[@KenjiExplains]

haha thank you!

[@0David0A]

HI Kenji, if want to add the cost/share based on all the buy and sell done per stock category. How do we add that to this excel?

[@MervynJardin]

Looks great but my ineptitude with Excel means the formula input does now work for me.

[@pranavvhankate2079]

Hey Kenji!! Really love your video...however there's a slight problem...the stocks data type is available for Office 365 users and I'm a student not having Office 365...can you tell me how to proceed or any feasible solution?

[@brittanygodin4499]

I must have missed the part about how to use live data - I guess I'm confused as to where you are pulling your data from and how things are auto populating... The excel download just seems to be a blank sheet.

[@ananasshow]

Your content is an asset. Keep going in this way

[@KenjiExplains]

I appreciate that! More to come hopefully :)

[@NammaReels8]

Hi when adding new stock on the transaction tab it doesn't automatically update on the dashboard. Please can ou help me with this. Thanks

[@moesadr3342]

Any idea on how to create a real time Technical Scanner signaling a special indicator/s event? like MV Cross over or MACD Histogram , etc ROC crossed above/below Zero. etc.?

[@neluto]

just wonder how you can do for crypto dashboard?

[@alfonsolopc]

In the Charting tab, when I use stockhistory for ETH/EUR for example, the list of prices is in USD. Is this a formatting issue only or is it not retrieving the right trailing prices? Similarly, in Charting, if I want to compare Apple's prices in EUR with other stocks from my Portfolio, how can I retrieve the prices in EUR and not USD? Once again the prices come up in USD with stockhistory, even when I select Apple from other European stock markets.

[@sfredheim]

Great video! Could you (or someone else) explain how I can add purchase price per share, based on the transactions? I find it hard to find the avarage purchase price, when there's different purchase prices. I found one way, but the complication is in a scenario where I have bought, sold and then bought again. Then the first purchase is included in the calculation, which messes up the avarage purchase price inmy current holding. Thanks :)

[@leodiaz7585]

Muchas gracias por compartir conocimiento valioso! una pregunta, en el gráfico comparativo las líneas no terminan en los precios exactos, por ejemplo GOOG vs AMZN: AMZN CERRO HOY 10/05/22 $2,177, sin embargo en el gráfico el último punto de la línea se ubica por debajo de $2,000.... Se puede mejorar la precisión o es solo para ver la tendencia? Gracias!

[@WalshStrategy]

Love it. How do I make an excel chart that compares return of two stocks over a given time period? Thanks dude

[@kgba17580]

hi how do i get this fund HSBC FTSE 100 Index Income C into my stock portfolio in excel please because every time i try it changes to HSBC HOLDINGS PLC (XNYS:HSBC) i have same problem with other funds to.. i am just trying to build portfolio of dividend's and funds. i live in uk and have Microsoft 365 kind regards kev

[@reelzerotuned3675]

I need this. Thank you!

[@KenjiExplains]

You're so welcome!

[@timothyy7]

how to add industry in google sheets?

[@hetfield9875]

The video is a good one unfortunately the tracker is not working with fractions of shares

[@saeednoroozi9640]

Hi thank you for your especial efforts I was going to ask you to send me this excel file because I can't download it Warmest regards

[@camlex6310]

So helpful!! Thanks

[@KenjiExplains]

Glad it helped!

[@fmuratori]

Can this be used with Google Sheets?

[@caetanabarros7405]

In the formula it gave me an empty array error. I don't understand the formula that you are using since you asking excel to see the stock names greater than zero not a number, so of course it gives me an empty array error because there are no names that can be bigger than zero, it is a stock name not a number. Can you explain this

[@ikingzeus69]

There is a slight error with the way excel has updated formula input. Try the following formula: =UNIQUE(FILTER(Table_1[Company],SUMIF(Table_1[Company],Table_1[Company],Table_1[Units])>0))

[@HarryMinutemands]

what excel version do you use

[@sakshamjain7334]

In sensivity analysis I'm getting #Div/0! in two cells.whats the reason

[@dader1969]

I have a problem on the date history it smth inlcudes bank holidays, So I get NA can someone help?? THX

[@AlexanderDiArmani]

I get an error when I use #. =E5# - gives an error but If I don't use it it works but it does not auto populate the cells. Help?

[@gabogainz]

Hey Kenhi, did you major in Finance or Accounting in University?

[@TheRafanadal23]

how to download stock data with a Mac?

[@kelvinlee8754]

I click data , stocks, choose my " company". It is show USD by default. can i change to other currency such as UK? SGD, JPY? Thank you

[@KenjiExplains]

Hey hard to say but it might have to do with how your excel cell is formatted. For example if you have USD format, even if the number is in JPY, you'd still have the USD sign.

[@lipsin1976]

Hi. How does the prices get updated when you open the file next day? It doesn't get updated for me. Thanks!

[@lipsin1976]

I saw it. Thanks! Great video! :)

[@davidbartos15]

Its not working for me, i was always working in Excel or Sheets Online with Google. May it be because of it ? When in transaction i wanna writte " Apple" it doesnt fill out anything .

[@emadr5780]

Gracias!

[@KenjiExplains]

Gracias a ti por ver el video!

[@aminelazreq5614]

Excellent video ! Thank you so much and cannot wait to see further of your content !!

[@KenjiExplains]

Thank you, more to come!

[@markmiller3024]

I downloaded the file to create the portfolio tracker in excel and found on the transaction screen that the Icon for the share information for the stock was not there. Therefore when I loaded the stock tracker add-on, I still was not able to get the information about the stock. Would you please send me the file with the Icon column already in place. Thank you

[@KenjiExplains]

Hey some versions of microsoft excel don’t have access to stock price data. Are you using micrsoft 365? If not you might not have access unfortunately

[@geluu89]

Could you do something similar for crypto coins?

[@tf9151]

how do I find SPY ETF?

[@Bshiraz]

Hey Kenji! Is there a way to set up projected returns in this file? i.e.) if you want to invest in either Apple or Amazon and play with hypothetical projections. Is there a way to see which one will return the most based on capital allocation/how many shares you invest? Let me know if this made sense

[@miamiwalking]

Flodesk did not send me the download after I submitted my email address. Flodesk not ready for prime time?

[@KenjiExplains]

Check your browser downloads / computer downloads folder

[@anuragpandey6766]

CTL + T is not working initially

[@youngtrier2474]

May I ask which university you attended in Spain ?

[@KenjiExplains]

I graduated from Cornell University actually (it’s in the US)

[@youngtrier2474]

Thank you for your response! Didn’t you mention you attended a Spanish university before transferring to Cornell (in a other video) ?

[@miraii_21]

hi can you alaso do crypto?

[@Bradventurouss]

Did anyone receive the Excel Sheet template? I submitted my email and still haven't received anything

[@turbed32]

did anyone get this to work?

[@davidallen-hf1ux]

too bad the download doesn't work correctly.

[@KenjiExplains]

How so? You should get an email with the file

[@nowuknowthis1903]

didn't work, excel file has errors, #Value! no drop down, not stocks.

[@josephkangandjera2054]

Hi how can I get a hold of you? Email? Social?

[@MervynJardin]

Oops, I meant to say does NOT work for me.

[@ukaszapinski2909]

That video was awesome, thanks

[@KenjiExplains]

Glad you liked it!

[@fotiosvellios3346]

Amazing video Kenji. Honestly so much fun watching you do these! One question for you, do you think comparing the returns would give us a better representation of which stock is doing better/worse? Like you said when comparing AMZN and WMT, they do have different prices and SO so would it not make more sense to compare purely based on returns? Would love to hear your feedback!!

[@KenjiExplains]

Yes I agree with you! It's just that it would have been an extra step on the video and it was already getting long, so seeing the overall trend was still good enough :)

[@fotiosvellios3346]

@@KenjiExplains makes sense sir! Appreciate your content, as always