How to build your stock portfolio with Excel | Investing 2020

[@Zetunez]

I respect a man that enjoys wood finishes.

[@josiefrench7672]

Financial investment can be emotionally exhausting/frustrating in a case where inconsistency in trade wins is much. Positive results are guaranteed more if one works with a reliable professional. My most sincere take though!!

[@lennonbrady8444]

I share the same view. Tried to make a start on crypto investment but was almost frustrated out by heavy loses till JOANNA MALIVA LEE was recommended to me. She has been managing my portfolios for a while now and have never faltered

[@christopherkraft5553]

@@lennonbrady8444 Wow, so grad someone is talking about Joanna Maliva Lee, her investment strategies are top notch. Have been following her guidance

[@josiefrench7672]

@@christopherkraft5553 How do I contact her?

[@hennykelly7535]

@@lennonbrady8444 I Just look her up on the web, wow! Quite Impressive. I’m definitely investing with her Thanks!

[@CarolynELane-my5ch]

@@christopherkraft5553 Much thanks

[@birks4429]

What happens from 3:20-3:21? The table with the numbers and stuff magically just appears on the screen.

[@andreslbandre]

Hi, Can you share the excel? I can't follow you :( to fast for me Thanks

[@Pixmmxie]

You have no idea how helpful this was man!!

[@jmindl]

How I can reflect in the transaction history if there are stock split or reverse split?

[@dylanlawrence514]

how would you get your sheet so all stocks are in the same currency. Some of my transactions are in £ and some in € and $. Will this not mess it all up?

[@jimmysanders1375]

When you do your SUMIF formula, doesn't it also account transactions where you sold? Therefore, I don't think it is accurate?

[@viorel-]

Have you found a fix?

[@jimmysanders1375]

@@viorel- Just put a negative in the quantity whenever you sell and you will be good

[@viorel-]

@@jimmysanders1375 Yeah, it did work, it;s just one of my stocks shows a really weird gain percentage that is wrong, need to figure it out somehow.

[@viorel-]

@@jimmysanders1375 everything else works though. Thank you!

[@runningforglory]

how did you get your portfolio value for each day? manually? i hope not!

[@Akas410]

How do you record realized profit or loss using the spreadsheet you have proposed? For example if you conduct a sell transaction at a profit it simply reduces total quantity and total market value/cost. But that doesn't account for the profit you made. Please advise on this!

[@garyfloyd5445]

I found this useful up until I tried to process "sells". e.g. A sell transaction with a large profit will skew cost-per-share to negative and throw basically everything off, making the Portfolio sheet useless. "Sells" need to be treated by reducing the original amount purchased, then track the remaining holdings going forward.

[@johnchan9032]

I am new to this and this week I have tried duplicating your work with my profile. However I realized that (Average Cost Per Share) will become in accurate if I have repeated transaction for the same stock. So lets say i have transacted on the same stock for 4 times, and sold partial of the stock twice and bought it twice as well. Because of the “profit” in sold amount for the transaction history tab it had resulted in a lower percentage of cost per share. Is there anyway I can override this issue?

[@mohanpuri9917]

Very well explained and easy to build method of tracking performance of a portfolio.

[@kev1nme1er]

First, thank you for the informative video. I was previously using Google Sheets because it has a couple of functions not available in Excel for getting data from web pages. Specifically ImportHTML() and GoogleFinance(). I was looking for similar functions in Excel and your video showed me how I can work around those. Now for the criticism. Not everyone is an Excel god like you. You go way too fast to following so had to rewind many many times to get to where I am at. Next, you move on to performance where you download data from Yahoo and then indicate to just import the one column next to your existing data in Sheet4. Unfortunately, I saw nowhere in the video where you created Sheet4 so I was done at that point.

[@WinnieTheTrain]

Yeah this dude went too damn fast, i now need to watch another video, I was looking how to do this and learn excel...

[@as8crew74]

Thank you so much. This helped a lot.

[@randyramos4958]

You literally same from tipping all of that info, Thank you very much! Bless you!

[@josephvillatoro4215]

do you any chance have any template prebuilt that you can share? it would be very helpfull.

[@dook5412]

What happens if you sell all of the quantity of a stock. Say you bought 10 shares on 1/1/2020 in AAPL but then sold 10 shares on 31/1/2020.

[@investingwithperspective1034]

