Amazing! Exactly what I have been looking for and more! Love the touches with all the additional charts and trackers. Thank you for sharing all the formulas. Probably going to need to go through the video a few more times to build my version, and that will be a nice weekend project.
I've been searching for a nuggets like this for a while this is fantastic. Definitely deserve more likes! Thank you for the effort in uploading this amazing!
Nice job! Could you add a graphic so when you hit your stock/crypto you can see its performance over time. Ex If I want to see if apple was maxed and I should have sold or bottomed and I should have bought relative to my ownership of the stock. Allow it to reflect also return in a separate graphic representation. Ex. Bought BTC In march, June and a month ago, what is the performance. 2 different tweeks to see performance over time. Great work! Keep it up!
Hey Frank, this is a great idea. Thanks so much for your suggestion. I will apply it in the 2nd Gen Tracker I'm currently developing. Stay tuned! Cheers
Thank you for your videos could you please make a crypto Portfolio Growth Chart (hourly - daily - monthly and yearly ) with timestamps, i mean record all the price changes (history) that happen to the portfolio balance overtime thanks.
This is a great video. In the Currency Converter portion of the tutorial, your screen shows an error message after you enter in the formula in the Avg. Price error and mentions that it has a problem with GBP, but then you don't really show how this was resolved, and it just jumps to you pasting the formula to the rest of the cells. Is there something in the formula that needs to be fixed in order for this to work properly? Thank you.
Hi Ryan, thanks so much for your comment. You are right, there is an error and it happens because in certain occasions Google finance cannot find the currency for stocks in GBX. If you continue watching, there is a column in Transactions that we haven't completed yet (column K). This formula in the Currency Converter will not show errors once column K in 'Transactions' is completed. Basically acts as a last option if the currency was not found. Thanks for letting me know about this, next time I'll explain in the video why the error temporarily shows. Sometimes I fast forward a few formulas because I might skip explaining every single formula but this is one I should've explained. Thanks again and please let me know if you need help with anything else! Cheers, PF
Great work - but a quick question. If the same holding is found in two portfolios, it seems to mistakenly put all the holding in the first portfolio it was found in. I think this is because of the summaries in the currency conversion tab. Before I take a shot at fixing, just want to ensure this is a mistake. Thanks!
Hey! Thanks for your comment. I have fixed this in the newest version of the portfolio tracker. Feel free to check it out and let me know if you have any further questions! Cheers
Hi! Thanks so much for your comment. In this version there is no way to list them separately as they are automatically removed by the script if sold all. Feel free to check out the 2nd Gen Tracker where there is more detail on the "Sold All" positions.
Life saver! I have a small problem which I have emailed you about. When I enter the transactions for £IUSA ETF my portfolio goes to NUM errors. Is there anyway we can fix this? I have no idea what I am doing and was lucky to find your tracker.
Hi Kyle, thanks for your comment! Make sure to enter the ticker for this ETF including the exchange symbol. I believe it should be LON:IUSA. Alternatively, you could build/download the Portfolio Tracker 2G which includes a feature for price fetching from Yahoo Finance if Google Finance doesn't find the ticker. Cheers!
@@PlanetFinanceOfficial Hi mate, it’s found the ticker but the current price on currency converter is showing in GBX whilst the rest is GBP. This is showing my gain as 9000% as my average cost is £33.30. Is there a formula I need to change? Many thanks
Hi bro, I've been using it and everything good, but i actually have more than 157 transaction and got this error "Exceeded maximum execution time (line 0).". Do you know how to fix it? I think it's because custom functions have a limit of 30 seconds but don't know the solution. I appreciate your work, greetings from Argentina
Hey! Thanks for your comment! I believe you get this error when the custom formula is not set properly. Did you see this error with the first transactions? It might be an issue with the order and syntax of your transactions. E.g if you buy something without an initial cash deposit transaction or if you enter a buy transaction that's missing a date or a price. Let me know how you go and if you need any further help I'm always happy to help. Cheers!
Looks amazing! Just wondering if it is possible to automate recurring buys for example if I wanted to dollar cost average each month into a stock. Could it be possible to like schedule a transaction to be repeated so I don't have to go in each month and copy and paste it?
Hi Eddie, thanks so much for your comment! Unfortunately I haven't added the capability to automate recurring buys in the transactions as every entry is manual and if you were to DCA it needs to find the price you bought it for. This tracker is not linked to any broker website to the only way to record the price at which you bought it for is manually in the transactions tab. It's a great idea though, I'll look into it to see if there is a way to make this more effective and I'll apply it in the upcoming 2nd Gen tracker. Cheers!
Hi, this is great, I'm just starting using it, but it seems that the brokerage fees are not taken into account on a 'Buy' transaction. So they are not deducted from the portfolio return. Is this by design ? Can you explain why ? Thanks 😀
Thanks so much for your comment! Yes, the standard formula doesn't include it for buy transactions. In the ready-to-use version I've written down a note with alternative formulas that take it into consideration: Standard Total column function only subtracting brokerage fees from Sell orders: =IFERROR((IF(ISBLANK(D3),"",IF(F3="Sell",G3*H3-I3,ABS(G3*H3)))),"-") If you want to deduct brokerage fees from Buy orders enter the below formula in cell J3 and then drag down: =IFERROR((IF(ISBLANK(D3),"",IF(OR(F3="Sell",F3="Buy"),G3*H3-I3,ABS(G3*H3)))),"-") If you want to add the brokerage fee to Buy orders enter the below formula in cell J3 and then drag down: =IF(ISBLANK(A3),"",IFERROR(IF(F3="Sell",G3*H3-I3,IF(F3="Buy",G3*H3+I3,ABS(G3*H3))),"")) If you want to add brokerage fee for Buy Orders and deduct brokerage fee from Dividends (could be used for tax), enter the below formula in cell J3 and then drag down: =IF(ISBLANK(A3),"",IFERROR(IF(OR(F3="Sell",F3="Dividend"),G3*H3-I3,IF(F3="Buy",G3*H3+I3,ABS(G3*H3))),"")) These are a few of the options available. Feel free to try them! I'd recommend you take a look at the 2nd Gen tracker I released (linked in the description) as it has better capabilities. Soon I'll release a 3rd Gen that improves many features. Stay tuned! Cheers, PF
Great Video! Now, I have transactions where I Convert one cryptocurrency for Another cryptocurrency. Any chance that you have any tips to implement this? I'm trying to find a way to accurately track my cost basis mainly for tax season
Hey Jason! Thanks for your comment. This tracker works only when you buy and sell cryptos in one platform. It doesn't support converting from one crypto to another or sending to another platform. I'm currently working on a second generation tracker similar to this one that will include such capabilities. Stay tuned!
@@PlanetFinanceOfficial Awesome! Thank you very much for the quick response. Keep up the great content, this channel is going to the moon.. I’m looking forward to seeing your next tracker creation!
Hi Mattias, thanks so much for your comment. If Google Finance doesn't support the Norwegian stocks what we can do is help you create a custom formula that will extract the prices from another website like Yahoo Finance. Feel free to email me the stock list and I can help you create a formula. Cheers, PF.
Hi Ssinseeme, thanks for your comment! Try this formula: =IF(ISBLANK(AD3),"",SUMIFS(Transactions!$M$3:$M,Transactions!$H$3:$H,"Sell",Transactions!$D$3:$D,AD3)+SUMIFS(Transactions!$M$3:$M,Transactions!$H$3:$H,"Dividend",Transactions!$D$3:$D,AD3)) AD3 is the Stock ticker. Transactions M is the Total, Transactions H is the Action, Transactions D is the Ticker. You will have to edit the formula to fit your tracker as this is the formula for the new upcoming version. This formula has been applied in the Portfolio Tracker 2nd Gen that will be released in the upcoming week!
@@PlanetFinanceOfficial Thanks for the reply, unfortunately, it didn't work, for example, I bought 5 shares of ZM@200 and sold 1 share @250 the gain should have been 50$ but the formula returned 250$. I will wait for your update video.
@@ssinseeme My bad, the formula should be: =IF(ISBLANK(AD3),"",SUMIFS(Transactions!$M$3:$M,Transactions!$H$3:$H,"Sell",Transactions!$D$3:$D,AD3)+SUMIFS(Transactions!$M$3:$M,Transactions!$H$3:$H,"Dividend",Transactions!$D$3:$D,AD3)-SUMIFS(Transactions!$M$3:$M,Transactions!$H$3:$H,"Buy",Transactions!$D$3:$D,AD3)-SUMIFS(Transactions!$M$3:$M,Transactions!$H$3:$H,"DRIP",Transactions!$D$3:$D,AD3)+SUMIF($AD$3:$AD,AD3,$AK$3:$AK)) We add up up the cost at the end, AK is the cost column in this formula.
Hi Thank you so much for this vide, I just became a patreon but couldnt find this video specific or the video on cyberpunk style, could you please help me on this? thanks
Hi ivan, thanks so much for your comment! There is no specific video per theme, one applies to all themes as the trackers are exactly the same just with different colour schemes. Thanks so much for joining my Patreon and supporting my work. I've sent you a private message with the post that takes you to the Cyber Punk Theme.
Hey! Unfortunately I don't have this yet. You could take a look at the day trading tracker but happy to add forex into the pipeline of upcoming trackers.
I’m having issues in filling cash and realised gains in portfolio balances. I changed the names of respective columns in the transactions tab. Help me.
Hey, you need to make sure to follow the correct sequence: cash deposit, buy asset, dividends and then sell. The formulas in portfolio balances reconcile the numbers by making sure the actions uses have a specific text. Please let me know if you need further assistance!
@@PlanetFinanceOfficial got it. Can u help me with dividend income formula =SUMIFS(TransactionsTotalConvert,TransactionsDate,">="&$C$21,TransactionsDate,"="&$C$21,TransactionsDate,"
If I enter any more than like 20 transactions in the transactions tab, the portfolio tab formula comes up with an error that says "exceeded maximum execution time." Do you know what the problem could be? Like, is it the script that is unable to sort through all the data in the transactions tab? If so, any suggestions on a possible fix?
Hey Brad, make sure to enter transactions with the correct sequence (cash deposit, then buy security). Check out the extra info in the formula sheet that I've shared in the description. If you still encounter issues feel free to email me and we can solve your issue. planetfinanceofficial@gmail.com
Thanks Bro. Found the mistake I named the Transaction sheet wrong. Got it corrected. Works great now.... Thanks for the help Bro. You are doing such a great work. This is exactly what I and many need. Got great skills 👍 Keep it up Bro. Thank You
Hey, thank you so much for the videos am planning to join your patreaon. wanted to ask if its possible to get get Etoro informations Like the copy trader information and what stocks they bought and sold and how much money you have invested as well as P/L from them. it would be a great addition for the 2nd Gen Tracker if you feel this is a good point. :)
Hey! Thanks so much! You can enter trade information from any platform as long as you follow the correct sequence. E.g deposit cash transaction, then buy stock, then sell, etc.
@@PlanetFinanceOfficial thank you so much for the reply. Yes, but the problem with eToro is that i don’t exactly see the transactions made by the copy traders.. if I go in and check each trader then it’s going be to be a Herculean task… If we can download the spreadsheet from eToro, is it possible to pull the data?
@@akkintouch Can't you download a spreadsheet from eToro with your transactions and then try to fit the data as per the columns available in the Transactions tab? Unfortunately this tracker requires manual input of transactions as it's not pulling data from any platform but Google Sheets.
@@PlanetFinanceOfficial yup am trying that.. but Etoro’s downloaded spreadsheet is very confusing and not very user friendly for beginners.. I will try to decide it and put it in the google sheets. What should I do with different exchange? Like gbx or chf or eur…. I understand we can change the currency in this portfolio sheet but how to make sure google finance link the correct ticker of the different exchange.. Sorry about a lot of questions…
@@akkintouch just make sure to enter the Transactions with the currency that you are trading in. There's a column where you specify that currency so the formulas in the tracker will capture that and convert to an overall currency for precise total numbers. Make sure to check out the Formula Sheet link in the description where I show a screenshot with an example on how to enter transactions.
im getting #NAME? or #REF? error after entering =Positions/Investments(Transactions!D3:D,Transactions!F3:F,Transactions!G3:G,Transactions!H3:H) Any idea how to fix this?
@@PlanetFinanceOfficial thanks for the reply, how exactlly do you grant permission to Gaccount? Ive been searching it up but there doesnt seem to be an option where I could enable it in "My accounts"
@@mrpax2303 When you click Run after pasting the script, Google shows a pop-up warning you about an unofficial Google script. You just need to click continue with the script and it should be enabled with your account
Hi, thanks for this. I bought Glen on LON exchange where is listed in GBX. However, your spreadsheet seems to work only with GBP currency. This makes calculation difficult. For example, today LON:GLEN values GBX 347.50 and your Currency Converter sheet lists 405.92 current price (EUR). I understand therefore that the spreadsheet thinks that 347.5 is GBP, instead of GBX. The only solution I see here is that I multiple the number of share for the conversion factor between GBX and GBP. What do you think? Thanks!
Hi Danilo! Thanks for your comment. If you enter GBP in 'Transactions' > Currency Column for LON:GLEN instead of GBX, the conversions should work. Let me know how you go, if you still find an error I'll help you customize a formula.
@@PlanetFinanceOfficial Hi - this is a really great tracker, thank you. But I'm also having the problem above. I've listed Aviva (AV ticker) as GBP but it thinks I've 100x my investment as it's showing as £403.1 instead of £4.03 in the currency converter and portfolio tabs. Is there a way around it?
Amazing work! It really helps me build my own. But I'm currently having some problems when I pull the price for some crypto currency. The price cells are getting populated but if try to refer that cells for a simple A*B function I'm getting the error "Function MULTIPLY parameter 2 expects number values. But '$0.7628' is a text and cannot be coerced to a number.", "$0.7628" being the price I get with the importhtml function. Any suggestions on how I can format that cell to overcome this issue ?
@@PlanetFinanceOfficial Hi there! First of all, many thanks for this video - very useful! I have the same issue as Ionut Florin but unfortunately, changing the cell formats to "Currency" doesn't change anything... is there another walkaround? Thank you!
@@marcoweiss3618 Hey! Thanks so much for your comment. I'm really glad you have found this useful. It seems like the function might be pulling the prices with the $ sign as well. To fix this you can add the VALUE function in front of the formula for prices and it will convert it to numbers you can manipulate. I'll double check the function, maybe coinmarketcap changed their API and I'll need to edit the formula and notify those who downloaded it. Cheers!
@@PlanetFinanceOfficial its done, i think the problem was my google chrome, when i paste the script with edge it workss so well!! thankyouu so much! i think your channel deserve more
im getting dates as average purchase prices after i enter in formula "=Investments(Transactions!D3:D,Transactions!F3:F,Transactions!G3:G,Transactions!H3:H)". any tips on how to fix this
Hi Avishek, please make sure that you copied the script from the resource shared. Also, you need to make sure the fields in 'Transactions' match the ones I entered. Specifically columns D, F, G and H. If you still get an error feel free to email me your sheet and I can help :)
@@mrpax2303 I meant make sure to enter the correct figures in the transactions columns. e.g. stock ticker should go in the column where ticker is meant to go, just like in the video. Let me know how you go!
I followed the instruction as provided in the video but I keep getting an error. Can you help please. Error below; Error TypeError: Cannot read property 'map' of undefined (line 38). and below is the script I had copied and pasted in Script editor; (Yellow highlighted is line 38) if(action.toUpperCase() == "SPLIT"){ let activeTrades = portfolio.get(ticker); let ratio = quantity[i].toString().split(":"); activeTrades = activeTrades.map()((trade) =>{ trade.shares = trade.shares * ratio[0]; trade.price = trade.price / ratio[0]; trade.shares = trade.shares / ratio[1]; trade.price = trade.price * ratio[1]; }); } Please help and thank you
@@PlanetFinanceOfficial Yes, I realized I put an extra open parenthesis. I was trying to make it work and didn't realize. After copy pasting the FIFO Script again still getting the error and also sent you an email with updated script as per you suggested but still getting an error. Thanks again.
Hey Enzo. If the managed fund's ticker is available in Google Finance you could add it like we add stocks or ETFs. Of it's not, you could add the transactions you've done by using the units bought and unit prices and manually updating the unit price of the managed fund in 'Portfolio' tab under current price. If you have more questions please let me know. Happy to help. Cheers PF
Hey, you can track your forex trading by entering the currency pair in the ticker column. Feel free to check out the Day Trading Tracker as it might be more useful if you do trading.
@@PlanetFinanceOfficial No, Im not a trader. I you base currency is in EUR and I need to trade it to USD so I can buy stocks. Now when I mark the currency conversions it messes up the whole spreadsheet when I set the asset class as a cash. What asset class should I use?
@@Powerman3000 Add a new asset class in the Data Validation feature and call it FOREX. This should stop the other formulas for breaking as the tracker calculates cash balances with the Asset Class called "Cash". Let me know how you go!
Hi Bhavnesh, there's no sample file for this tracker. If you don't want to build it yourself please go to the Patreon link to download the ready-to-use version. Cheers!
Hey! At the moment the tracker can't find the price of the option automatically, you could add them but would need to change the prices manually. This is a capability I'm aiming to add in an upcoming 2nd Gen tracker :)
@@PlanetFinanceOfficial Cool. I'm looking for something to keep track of all my investments, Stocks, Forex, ETFs, Cryptos, and most importantly Options. I am gonna finishing watching the video. The only other thing I'm worried about is when buying more shares, at different price, and reinvesting to buy more shares. Will it up update that as well? I can handle adding Option prices as long as it update everything else and my capital.
@@Troyson04 There's a script in the tracker that updates your # of shares and average purchase price based on what you buy and reinvest. Make sure to check out the formula sheet document. There's a screenshot and instructions on how to enter transactions to make sure all your data is 100% accurate!
Amazing! Exactly what I have been looking for and more! Love the touches with all the additional charts and trackers. Thank you for sharing all the formulas. Probably going to need to go through the video a few more times to build my version, and that will be a nice weekend project.
Thank you so much for your comment. I'm very happy it has helped you! Stay tuned, there's an improved version coming soon :)
I've been searching for a nuggets like this for a while this is fantastic. Definitely deserve more likes! Thank you for the effort in uploading this amazing!
Thanks very much! More coming, stay tuned!
Perfect! Just what I have been looking for. Very easy to follow and All the information one would need. Thank you for this! Two big thumbs up!
Nice job! Could you add a graphic so when you hit your stock/crypto you can see its performance over time. Ex If I want to see if apple was maxed and I should have sold or bottomed and I should have bought relative to my ownership of the stock. Allow it to reflect also return in a separate graphic representation. Ex. Bought BTC In march, June and a month ago, what is the performance. 2 different tweeks to see performance over time. Great work! Keep it up!
Hey Frank, this is a great idea. Thanks so much for your suggestion. I will apply it in the 2nd Gen Tracker I'm currently developing. Stay tuned! Cheers
Thank you for your videos could you please make a crypto Portfolio Growth Chart (hourly - daily - monthly and yearly ) with timestamps, i mean record all the price changes (history) that happen to the portfolio balance overtime thanks.
Hi Batarchi, thanks for your comment. I am planning to implement this in the future, stay tuned!
A Nice Video. Thanks for this tracker. You deserve more subscribers.
This is a great video. In the Currency Converter portion of the tutorial, your screen shows an error message after you enter in the formula in the Avg. Price error and mentions that it has a problem with GBP, but then you don't really show how this was resolved, and it just jumps to you pasting the formula to the rest of the cells. Is there something in the formula that needs to be fixed in order for this to work properly? Thank you.
Hi Ryan, thanks so much for your comment. You are right, there is an error and it happens because in certain occasions Google finance cannot find the currency for stocks in GBX. If you continue watching, there is a column in Transactions that we haven't completed yet (column K). This formula in the Currency Converter will not show errors once column K in 'Transactions' is completed. Basically acts as a last option if the currency was not found.
Thanks for letting me know about this, next time I'll explain in the video why the error temporarily shows. Sometimes I fast forward a few formulas because I might skip explaining every single formula but this is one I should've explained. Thanks again and please let me know if you need help with anything else!
Cheers,
PF
Great work - but a quick question. If the same holding is found in two portfolios, it seems to mistakenly put all the holding in the first portfolio it was found in. I think this is because of the summaries in the currency conversion tab. Before I take a shot at fixing, just want to ensure this is a mistake.
Thanks!
Hey! Thanks for your comment. I have fixed this in the newest version of the portfolio tracker. Feel free to check it out and let me know if you have any further questions! Cheers
Nice spreadsheet!
Thanks so much! 2nd Gen coming up soon so stay tuned!
Thanks for the awesome video on portfolio tracker in Google Sheets.
Is there anyway to list down the exited positions separately ?
Hi! Thanks so much for your comment. In this version there is no way to list them separately as they are automatically removed by the script if sold all. Feel free to check out the 2nd Gen Tracker where there is more detail on the "Sold All" positions.
@@PlanetFinanceOfficial Thank you for the details
Life saver!
I have a small problem which I have emailed you about.
When I enter the transactions for £IUSA ETF my portfolio goes to NUM errors.
Is there anyway we can fix this? I have no idea what I am doing and was lucky to find your tracker.
Hi Kyle, thanks for your comment!
Make sure to enter the ticker for this ETF including the exchange symbol. I believe it should be LON:IUSA. Alternatively, you could build/download the Portfolio Tracker 2G which includes a feature for price fetching from Yahoo Finance if Google Finance doesn't find the ticker. Cheers!
@@PlanetFinanceOfficial Hi mate, it’s found the ticker but the current price on currency converter is showing in GBX whilst the rest is GBP. This is showing my gain as 9000% as my average cost is £33.30.
Is there a formula I need to change?
Many thanks
@@StockMarket2197 Hi Kyle, what should be the correct avg purchase price?
Hi bro, I've been using it and everything good, but i actually have more than 157 transaction and got this error "Exceeded maximum execution time (line 0).". Do you know how to fix it? I think it's because custom functions have a limit of 30 seconds but don't know the solution. I appreciate your work, greetings from Argentina
Hey! Thanks for your comment! I believe you get this error when the custom formula is not set properly. Did you see this error with the first transactions? It might be an issue with the order and syntax of your transactions. E.g if you buy something without an initial cash deposit transaction or if you enter a buy transaction that's missing a date or a price. Let me know how you go and if you need any further help I'm always happy to help. Cheers!
Excellent video and tracker... one question... how do i add Options to the tracker??
Thank you so much:) at the moment it doesn't have the capabilities for options but I am developing one that will include them!
Looks amazing! Just wondering if it is possible to automate recurring buys for example if I wanted to dollar cost average each month into a stock. Could it be possible to like schedule a transaction to be repeated so I don't have to go in each month and copy and paste it?
Hi Eddie, thanks so much for your comment! Unfortunately I haven't added the capability to automate recurring buys in the transactions as every entry is manual and if you were to DCA it needs to find the price you bought it for. This tracker is not linked to any broker website to the only way to record the price at which you bought it for is manually in the transactions tab. It's a great idea though, I'll look into it to see if there is a way to make this more effective and I'll apply it in the upcoming 2nd Gen tracker. Cheers!
Hi, this is great, I'm just starting using it, but it seems that the brokerage fees are not taken into account on a 'Buy' transaction. So they are not deducted from the portfolio return. Is this by design ? Can you explain why ?
Thanks 😀
Thanks so much for your comment! Yes, the standard formula doesn't include it for buy transactions. In the ready-to-use version I've written down a note with alternative formulas that take it into consideration:
Standard Total column function only subtracting brokerage fees from Sell orders:
=IFERROR((IF(ISBLANK(D3),"",IF(F3="Sell",G3*H3-I3,ABS(G3*H3)))),"-")
If you want to deduct brokerage fees from Buy orders enter the below formula in cell J3 and then drag down:
=IFERROR((IF(ISBLANK(D3),"",IF(OR(F3="Sell",F3="Buy"),G3*H3-I3,ABS(G3*H3)))),"-")
If you want to add the brokerage fee to Buy orders enter the below formula in cell J3 and then drag down:
=IF(ISBLANK(A3),"",IFERROR(IF(F3="Sell",G3*H3-I3,IF(F3="Buy",G3*H3+I3,ABS(G3*H3))),""))
If you want to add brokerage fee for Buy Orders and deduct brokerage fee from Dividends (could be used for tax), enter the below formula in cell J3 and then drag down:
=IF(ISBLANK(A3),"",IFERROR(IF(OR(F3="Sell",F3="Dividend"),G3*H3-I3,IF(F3="Buy",G3*H3+I3,ABS(G3*H3))),""))
These are a few of the options available. Feel free to try them!
I'd recommend you take a look at the 2nd Gen tracker I released (linked in the description) as it has better capabilities. Soon I'll release a 3rd Gen that improves many features. Stay tuned!
Cheers,
PF
Got it going Mate thanks heaps
Great to hear! Feel free to ask my any further questions if something comes up.
great job of putting this together...but few formula's are giving errors! where can I email u
planetfinanceofficial@gmail.com
Great Video! Now, I have transactions where I Convert one cryptocurrency for Another cryptocurrency. Any chance that you have any tips to implement this? I'm trying to find a way to accurately track my cost basis mainly for tax season
Hey Jason! Thanks for your comment. This tracker works only when you buy and sell cryptos in one platform. It doesn't support converting from one crypto to another or sending to another platform. I'm currently working on a second generation tracker similar to this one that will include such capabilities. Stay tuned!
@@PlanetFinanceOfficial Awesome! Thank you very much for the quick response. Keep up the great content, this channel is going to the moon.. I’m looking forward to seeing your next tracker creation!
Merci 🇨🇵
Hi
Awesome sheet! I have a lot of Norwegian stocks. Can this sheets manage that? I know Google Finance doesn't support Norwegian stock :)
Hi Mattias, thanks so much for your comment. If Google Finance doesn't support the Norwegian stocks what we can do is help you create a custom formula that will extract the prices from another website like Yahoo Finance. Feel free to email me the stock list and I can help you create a formula. Cheers, PF.
Very good video. Do you have a formula for Unrealized and realized gains for individual stocks, from all the sell and buy trades? Thank you.
Hi Ssinseeme, thanks for your comment! Try this formula: =IF(ISBLANK(AD3),"",SUMIFS(Transactions!$M$3:$M,Transactions!$H$3:$H,"Sell",Transactions!$D$3:$D,AD3)+SUMIFS(Transactions!$M$3:$M,Transactions!$H$3:$H,"Dividend",Transactions!$D$3:$D,AD3))
AD3 is the Stock ticker. Transactions M is the Total, Transactions H is the Action, Transactions D is the Ticker. You will have to edit the formula to fit your tracker as this is the formula for the new upcoming version.
This formula has been applied in the Portfolio Tracker 2nd Gen that will be released in the upcoming week!
@@PlanetFinanceOfficial Thanks for the reply, unfortunately, it didn't work, for example, I bought 5 shares of ZM@200 and sold 1 share @250 the gain should have been 50$ but the formula returned 250$. I will wait for your update video.
@@ssinseeme My bad, the formula should be:
=IF(ISBLANK(AD3),"",SUMIFS(Transactions!$M$3:$M,Transactions!$H$3:$H,"Sell",Transactions!$D$3:$D,AD3)+SUMIFS(Transactions!$M$3:$M,Transactions!$H$3:$H,"Dividend",Transactions!$D$3:$D,AD3)-SUMIFS(Transactions!$M$3:$M,Transactions!$H$3:$H,"Buy",Transactions!$D$3:$D,AD3)-SUMIFS(Transactions!$M$3:$M,Transactions!$H$3:$H,"DRIP",Transactions!$D$3:$D,AD3)+SUMIF($AD$3:$AD,AD3,$AK$3:$AK))
We add up up the cost at the end, AK is the cost column in this formula.
Hi Thank you so much for this vide, I just became a patreon but couldnt find this video specific or the video on cyberpunk style, could you please help me on this? thanks
Hi ivan, thanks so much for your comment! There is no specific video per theme, one applies to all themes as the trackers are exactly the same just with different colour schemes. Thanks so much for joining my Patreon and supporting my work. I've sent you a private message with the post that takes you to the Cyber Punk Theme.
Hi, do you have any tracker for forex traders?
Hey! Unfortunately I don't have this yet. You could take a look at the day trading tracker but happy to add forex into the pipeline of upcoming trackers.
nice
I’m having issues in filling cash and realised gains in portfolio balances. I changed the names of respective columns in the transactions tab. Help me.
Hey, you need to make sure to follow the correct sequence: cash deposit, buy asset, dividends and then sell. The formulas in portfolio balances reconcile the numbers by making sure the actions uses have a specific text. Please let me know if you need further assistance!
@@PlanetFinanceOfficial got it.
Can u help me with dividend income formula
=SUMIFS(TransactionsTotalConvert,TransactionsDate,">="&$C$21,TransactionsDate,"="&$C$21,TransactionsDate,"
@@palamakulamukeshreddy Hey! Where in the tracker do you want to apply this formula?
If I enter any more than like 20 transactions in the transactions tab, the portfolio tab formula comes up with an error that says "exceeded maximum execution time." Do you know what the problem could be? Like, is it the script that is unable to sort through all the data in the transactions tab? If so, any suggestions on a possible fix?
Hey Brad, make sure to enter transactions with the correct sequence (cash deposit, then buy security). Check out the extra info in the formula sheet that I've shared in the description. If you still encounter issues feel free to email me and we can solve your issue. planetfinanceofficial@gmail.com
Hi Bro, I really need help with the REF! error all the columns are at the right place and permission pop up didn't showed up How to fix this?
Hey! You need to click Save and then Run the script. The authorization pop up should appear after that. Let me know how you go! Cheers
Already did that, nothing is happening, could I share you the the sheet, so you can look into it, it would be a great help.
@@akshatverma6160 Yea no problem! Please share it to me here: planetfinanceofficial@gmail.com
Thanks Bro. Found the mistake I named the Transaction sheet wrong. Got it corrected. Works great now.... Thanks for the help Bro. You are doing such a great work. This is exactly what I and many need. Got great skills 👍 Keep it up Bro. Thank You
Hey, thank you so much for the videos am planning to join your patreaon.
wanted to ask if its possible to get get Etoro informations
Like the copy trader information and what stocks they bought and sold and how much money you have invested as well as P/L from them. it would be a great addition for the 2nd Gen Tracker if you feel this is a good point. :)
Hey! Thanks so much!
You can enter trade information from any platform as long as you follow the correct sequence. E.g deposit cash transaction, then buy stock, then sell, etc.
@@PlanetFinanceOfficial thank you so much for the reply.
Yes, but the problem with eToro is that i don’t exactly see the transactions made by the copy traders.. if I go in and check each trader then it’s going be to be a Herculean task…
If we can download the spreadsheet from eToro, is it possible to pull the data?
@@akkintouch Can't you download a spreadsheet from eToro with your transactions and then try to fit the data as per the columns available in the Transactions tab? Unfortunately this tracker requires manual input of transactions as it's not pulling data from any platform but Google Sheets.
@@PlanetFinanceOfficial yup am trying that.. but Etoro’s downloaded spreadsheet is very confusing and not very user friendly for beginners..
I will try to decide it and put it in the google sheets.
What should I do with different exchange?
Like gbx or chf or eur….
I understand we can change the currency in this portfolio sheet but how to make sure google finance link the correct ticker of the different exchange..
Sorry about a lot of questions…
@@akkintouch just make sure to enter the Transactions with the currency that you are trading in. There's a column where you specify that currency so the formulas in the tracker will capture that and convert to an overall currency for precise total numbers. Make sure to check out the Formula Sheet link in the description where I show a screenshot with an example on how to enter transactions.
im getting #NAME? or #REF? error after entering =Positions/Investments(Transactions!D3:D,Transactions!F3:F,Transactions!G3:G,Transactions!H3:H) Any idea how to fix this?
Hey, you need to make sure to enter the script exactly and also giving it permissions with your Google Account!
Also make sure you've entered the correct figures in the corresponding columns in Transactions
@@PlanetFinanceOfficial thanks for the reply, how exactlly do you grant permission to Gaccount? Ive been searching it up but there doesnt seem to be an option where I could enable it in "My accounts"
@@mrpax2303 When you click Run after pasting the script, Google shows a pop-up warning you about an unofficial Google script. You just need to click continue with the script and it should be enabled with your account
im having troubles with the currency converter. im not very good at these type of things. any chance i can share it with you and u can take a look?
Sure :) feel free to email me: planetfinanceofficial@gmail.com
@@PlanetFinanceOfficial thank you so much man. i just sent it
Hi, thanks for this. I bought Glen on LON exchange where is listed in GBX. However, your spreadsheet seems to work only with GBP currency. This makes calculation difficult. For example, today LON:GLEN values GBX 347.50 and your Currency Converter sheet lists 405.92 current price (EUR). I understand therefore that the spreadsheet thinks that 347.5 is GBP, instead of GBX. The only solution I see here is that I multiple the number of share for the conversion factor between GBX and GBP. What do you think? Thanks!
Hi Danilo! Thanks for your comment. If you enter GBP in 'Transactions' > Currency Column for LON:GLEN instead of GBX, the conversions should work.
Let me know how you go, if you still find an error I'll help you customize a formula.
@@PlanetFinanceOfficial Hi - this is a really great tracker, thank you. But I'm also having the problem above. I've listed Aviva (AV ticker) as GBP but it thinks I've 100x my investment as it's showing as £403.1 instead of £4.03 in the currency converter and portfolio tabs. Is there a way around it?
Amazing work! It really helps me build my own. But I'm currently having some problems when I pull the price for some crypto currency. The price cells are getting populated but if try to refer that cells for a simple A*B function I'm getting the error "Function MULTIPLY parameter 2 expects number values. But '$0.7628' is a text and cannot be coerced to a number.", "$0.7628" being the price I get with the importhtml function. Any suggestions on how I can format that cell to overcome this issue ?
Hey! Have you tried changing it to currency format? Let me know how you go and feel free to email me your spreadsheet if you need extra help. Cheers!
@@PlanetFinanceOfficial Hi there! First of all, many thanks for this video - very useful! I have the same issue as Ionut Florin but unfortunately, changing the cell formats to "Currency" doesn't change anything... is there another walkaround? Thank you!
@@marcoweiss3618 Hey! Thanks so much for your comment. I'm really glad you have found this useful. It seems like the function might be pulling the prices with the $ sign as well. To fix this you can add the VALUE function in front of the formula for prices and it will convert it to numbers you can manipulate. I'll double check the function, maybe coinmarketcap changed their API and I'll need to edit the formula and notify those who downloaded it. Cheers!
I do not want purchase but i want just sample file
Hey! Unfortunately there is no sample file
why mine shows error unknown function, on portfolio sheet, ticker column 😭
Hey Sherwin, make sure to enter the script in the script editor and run it before entering the formula. Let me know how you go!
@@PlanetFinanceOfficial its done, i think the problem was my google chrome, when i paste the script with edge it workss so well!! thankyouu so much! i think your channel deserve more
@@sherwinsatyahadi9790 Great glad it worked. Thanks so much I really appreciate it!
Can you make indian market same file?
Yes, you can enter indian stocks in this tracker. Make sure to enter the exchange symbol, followed by a : and then the ticker symbol.
im getting dates as average purchase prices after i enter in formula "=Investments(Transactions!D3:D,Transactions!F3:F,Transactions!G3:G,Transactions!H3:H)". any tips on how to fix this
Have you selected the avg. purchase price column and changed the format to $?
@@PlanetFinanceOfficial i actually love you.
Im getting #REF! error after entering =Investments(Transactions!D3:D,Transactions!F3:F,Transactions!G3:G,Transactions!H3:H) pls help
Hi Avishek, please make sure that you copied the script from the resource shared. Also, you need to make sure the fields in 'Transactions' match the ones I entered. Specifically columns D, F, G and H. If you still get an error feel free to email me your sheet and I can help :)
@@PlanetFinanceOfficial do you mean we have to copy the same stocks that you put in? Or can we enter our own investments in?
@@mrpax2303 I meant make sure to enter the correct figures in the transactions columns. e.g. stock ticker should go in the column where ticker is meant to go, just like in the video. Let me know how you go!
I followed the instruction as provided in the video but I keep getting an error. Can you help please. Error below;
Error
TypeError: Cannot read property 'map' of undefined (line 38).
and below is the script I had copied and pasted in Script editor; (Yellow highlighted is line 38)
if(action.toUpperCase() == "SPLIT"){
let activeTrades = portfolio.get(ticker);
let ratio = quantity[i].toString().split(":");
activeTrades = activeTrades.map()((trade) =>{
trade.shares = trade.shares * ratio[0];
trade.price = trade.price / ratio[0];
trade.shares = trade.shares / ratio[1];
trade.price = trade.price * ratio[1];
});
}
Please help and thank you
Hi Sunny, I've replied to you via email. One line in your script is incorrect:
activeTrades = activeTrades.map()((trade) =>{
and should be:
activeTrades = activeTrades.map((trade) =>{
Make sure you copy the code from the resource provided in the description :)
Cheers!
@@PlanetFinanceOfficial Yes, I realized I put an extra open parenthesis. I was trying to make it work and didn't realize. After copy pasting the FIFO Script again still getting the error and also sent you an email with updated script as per you suggested but still getting an error. Thanks again.
How should I impute managed funds to not break it?
Hey Enzo. If the managed fund's ticker is available in Google Finance you could add it like we add stocks or ETFs. Of it's not, you could add the transactions you've done by using the units bought and unit prices and manually updating the unit price of the managed fund in 'Portfolio' tab under current price.
If you have more questions please let me know. Happy to help.
Cheers
PF
How does forex trading work in here?
Hey, you can track your forex trading by entering the currency pair in the ticker column. Feel free to check out the Day Trading Tracker as it might be more useful if you do trading.
@@PlanetFinanceOfficial No, Im not a trader. I you base currency is in EUR and I need to trade it to USD so I can buy stocks. Now when I mark the currency conversions it messes up the whole spreadsheet when I set the asset class as a cash. What asset class should I use?
@@Powerman3000 Add a new asset class in the Data Validation feature and call it FOREX. This should stop the other formulas for breaking as the tracker calculates cash balances with the Asset Class called "Cash". Let me know how you go!
How to download sample file?
Hi Bhavnesh, there's no sample file for this tracker. If you don't want to build it yourself please go to the Patreon link to download the ready-to-use version. Cheers!
@@PlanetFinanceOfficial send link so i have download?
@@bhavneshparikh8283 www.patreon.com/planetfinance
Can this work if I add Options too?
Hey! At the moment the tracker can't find the price of the option automatically, you could add them but would need to change the prices manually. This is a capability I'm aiming to add in an upcoming 2nd Gen tracker :)
@@PlanetFinanceOfficial Cool. I'm looking for something to keep track of all my investments, Stocks, Forex, ETFs, Cryptos, and most importantly Options. I am gonna finishing watching the video. The only other thing I'm worried about is when buying more shares, at different price, and reinvesting to buy more shares. Will it up update that as well? I can handle adding Option prices as long as it update everything else and my capital.
@@Troyson04 There's a script in the tracker that updates your # of shares and average purchase price based on what you buy and reinvest. Make sure to check out the formula sheet document. There's a screenshot and instructions on how to enter transactions to make sure all your data is 100% accurate!
@@PlanetFinanceOfficial awesome thank you.