How to build your stock portfolio with Excel | Investing 2020

Подписаться 1 тыс.

Просмотров 101 тыс.

50% 1

Welcome to another episode! Let me show you how you can build your portfolio using Excel. This is something that has helped me along the way and sharing is caring! As always, make sure you do your research before buying any stock.
How to create a stock watchlist: • How to create a stock ...
Follow me on instagram: / perspective.investing
--------------------
Tools used in this video:
Canva - www.canva.com
Clip2Comic - apps.apple.com/us/app/clip2co...
iMovie - v10.1.14
--------------------
Disclaimer:
The content in this channel is for educational purposes only. This channel does not provide any financial advice nor should the content be taken by the viewer as financial advice in any way. To the best of our knowledge, the information provided is true, accurate and up-to-date. We do not accept liability for any losses to your investments arising directly or indirectly from the reliance on the content in this video and this channel. The viewer should conduct their own research and make their own judgements and is encouraged to seek independent financial advice.

Опубликовано:

1 авг 2024

Ссылка:

Скачать:

Готовим ссылку...

Добавить в:

Мой плейлист

Посмотреть позже

Комментарии : 100

@Zetunez 3 года назад

I respect a man that enjoys wood finishes.

@randyramos4958 3 года назад

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

@Pixmmxie 2 года назад

You have no idea how helpful this was man!!

@alexisflores4013 3 года назад

Thank you so much! This was very helpful.

@raananmilshtein8573 3 года назад

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

@ctialloo580 4 года назад

Very useful! Love it

@as8crew74 2 года назад

Thank you so much. This helped a lot.

@josiefrench7672 2 года назад

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 2 года назад

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 2 года назад

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

@josiefrench7672 2 года назад

@@christopherkraft5553 How do I contact her?

@hennykelly7535 2 года назад

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

@CarolynELane-my5ch 2 года назад

@@christopherkraft5553 Much thanks

@Kiki-lp6sr 3 года назад

GODDAMNNN this is good stuff

@tza5795 3 года назад

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.

@mohanpuri9917 3 года назад

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

@GGPHelp 3 года назад

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

@Alex77439 2 года назад

Very good thank you!

@birks4429 2 года назад

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

@lawrenceg7341 2 года назад

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

@wafflezplay6422 4 года назад

Amazing

@CJ-tr3ji 3 года назад

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

@jmindl 3 года назад

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

@EmilioCalderonStocks 3 года назад

Good job really ❤️

@leogatineau4716 3 года назад

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 3 года назад

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.

@johnchan9032 3 года назад

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?

@Akas410 2 года назад

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!

@dylanlawrence514 3 года назад

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?

@lil__lad 3 года назад

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%.

@andreslbandre 3 года назад

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

@josephvillatoro4215 2 года назад

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

@analynoclarit564 3 года назад

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

@jmindl 3 года назад

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")

@musikcomedymagic 3 года назад

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 3 года назад

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!

@ice2787 3 года назад

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

@user-xz1id2rb6q 4 года назад

敲碗！啥時會有副頻道

@nickp269 3 года назад

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

@strangergranger10 3 года назад

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

@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?

@ashikurrahman6409 3 года назад

Can I use it MS excel 2013???

@kgba17580 6 месяцев назад

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

@dovid-19 Год назад

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

@danielwang5037 4 года назад

👍

@mkhbr6392 3 года назад

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

@user-he7sc3fg3r 3 года назад

@Yeou Ling stfu

@kev1nme1er 3 года назад

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 7 месяцев назад

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

@jimmysanders1375 3 года назад

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

@viorel- 3 года назад

Have you found a fix?

@jimmysanders1375 3 года назад

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

@viorel- 3 года назад

@@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- 3 года назад

@@jimmysanders1375 everything else works though. Thank you!

@yourvoice3502 4 года назад

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 4 года назад

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 3 года назад

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

@inspirationalcounselling3589 3 года назад

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

@vincemucci2506 3 года назад

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

@subhashsansarwal3914 3 года назад

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 3 года назад

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 3 года назад

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

@Akas410 2 года назад

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

@valentinklotzner8851 3 года назад

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 3 года назад

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 3 года назад

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

@investingwithperspective1034 3 года назад

Thank you for your support!

@issadabain7116 3 года назад

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 3 года назад

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

@shadowseo4988 3 года назад

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

@maumautg 2 года назад

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?

@derekhiggs1513 3 года назад

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

@davidjames1196 3 года назад

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

@leogatineau4716 3 года назад

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

@biancabenjamin4999 3 года назад

@@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☺️

@dook5412 4 года назад

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 4 года назад

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- 3 года назад

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

@Carrot-cb9nv 3 года назад

@@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.