In that case, your portfolio would just show 0 quantity for AAPL as the trades in the transaction history table would cancel out. Does that answer your question?

[@viorel-]

@@investingwithperspective1034 The formula doesn't seem to substract any value tagged as "Sell", how do I make it account for sells?

[@Carrot-cb9nv]

@@viorel- You can just insert a minus wherever you sold something. Or if you really want you can account for it by using some sort of an IF function. Where you would tell excel if the cell says "Sell", make the amount a negative.

[@dovid-19]

I'm so confused, when did you create the balance sheet? What is a balance sheet?

[@lil__lad]

3:24 What if you want to record a buy of 30 shares at date X and then a sell of the same 30 shares at date Y? It would give a divide by 0 error and report a Total Loss of -100%.

[@jmindl]

I found out that if you have SPLIT in the stock and you simply write instead of BUY or SELL word SPLIT. Once in the minus amount of share changing to higher or lover amount (in REVERSE SPLIT) then for split it all works, but you must to correct formula for costs column as follow =SUMIFS('Transaction history'!F:F;'Transaction history'!B:B;[@[Ticker symbol]];'Transaction history'!C:C;"BUY")+SUMIFS('Transaction history'!F:F;'Transaction history'!B:B;[@[Ticker symbol]];'Transaction history'!C:C;"SELL")

[@GGPHelp]

Thank you. It was very fast and very hard to follow along with. - but have a working table. - Cheers

[@lawrenceg7341]

For the comparing with SP500 part( sheet 4), would it also require you to have YTD from your own portfolio ( like column A&B)?

[@analynoclarit564]

hi, may i ask what's the formula for sell? thank you!

[@tza5795]

What you suggest to implement merging situation? 1st Co. to be merged (factor x for its shares and for the merge price) into 2nd Co.

[@CJ-tr3ji]

Extremely helpful. Gave you praise to a colleague. So does Appfolio do the same thing or does Excel do it better?

[@IIgleebearII]

Your formula for quantity doesn't seem to discount shares of the same stock that have been sold already, and instead just adds them up with shares that have been bought. Can you fix that part please?

[@Alex77439]

Very good thank you!

[@leogatineau4716]

Cheers for the video! What should I do if I am buying using GBP but am buying both US and Uk stocks, how do I make excel understand this?

[@ljtheiss]

Robinhood doesn't allow more than one portfolio, so hoping to make a version of this which allows me to subcategorize. Long term investment portfolio vs the money I'm just throwing in for some return while waiting to buy Cybertruck portfolio for example.

[@alexisflores4013]

Thank you so much! This was very helpful.

[@ashikurrahman6409]

Can I use it MS excel 2013???

[@raananmilshtein8573]

amazing content bro, just what I came looking for :)

[@derekhiggs1513]

How did you merge the two tables at min 3:19. I was watching but that step got glossed over

[@davidjames1196]

I was wondering that question as well Derek - did you ever find the answer?

[@leogatineau4716]

@Derek Higgs @David James , its at the bottom of the sheet there is an option to add new pages to the same document

[@biancabenjamin4999]

@@leogatineau4716 I believe his question was referring to the fact that Transaction History was empty than it was filled in with information 3 seconds later. So he would like to know what was the step to inputting the values of each company from the previous sheet. Am I correct? @Dereck if so I would like to know too☺️

[@yourvoice3502]

when I go to data there is no stocks option !!? do you know the reason by any chance I'm using the latest version of excel on Mac . thanks

[@investingwithperspective1034]

To my knowledge, Microsoft has only made it available through the subscription based Office 365 version of Excel 2019. So if you don’t have Office 365 then I’m afraid you won’t be able to use the function :/

[@leogatineau4716]

ALso if you are using 365 try and restart the software, reinstall it and also logging in and back out. that made it work for me

[@Kiki-lp6sr]

GODDAMNNN this is good stuff

[@strangergranger10]

Is there a code for what type of dividend a company is paying???

[@ctialloo580]

Very useful! Love it

[@subhashsansarwal3914]

Do you know how we can keep track of realized gain & loss separately? Because most of the videos that I have seen only shows to track unrealized gain or loss.

[@dan007grthedunkmaster9]

Well i read in a book about morginstar and after i made my portofolio for free i loved it.It is very helpfull just dont know if it calculates divs so i am still learning how to use it.It is free and i think the best site i have used by far .You can see the day change,realised changed ,unrealised change ,and bench mark with the cash you incerted 100% reccomend

