The fault was all mine. I would say currently my spread sheet works only in Google Spreadsheet. Would you mind sending me file? You can also reach the Office Store through the Insert tab in Excel.
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. Rather than going through hundreds or thousands of transaction data to figure out how you are doing, the Summary tab condenses it to a single line per stock.
Remember that the yellow cells are the ones you fill up and the blue cells contain formulas which you copy and paste to the next row.
The focus is a simple profit and loss analysis of your portfolio. For every new position, you have to edit the range of the cells for the data to be updated in the graphs. Click on the table once and a small menu icon appears in the corner. In the spreadsheet, there are 15 positions. To see the ranges in more detail, click the grid icon next to the range values to bring up this window.
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. Stock Connector will update the stock price instantly when you enter the ticker symbol for the first time and every 15 seconds after that for as long as you have the Excel worksheet open.
The add-in works in the Excel Desktop version as well as online. This means you can use One Drive and other cloud storage services for your stock-tracking Excel worksheets and not lose the Stock Connector features. Stock Connector can retrieve information about any stock, indexes, ETFs, etc. However, for more obscure securities you may have to do some research to find the right ticker symbol. I used Excel for the example in this article, but the add-in was originally designed for Excel and presumably works in that version as well.
Note that while Stock Connector does not cost any money to install and use, it does display a small advertisement in the lower portion of its window. It is unobtrusive, but it is there nonetheless and you may notice it from time to time.
Do you have a favorite add-in that brings more power or convenience to Excel? Share your recommendations with fellow TechRepublic members. Kaelin has been writing and editing stories about the IT industry, gadgets, finance, accounting, and tech-life for more than 25 years.
Most recently, he has been a regular contributor to BreakingModern. Inside the Raspberry Pi: How self-driving tractors, AI, and precision agriculture will save us from the impending food crisis. How IoT, robotics, and AI are tackling one of the biggest problems of the century.
How digital farming is revolutionizing the future of food.
Copyright © 2017 · All Rights Reserved · Maine Council of Churches