Editing Transactions Step 2: After plugging in all my historical transactions, all I can say is that this is my go to portfolio tracker. Hi, I actually managed to customize the tool myself. The Yahoo Finance version seems to work the best. August 25, at 6:
Lose the spreadsheet
You create your own. If it seems that you did not make a change to the cell, it will not auto-recompute the cell. If you have set the trigger correctly as how I explained in the Refresh Yahoo Prices Properly using Google Script and Trigger section, then it will work. You can read the article here. Rights Issues are basically company asking for more money from you to buy more shares.
They make it attractive for you to buy at a lower price. You currently have shares. Bonus shares are company paying you shares instead of cash. This means if you have Challenger shares you get crazy good deal! They are basically like bonus shares.
Instead of the company taking that action, the share holder you choose to get paid by shares. Determine how much shares you will get from Reinvestment statements and input a Buy transaction with a quantity of shares at a transacted price of Zero with Zero commission. To make it simple for you guys I will try to add the different stock quote examples to help you get started.
You can also see how many different exchanges are supported. Is viewed in one package. However, if you are not so familiar with spreadsheet or would like some support on. I will make my services available. Must say that I thoroughly enjoyed this experience while creating this spreadsheet. I hope I am able to help someone who was having the same problem as myself. Take a look at the spreadsheet , make a copy and modify it to suit your needs.
For those interested in tracking my most current holdings, you can review my portfolio over here. Since I am updating this together with my Quicken yes still using that. Active Stock Investor for 14 years. Reached Financial Independence at Thank you for this. I have been looking for something like this for a long time.
Most of the excel sheets seem to focus on trading and not so much on dividend and long term holdings. Let me try yours and and feedback to you. If you are willing to accept some modifications request, that would be great. Most likely not since I do not have competency in that area. What is required to adapt to options trading. I like to give you a feedback on the cash register — please note I not an expert.
I may be wrong on my comments. Thanks for the feedback but i think it will not work. Hope this helps explaining. Thank you for such a brilliant worksheet! Thanks , I created a simple one page version previously but your is much more comprehensive. Hi Wee Siong, i would love to take a look at your sheet as well. Just use what works for you. My formulas are accessible to all. Would love to learn what are the things you look out for. Hi, thank you for providing such an excellent worksheet.
I always have problem tracking my gain or loss on my portfolio over time. Sometime I will replace and sell stocks to increase my chance of better gain. But too often I forgot what my portfolio value was before the change!
I was thinking if we can have a time line over years vs portfolio value to see growth or dips. But I think it is very hard since the spreadsheet will only capture the market when you open the file.
So you can do snapshot but cannot have continuous trend. What do you think? Where did you get the yahoo link to download the quotes as csv? The one I can find on yahoo site leads me to http: Ah, I think I found it. I can either set 4 decimal places or currency, but not both. I would like to request as others did, to be able to track my stocks to know based on the transactions buy , sell , rights, dividends etc..
It would also be great if I could set a target for my portfolio like 1 million in 10 years etc…. Hope I am not asking too much. Love the spreadsheet, spent quite a bit of time updating it with all my details as far I know.
For the portfolio summary, It takes the dividends from — and combines them together Total Dividend Portfolio Dividends Collected and compares it against the Total Dividend Portfolio Dividends Collected which seems to comparing against a single year. How can I view the dividend I got per year and the dividend yield across the the dividend stock portfolio.
Can input in dividend also. Hi, thanks for sharing this template. How do I convert to the same google spreadsheet as urs? Only managed to achieve half of what you have done here. I would say currently my spread sheet works only in Google Spreadsheet.
Those formulas differs once you download it as an excel. However i will try to come up with an excel version to alleviate the excel problem in the mean time. Hahaha I was trying to use it in Excel too. Tried to modify non-macro sheets into excel, but alas! When I sell it at This would give a more accurate value of portfolio summary in SGD.
I did thought about this. I have been searching for a good portfolio management tool for SGX stocks for ages. I was using Bloomberg portfolio until now. But this one is great. I have been thinking about formulas for calculation of returns. I would like to ask your opinion on that. Hi Fencer, i tend to calculate the return of the stock as a consolidation of how much money i made and loss through dividends and selling at the Stocks Summary.
As for why dividends stay the same, i take it that dividends do not bring down my original investment value. The example of Food Junction in your transaction sheet, why does the dividends not bring down the cumulative cost? May i check with you how to modify the Yahoo Data Ref? I looked at your formula, but seriously, i dont understand ….. Thanks for sharing your spreadsheet. With that said, I want to understand from you if you plan some updates.
And if so, how can one update the tracker sheet with those updates. This is a very good effort, however my idea was to create a small MS Access database to maintain and track stocks. This allows an easy data entry whilst enabling different kinds of reporting. I have also created an Excel template however I find it a little cumbersome to manage once the size becomes large.
The problem with software use is data portability. We hope that everyone uses the most portable format and right now its excel haha! But i really like about my stock portfolio tracker is that it is ubiquitous, because it is a web spreadsheet i can view and edit wherever i am.
Hi PN, currently i do not. I usually create a seperate worksheet for it. What you can do is to duplicate my worksheet and then add cash as an asset class. I see that it only captures data from the SGD stock summary — is there a way to integrate the USD stock summary data as well so we can see fund allocation across these 2 markets? Hi Drizzt, this is a great piece of work! I love it and am using it. Great work and thanks for sharing!
For another simple guide on how you can use this spreadsheet you can take a look at the guide here. Great work and thank you for sharing this clear and effective spreadsheet! Had started using your spreadsheet and make some changes to incoporate other asset classes. Thanks again for this great tool! Nothing make me happier than my spreadsheet being of convenience for other folks. Perhaps u can reveal where you are from and which markets you use it for?
Your spreadsheet is really awesome and I am able to learn a great deal of google functions usage from your spreadsheet. I am thinking if you can add another 2 features to this, I can consider this as the ultimate portfolio management spreadsheet. Adding an additional column for Owner so that multiple portfolios can be maintained in a single sheet 2. Start with a cash balance, adding and subtracting from the same based on the transactions.
I hope you will add these features sometime in the future when you have some free time. Hi Tedla, I am glad you enjoyed it. I think the first suggestion i may not add in. I am seriously thinking about the second point. Thanks for the prompt response.
I was able to tweak the spreadsheet to add both the features. May not be the best implementation, but works for me. Tweaked column A on stock summary and made it a Owner column and starting each stock name with first letter of the owner name. This is very important for me as I manage 4 different portfolios.
Other thing I am dying to add is annualized gains. I think this is little complicated than the above 2 features. First of all, thank you for this awesome stock portfolio tracker template. It have just replaced my default template. I agree with you on owner and cash in hand. Please let me know if you are able to achieve annualized yield any time soon.
You have to be a little bit more explicit. I found that you have ver 1. But how can I upgrade my existing one with your latest version since I already have all my data in the old version, and I could not copy your formula over to my old version since the spreadsheet is locked?
Amazing work building this spreadsheet. I have all my dividends paid in cash not shares of the same stock.
I have not been able to input the data properly to predict cash flow and track return. Any suggestions would be appreciated. Anyway, for this, you can try using the Copy To method from the new sheet to your existing workbook and it should work as I have tested. I really liked your Google spreadsheet and was planning to do something like this with my spreadsheet — put it up for others to use.
Most portfolio trackers Y! F are geared towards trading and marketable securities. Secondly investing seems to be so personalized, that a spreadsheet gives people something to start with.
So I really like what you have done here. I had a couple of follow-up questions which I wanted to ask over email. Would appreciate it if you reply to my email address. I began messing and creating Stock Portfolio Tracker 2 months ago to see if I can create portfolio tracker for keeping track and monitoring stocks. I enter the cost of the stock in pounds and pence uk which correctly shows how much I paid for the stock.
The stocks get quoted in pence from Google and Yahoo. However because the stock is quoted in pence it shows I have made a massive increase when none has occured. Hi Pretty Neat tool from what I read. I plan to download soon.
How does it if it does track options trades? When i exercise it i will treat it as a purchase of stock at the average price. So in the meantime, we have to track unexercised warrants separately? So I had input in the stock myself. Thanks for sharing the spreadsheet. I am trying to create an excel version of this. Your spreadsheet is one of the most comprehensive on the web. I was using your format until yesterday, the tabs for monthly and yearly does not provide any data.
It shows up as NUM!. Comparing with your spreadsheet, I found out yours was also NUM!. Hi Drizzt Is ther an issue with thre spreadsheet or is it from Googles side? When I open the spreadsheet and go to the tables mth and yrly tere is nothing populated anymore.
I also tested this by downloading your original sheet and it is the same. Is there something to be done or wait for google to fix it? Would you consider making your SGX Singapore Stocks Factsheet available for us to track the historical returns of the other stocks we are interested in?
Thank you so much for sharing the spreadsheet! Guess using your suggestion…. Hi Drizzt, like you spreadsheet, awesome, already put into good use.
I also notice that your transaction fee is pretty low, do you mind sharing how you get such low transaction fee? Thanks Drizzt, after google about it decide not to go for it. Keep up the good work here, will visit often. Hi Drizzt Thank you for sharing the spreadsheet, it is immensely useful.
How do you track yours? Take snapshots automatically every end of year and record down the value. I will try to work on it and hope i can hit something. Yeah, think it would involve scripting. I am trying to see if there are examples online. Let you know if I manage to find anything. The way that I will be trying to implement it would be to create another spreadsheet listing down the years with the corresponding value.
Then every time the spreadsheet is opened, a script will find the column of the current year to update the value of stock holdings. The link to your spreadsheet does not work for me. It leads to something other than the spreadsheet shown.
I am very disappointed. This is an amazing spreadsheet! When I receive a dividend it goes into my cash account. How do I record this on the spreadsheet? As far as I can tell I can record a dividend but it has to be reinvested in the stock. This is regarding your dividend stock tracker. Is there an option for archiving and retaining the realised gains?
For myself, i dont actually need to account for them. You may even want to call them another name. Show percentage of each stock as a whole of the portfolio 2. Like a previous comment, each of my 10 accounts has a cash position. I need a way to track this cash position when interest or dividends are paid. Does this calculate IRR or annual return. It would be really great to show what the portfolio returned in the past 3 months, 12 months, 5 years, 10 years etc….
I refrain from doing that because i am afraid the sheets will get very complicated and turn off most of the folks.
For readers who have done that perhaps you can link me to show how you guys are doing. First let me thank you for your spreadsheet: Also, can you add a macro which allows you to schedule query refresh based on 1,2,5,10 and 15 mins period.
This would mean that at certain point my spreadsheet currently will hit a snag. How we can get around this is that the formula in cell A2: Yes, I guess a seamless update from Google or Yahoo or both, as in your current spreadsheet would be awesome! While using the Internet instead of the spreadsheet, I find Reuters very detailed finance tab of any stock.
Hi, how would you suggest I do a Dividend Reinvestment meaning, I get shares automatically, never seeing the cash? Hey Chris, I took a look at your profile. It seems you are a foreign talent in Singapore haha. Have added your blog to my Google Reader.
Anyway on your question, for reinvestment, my approach is NOT to have a Div entry. So what i would put a Buy 11 shares at zero cost. May I know where can I enter the correct amount of units in the stock summary tab? It is blue in colour and I do not want to mess up the formula.
The STock Summary aggregates the transactions in the transactions tab. Edit Row 2 by using the drop down to select your stock name. Hi Drizzt, I have another question. Do I need to edit the formula in A2? Hello, thanks for providing this template. I try to set up but encounter few difficulty.
I am not very Excel savvy. Change the name to Intel. I hope that help. First of all, thanks for creating this great template. May I know what type of category should I put? For US transacted shares, Coca-Cola has splitted their shares for ratio 1: Should I put in the original shares which I bought earlier or the current splitted shares in the transacted shares? Hi Terence, there is a split transaction.
Thanks Drizzt, how about K-Green gave me 1 lot as dividend instead of cash. Hi Terence for K-green, you mean keppel give you one lot or kgreen give u one lot.
Can direct me where to check US stock, e. Thank you for your hard work and devotion. Namely, the stock symbols and then the transactions. My issue is that I have many symbols around and many transactions around and counting. Before I enter all the stock symbols, I extend your rows to work with the number of symbols. I then go to input the transactions by first extending your rows as you describe above extending the row limit.
So far, so good. In your stock summary tab one of the rows is for the user to enter the last DPU. As the data I have consolidated on my own excel sheet does not have the last DPU data, I attempt to solve the problem by adding another item to your yahoo data ref sheet.
I add a column for DPU and download the data from Yahoo along with the other items you are downloading. I also do another addition to your downloaded yahoo data on the yahoo data ref sheet to download the yield. I use this as a sanity check as manual monitoring of the dividend numbers needs to be done to account for unclear data from yahoo…. Do you have any recommendations for a work around on this issue? Looking forward to your brilliance. This way i can find out new ways of doing it. Do note that yahoo data ref can only get at most symbols if i remember correctly i gotta test them out.
I am trying to use your stock portfolio tracker. For some reason, after completing my data entry, either by mistake, I lost the data for the 52 wk low and 52 wk high columns. How do I reinstate them?
It works really nice. You have to add a column with the cash-flow from the transactions negative and positve and put at the bottom of the list the current value of the portfolio. Then I added a new sheet in which I linked the cashflow, the dates, the current value and the current date in order.
Finnaly I linked in Portfolio Summary the number. Here is a copy of my wifes portfolio with those changes, https: Hi, I am trying to convert your online spreadsheet to a normal spreadsheet in Excel. However, I am stuck on 2 things in the transactions tab. I want to convert both of these columns into excel formulae Previous Units — cell H2: I do not know what the equivalent to googlecalc array formula or filter function is in excel or better.
I could not make the formula work in excel when i put it in cell H2 of the saved excel file to return the equivalent to the googlecalc. Wonderful work you have done and shared with us. Few possible improvements that i am thinking about… Let me know what are your thoughts:. That will help getting an idea like how diversified the portfolio is… For example Technology, Consumer Staples, Industrials, Basic Materials etc.
Region and Asset Class might just be a drop down in the portfolio tracking sheet…. I added a comment on Saturday here and that did show up also with some questions… I am wondering why that is not showing up any more? Does your spreadsheet allow me to do that? Is there a way to add stock options to the portfolio?
Is there a way for this to become a real-time updating portfolio? Can it be linked to real-time quote providers like TD Ameritrade or something? How would you want to add stock options?
How do I do the MS Excel thing? Just simple copy and paste the cells? I have no idea how to add stock options or how to name them so one can get quotes from Yahoo! Did you think of making an auto updating MS Excel sheet with these capabilities if it can be done? I use QT simply for the portfolio part of it and I like the alerts as well, everything else is available on TOS, though I hate their interface and the crowded ill-colored screens they have there!
Please let me know whenever you come up with an MS sheet that does the above. As silly question; do you know of anything that does what we want in real-time connectable to TD Ameritrade or any other real-time quotation provider? Hi, wonderful job in the template, very elegant! I had a project to do in university, that included short sales as well. I updated the whole spread sheet expect monthly and yearly to allow short sales. If you want to update your sheet, here is the link of the one i updated:.
If you need any explanations, im here to help. Also if you have any idea how I could update monthly and yearly sheets to include short sales, it would help a lot. I have requested permission to view the spreadsheet. Hi, Came across your spreadsheet and love it!! If you have bought multiple positions in the same stock at different times, then the Stock Summary tab only shows 1 of them, whereas it should be showing the total shares bought and weighted average price.
Stock summary tab only shows the 5 shares. You specify the name of Goldman Sachs and the symbol as well as the dividend yield at the Stock Summary. I actually did enter the transactions in the transactions tab. But what I was saying was that only one of the positions then showed up on the stock summary page.
No total positin; no avreage price. Could you take a look at the formula? I did make 1 minor change to your spreadsheet- i changed all the formulas so that they refer to the stocks by their googlw ticker instead of by name. I just think thats easier and avoids typo situations, particularly on the transactions page. Second, how di I deal with a stock spin off? Sorry if this should be basic knowledge. Keep up the great work. Your solution is easily the best I have found including paid software.
Hi, great spreadsheet but I need to determine values using AUD as base. I imagine it will be similar to how you reference stock prices, can you offer any advice on how to do this? Are you able to share how you build up this excel spreadsheet? Hi Stella, i am confused. This is a google spreadsheet which is housed online. You can track dividend stocks similar to how i do it by following the sample data rows outlined here. May i know how do i add row onto transaction, monthly and yearly spreadsheet?
Can i just copy and paste the row on top? HI Wei Liam, you can copy the last row and drag it down so that the formulas will propagate downwards. Hi, Came across your stock sheet while looking again unsuccessfully for a flexible solution. Just starting the get it set up, but so far it looks good.
What is procedure for updating to a newer version? I noticed a link I followed gave me an older version without Yahoo added. I also wondered why you chose not to use Google to fill in the stock name? Hi Shawn, Nice to meet you. Alas for google spreadsheet it is difficult to link your sheets to my sheet. As to the question why not just use Google to fill in the stock name, the reason is that one of the objective of this sheet is for international investors to not feel left out.
I feel that the best thing i can do is to change it to use Yahoo Stock Name. Hi Drizzt, Ok, makes sense. But first I need to add some more sheets to the base template as I need to track stock across 5 accounts and still summarize in one place. One thing I have not figured out is your comment about dragging to copy rows. How does this work? I have currently 3 types of investments.
How can I add another category easily without destroying everything? I was initially thinking of using the categories such as Growth, Dividend to show cash and CPF instead. But if the same stocks is invested in Cash and CPF, it gets confusing which is which. Still busy playing around with it. So perhaps you would like to keep CPF seperate. Yahoo finance for Australia does not appear to work. I am not sure which stock you are refering to.
Hi, Thanks a lot for putting so much effort into this. It has been really helpful, especially for a technically challenged person like me. I have my past transactions, i need to somehow gather the past stock price and then calculate market value lets say end-of-month price of past-portfolio mix.
Any ideas how to do it? Your spreadsheet is amazing and so easy to use! So many people appreciate it.. I would like the cash to be reflected for example when i make a contribution to the investment account… then when i buy a stock i would like the cash to reflect the purchase of the stock, and vice versa when the stock is sold, and when dividends are received… Is the spreadsheet made to reflect the Cash in the account?
Hi David, thanks for the compliment. My spreadsheet is made flexible but i must have thought about what you want to do. I decided against it because it will make people very confused about how i want to do it.
Have a good one, david. We usually buy this in USD with a deteremined exchange rate at time of purchase. Thanks Oliver , I would put it as usd, but if you are adventurous you can put it under the sgd part as well! Sent from my iPad. Kyith My investment Blog: One question, I invest in stocks in many different currencies, how is it best to track these I like seeing pnl in USD. You will still have to manually enter the original forex rate though, unless someone else here knows how to pull historical forex rates.
I also really love your work on this portfolio tracker. But I also would really love to be able to include short sells. I hope you will be able to incorporate some day as I have been unsuccessful in trying to customize my copy! I have a question about how to modify the Stock Summary tab to show a view of what the portfolio cost based looked like at a particular date.
C ,0 ;1 ,0. Also, in the US, taxes on capital gains are paid at different rates depending on whether the holdings are long term held for 1 year or more vs.
This would be a great feature to break out gains by short term or long term. I am really excited to use your spreadsheet to track my portfolio. Thank you for putting this out there. One problem I am having is that the last four columns 52 week and day stats on the stock summary sheet are not pulling the data from yahoo finance.
One question, I want to manage it locally, for which I have to download it to Microsoft Excel which does not recognize formulas such as arrayfunction and filter. What is the solution? Use this formular in cell N3 of the Stock Summary wsheet. Afterwards you can drag down. Thanks for sharing this Mike, I was struggling with getting an alternative and this worked a charm. Just wanted to know if there any possibility to add interest cost as well, if the stocks are bought with borrowed money.
My cost will change everyday. The interest will accrue from the date of purchase till date of sale. I hope I am clear.. Please see if you could find some way out! Thanks for the update wrong me. Users will now use SGX: The first is inclusion of forex rates I created an extra column on the transaction sheet for the exchange rate when I bought the stock, and another column on the portfolio sheet with the current exchange rate.
Sorry to ask what may have been answered many times and a while back but I cannot find the answer; what symbols do I use for Yahoo Quote and Google Quotes and where do I get these from? Hi, wonderful spreadsheet, thanks a lot. How do I add one sheet per account? A little help and advice. I am now interested in HKD stocks listed on hong kong. Could you advice how I can start a new tab that tracks HKD and link back the information to the summary page? I used the one for usd as template for duplication, and changed all the usd to hkd.
However, at the transaction HKD sheet, the list of stocks in the drop-down remains as the ones in my usd stock summary.
Could you advice how to change this part? Thanks for the reply. Would you please help? Many thanks in advance. Hi Kyith, excellent job. You make me thinking to migrate my existing MS Excel portfolio files to this Google format althought my current copy is pulling data from web without much effort.
But we always like some sexy stuffs. Just wonder when you hind this? Finally saw your note after post a comment here. Still trying to understand some of the formulas in Google spreadsheet!
Will definitely share with more people out there. I have been trying to copy the format to Excel but realised that too many formulas go unrecognized in Excel. Hi Chatime, would it be possible for you to share an Excel file which pulls the data from Yahoo?
When I pull data it downloads too much. Probably your Excel file has implemented a way to neatly arrange the price data. Hi, This is a wonderful spreadsheet. Trying to work with it now. My question is it possible to track various stock markets like HK and Philippines and reflects local currencies as well? HK stock exchange is supported. Philippines look to be not supported. I notice Philippine stocks not supported by both Yahoo and Google. Would you know if I can use Bloomberg instead? Thank you so much for sharing.
If Bloomberg have a free feed that i can tap that would be good. As far as I know there is no way. Thanks Kyith for your help.
I hope Yahoo and Google would support the Philippine Stocks soon. Again thank you for sharing. Hi, is that the Tokyo Stock Exchange? I suspect they mean the Toronto Stock Exchange. This is available through google. Just tested—while google finance displays as TSE: Reading elsewhere I found it is necessary to write. TO and this does work. It is unexpected, as this stands for Toronto, Ontario, a common abbreviation for the geographical location of the exchange…. Perhaps it is a software limitations hence the quote.
Google Spreadsheets is great for comprehensively tracking the Toronto TSE, including mutual funds, which Yahoo does not. For a couple of years Google was botching the job on getting its accurate Finance data into the spreadsheet for a number of Canadian registered ETFs even some popular iShares but seems to have it back in hand again. Many thanks to the author for the template. Hi Thanks for this great spreadsheet, but i tried and maybe the only one out that that cant make it run, what happens is i tried 1 counter with all the transactions and stock summary , but something is amiss.
SI , it will overwrite the formulae. By default you do not have to touch Yahoo Data Ref. Yahoo Data Ref draws the data from Stock Summary.
Hi Kyith, yes, i would need some help to get this to run, ive just revert back yahoo data ref to its original form, do you need to click a button for it to start loading data into it? You dont have to click any buttons. Would kindly need you to help me take a look again, Thanks alot bro.
If you are free, allow me buy you coffee man. Hi, thanks for the spreadsheet, it has been very useful for me. May I know, I have been updating based on your previous version, and would now like to update my data records to your new version of the template. How do I do that efficiently? Do I need to copy and paste all the data over to the latest template again?
Hi Kyith, can you assist me with my question on the updating of your template to the latest version? I would also like to update the spreadsheet to the new version without entering all my data again. I currently have version 1. My version is also 1. How do I update to your latest version in the most efficient manner? Great spreadsheet you have, and many thanks for the effort! What a great spreadsheet! I have been looking for something like this for years after Microsoft Money ended.
I have a problem though. I have tried re-propagating the formula by dragging the blue cells in row 2 down for both pages , but still not working. Have shared the spreadsheet with you, please kindly take a look, thanks! I just copied the spreadsheet over to my Google Docs. I am only seeing the document information through the version history.
What am I missing here??? Thanks for the great spreadsheet and it does help me as it differentiates from other portfolio trackers by tracking transactions. May I know how the different indicators above, near, below are defined with sensitivity and how they could assist in decision making?
Currently I have customized my portfolio by adding an additional column at Ref sheet Stock Summary seems fine too. It have 3 columns, above stock price, below stock price and alert column. The alert column will show appropriate message when it matches one of the condition. You may want to consider using their script or customized your own and also by adding new column such as percentage change as well. Not sure if everyone share the same sentiment as me? Nice to see your reply. Not sure if these script can work with your spreadsheet, if not you may want to consider using formulas or other built-in function which can make this work?
Hi, I was wondering if the excel gurus amongst you all have figured out what ms excel formula to put in the transactions tab in columns H previous units and column K previous cost when the spreadsheet is exported to excel. The cell in column H has to pick up the last cumulative units in column i for the stock code on the left of each row.
Similarly, the cell in column k has to pick up the last cumulative cost for the stock on the left of each row from column N. If you can make it work, please private message me or share with us all. No one I have asked seems to have a formula for me. Hi, Thanks for replying. I am trying to decipher the formula so I can drag and drop it down the column s because I could not make it work.
From my understanding, it is saying if the row is the same row, return zero. If there is an error, return zero. Now the lookup formula is where I am encountering some difficulty comprehending:. Alternatively, if I was to drag the formula down, what would the formula be in row 5 for both the previous units and previous cost cells using the following as a simple example:.
Lets say the first 3 rows of the transactions tab are purely purchases so rows 2 to 4 row 1 is the heading are purchases of 3 different stocks.
Lets say in row 2 we purchased units of ABC stocks. What would be the formula in row 5 for previous units if I sold units of the same stock that was purchased in row 2 ABC stock so we should be able to return as the previous units in cell H5 and cell I5 would then just be units of ABC stock.
Alternatively, is it possible to email me the googlespreadsheet downloaded to excel with the first couple of rows of the transactions tab with the formulae dragged down for the 2 columns previous cost and previoius units so I can see what is happening?
Then drag formula down the column. Hi, I lazy is there anyway i can add a new sheet Commission sheet which cal the Commission charge and on Transactions sheet i can select rather than manual cal? I think you can always open up a new sheet for that but commission varies so you may really need to customized it.
Hi Kyith, I just added a stock but realised the live prices are not loading. It works now after following the steps. How would one use for stocks in other countries? NS pulls the Google price but not from Yahoo. This is a stock on NSE India. NS but the formula does not seem to be working for this stock. Would appreciate if you can take a look and comment. Like the example Stock Summary screen shot under the Conclusion portion on this page, some of my stocks under the Units and Costs column are grayed out.
At one time they showed the correct amount but for some reason they have now grayed out. It seems to occur randomly and to random stocks. Do you know what causes this? One day everything looks good and the next I get errors in fields where I have done nothing.. Mike the error was due to that the formulas for rows below being tainted. What you need to do is take the second row blue cells drag them all the way down to repropagate them.
Hi cuylar, I think its rather hard to implement it. That is why I would prefer the person to manually carry out those transactions. Thanks for the fast response. I believe you that it would be complex. I am exploring the spreadsheet tonight, this tracker is brilliant! Hope it is useful. The reason I came up with it originally because I thought there should be such a spreadsheet in the market.
I have been using your portfolio tracker in a paper trading contest. I notice that the portfolio calculations break if you enter short sales. But, based on your style I doubt that you intended it to be used for shorts. Hi, thanks for coming up with such a great spreadsheet. Could it be that I am not entering the data in the summary tab correctly? Hi, thanks for this very useful spreadsheet.
Can you give me a copy of version v1. So would like to see how exactly you did it. I am thinking using category as different accounts. Thank you so much Joseph, I hope its been helpful or inspires something that you can customized to help yourself. Hi kyith Thanks to provide so great information to us. I am using standard chartered online trading with no minimum charges.
Not sure why this happens. I am using a Mac. Appreciate advice pls and thanks for the great work! Thank you so much putting together this. I really appreciate the hard work and then sharing with the world, this is amazing thourght.
I wanted to use this sheet as MS Excel sheet. I am not a excel savvy person so having hard time getting to work in excel. Hi, thanks again for the wonderful work. Hi John, i am not sure if it will be easy or difficult, it depends on your understanding. After plugging in all my historical transactions, all I can say is that this is my go to portfolio tracker.
With our current offering of stock screening, side by side comparing, valuations and other features you get with Old School Value , the upcoming portfolio tracker is going to be powerful and simple way to keep up to date.
If you need a quicker and streamlined way to find new ideas, analyze and value stocks — play with the full live demo. Old School Value is a suite of value investing tools designed to fatten your portfolio by identifying what stocks to buy and sell. It is a stock grader, value screener, and valuation tools for the busy investor designed to help you pick stocks 4x faster. My wife and I are Christians and our focus is to love God and love people. We are proud supporters of Compassion and are grateful to be sponsoring 8 children.
We are ordinary people and understand that we cannot change the world, but we can try. Call me crazy, but our dream is to one day sponsor 1, children around the world. Thank you for helping us achieve this one child at a time. Fatten your portfolio with our checklists and other value investing resources straight to your inbox. Editing Transactions Step 2: Updating the Summary Transactions Data Step 3: Interpreting the Portfolio Summary Step 4: How to Edit the Table in the Spreadsheet Step 5: How to enter transactions.
Box Mercer Island, WA Under no circumstances does any information posted on OldSchoolValue. The information on this site, and in its related application software, spreadsheets, blog, email and newsletters, is not intended to be, nor does it constitute, investment advice or recommendations. In no event shall OldSchoolValue. Past performance is a poor indicator of future performance.