[@subhashsansarwal3914]

@@dan007grthedunkmaster9 Thanks able to figure out how to do it in excel using sumifs function.

[@Akas410]

@@subhashsansarwal3914 How did you do it? I'm trying to figure this out too.

[@valentinklotzner8851]

How can I get my year-to-date balances for my portfolio? so that I can compare it to the S&P 500.. thank you

[@investingwithperspective1034]

Hi Valentin, you can find out what your balance was at the start of the year and place that into the portfolio tracker. You can then work out the year to date return by using the formula YTD return = (Current balance / Jan 1 balance) - 1

[@valentinklotzner8851]

@@investingwithperspective1034 thank you very much! Great work and very useful. Looking forward for more videos

[@investingwithperspective1034]

Thank you for your support!

[@kgba17580]

hi how do i add vanguard LifeStrategy 80% Equity A ACC into my excel spreadsheet please :)

[@jandietrich1594]

The fact that he did that on a Mac.

[@ice2787]

What am i gonna do if excel does not recognize the stocks?

[@mkhbr6392]

Do you have to manually input the portfolio and S&P closing value? can it be done automatcally?

[@user-he7sc3fg3r]

@Yeou Ling stfu

[@musikcomedymagic]

hey great video! However what do you do if the stocks are different currencies? Since excel cannot register a portfolio with different currencies

[@Carrot-cb9nv]

You can get exchange rate data from excel in the same way you got the stock data. For example if you type USD/AUD in a cell and then change it to the stock data type excel will give you the exchange rate. You can then convert something that is in US dollars to Australian dollars by multiplying the amount with the exchange rste or you can get something that is in Australian dollars to US dollars by deviding with the exchange rate. Hope this makes sense!

[@rohanumate]

Why u stop uploading your video ?

[@inspirationalcounselling3589]

why doesnt this formula work? =SUMIF('Transaction History'!B:H20;[@[Ticker Symbol]];Transaction History'!D:D) im not sure where ive gone wrong

[@edwardchau5818]

How did you link excel to the stocks data at 2:12?

[@edwardchau5818]

To be clear I have Microsoft 2019 (not 365). Is it only available in 365 or can I update my excel somehow?

[@issadabain7116]

hey, I am working on a similar project where I stopped with the case when you sell partial quantity and then rebuy again from the same stock ex. when you buy 10 shares of Apple @100$ then sell 3 shares @120 and after that, you rebuy 5 shares @110$, the method I'm using is the weighted average cost of the share. do you have any idea how to work that out?

[@jxxonchan]

Hi, im facing the same problem as you. Have you figured it out?

[@shadowseo4988]

I have exactly the same problem as u. Have u figured it out? Thx.

[@EmilioCalderonStocks]

Good job really ❤️

[@nickp269]

Hey, for the sumif formula, how does it work if you have two of the same stocks. I keep getting #spill error and i'm assuming it's because i more than 1 of the same stock in my transaction history (buying more, of the same stock, lightening positions ect). Thanks

[@maumautg]

why is it so impossible to find someone calculating average cost correctly (It's not the sum of your cost divided by the sum of your quantity, as soon as you sell anything). If someone knows of a video teaching how to calculate it in excel, could you please share?

[@futurefinances2480]

Wayy to fast dude fried my head ahahaha, good shit though thank you!!!

[@epicboom]

Can you please send me this workbook

[@TheCANTBELIEVETHIS]

I'm glad to hear I'm not the only one struggling here

[@wafflezplay6422]

Amazing

[@bhavneshparikh8283]

provide excel file

[@vincemucci2506]

Can you make me the spreadsheet for my buy and sells with dates

[@鼎太瘋]

敲碗！啥時會有副頻道

[@chairezhuntz]

BUT WHY ARE THE GLASSES JUST HANGING ON THE WALL??

[@danielwang5037]

👍

[@inspirationalcounselling3589]

sorry i meant to type this..... =SUMIF('Transaction History'!B:B;[@[Ticker Symbol]];Transaction History'!D:D)

[@pnwguy5551]

Sorry going way too fast for me and probably 3/4 of the people below in the comments.

[@greygoosel2412]

Maybe you could go a little faster, hard to keep up

[@annegreen153]

The ritzy jump coincidingly amuse because digital generally thank than a tall weasel. innate, protective kitchen