June 5, by Kyith Comments. The stock portfolio tracker is a FREE Google Spreadsheet hosted at Google that can do the following:. Would like to Thank Everyone for the support! I created this in and have received overwhelming responses on how it managed help so many folks who is looking for a sophisticated spreadsheet that you can trust. Would like to thank the first time visitors from Old School Value and Bogleheads. Unfortunately, my IP was banned at Bogleheads for reasons I do not know of!

Not many Stock Portfolio tool or spreadsheet offer for free tracks your portfolio by transactions. By entering these transactions we will be able to keep record but also good data to easily visualize these questions. You can then construct different review perspective whether it is return on investment or different portfolio category.

After we enter the transactions as the transactions happen, we can review the stock in the Stock Summary sheet. Note that the formulas to derive Expected Annual Dividends and Expected Annual Dividend Yield is not coherent. This is because for some unlisted assets, you forecast the dividends based on the amount while for others you calculate the amount based on the forecast yield.

Well that causes money and intuit is not the only provider. Microsoft used to provide MS Money but they have stopped the support for it. Some folks may have concern with going too far with the app and difficult to switch to an alternative if another MS Money happens to their paid app.

You can always download the data as an excel xls or xlsx workbook to backup locally or if u intend to port it to another platform. Those work only in Google Spreadsheets. One problem with Quicken and local excel spreadsheet is you only can access your portfolio on your computer. You do not have to click Share and ask for my permission.

Yellow cells — these are cells that user will need to fill in appropriate data input. Light blue cells — these are cells that will be computed by Stock Portfolio Tracker automatically. Do not fill in your own input here! This is where you define the stock name and symbol. Upon viewing it for the first time, you will see rows of already populated stocks. Those are my data. Remember blue cells are computed cells! For this to work you will need to find out the stock quote of the stock you want to track in Google Finance or Yahoo Finance.

Should your stock belong to exchange supported by Google Finance e.

US exchangeentering the quote will enable Google Spreadsheet to automatically update your stock data every time you review your spreadsheet. Update 28 Apr As of today, Singapore SGX Stock Exchange is supported. Instead of what is depicted in the picture, look up the symbol in Google Finance.

My personal preference is still Yahoo Finance. They have a large number of supported exchanges. The difference between Google and Yahoo is that Yahoo supports more decimal places, Google can only go up to 2 decimal places. Yahoo takes last trade price while Google seem to take active trade price. Thus if your stock is halted for long, Google will show price 0, which may not be what you want. The upside for Google is that the data seem to be REAL TIME.

So you may be able to make use of it. If you are unable to get live prices from Google Finance or Yahoo Finance, you will need to update the last done price manually every time you open this spreadsheet to review. The sequence is to take Yahoo Finance as a priority, then Google, then Manual. One of the main purpose is to track dividend stocks so there is a column that yield investors can make use of to track for this stock how much yield on cost yield on your average cost of stock purchase you expect.

It is to help an investor better segregate the stocks that he or she is tracking. The most important row is row 2which is the first transaction row. Each new transaction you create is done by selecting the previous row and dragging it down one row and then amending this new row. Do Not Delete this Row 2! For your first transaction, amend the yellow cells in this row. From the drop down select the type you are entering.

For all transactions, enter the date of transactionselect the stock by name and select the type. In the case of Global Testing above, this is a reverse split where 20 shares become 1 share. So 49, shares become 2. Most of the time there is no cost. If you are using the Cash Register, you will then enter a corresponding Inflow money coming into the register and select Capital Return note the image may differ a bit as I am using an example.

You will enter the full amount coming in. When this happen can be during your monthly review, you enter the dividend transactions that are paid between first of this month and the last day of the month.

I usually do it during my month end portfolio review, where I key in the dividend transactions that was paid to me for the month. You will also be able to see your average cost of current shares for each stock. If you learn from my formulas you can insert more columns possibly to calculate XIRR or total gains or losses.

We also provide 2 worksheets so that as bloggers you can easily tell your readers how much dividends you collected each month and how much gains or losses you realise. I get this question a lot and so let me highlight it here. You do not need permission to edit MY stock portfolio tracker.

You create your own. This happens to be a strange bug that i cannot get rid of and i have to apologize for not being able to solved this. That said, the work around solution is very simple. The reason the price shows wrongly is because the Stock Portfolio Tracker fails to grab the last price from Yahoo. Follow the instructions below to refresh any new stock added. This means that every time you add a stock such as Intel that you have not keep track of previously, you do this.

Once you are keeping track of it, you do not have to do this. This is where the data is retrieved. At the top formula bar fxclick and use Ctrl A on your keyboard to select the whole formula string. Click on a cell that is out of range like what is shown in the picture above. This should make the rest of the cell computation disappear. Now click on cell A2 again. Use Ctrl V to put back the formula you cut away just now. Now check your Stock Summary that the last price is updated.

On the Yahoo Data Ref, you should also see that the new stock is correctly retrieved. 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. So take for example First REIT has a rights issue. You currently have shares. Bonus shares are company paying you shares instead of cash. Take the example of Challenger Technologies issuing a 1 FOR 2 bonus shares. This means if you have Challenger shares you get crazy good deal! Bonus shares on my Stock Portfolio Tracker is actually buying the stocks at ZERO transacted price. You input a Buy transaction with a quantity of shares at a transacted price of ZERO with ZERO commission.

Some companies like Aims AMP, Exxon Mobil have reinvestment plans. 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. If you need to make one new set for say Canadian Dollars, you need to duplicate them, then rename them as Stock Summary CAD, Transactions CAD and Yahoo Data Ref CAD. In this Stock Summary example, the new Stock Summary CAD you created, go through each cell in Row 2 to find references to Transactions, Stock Summary or Yahoo Data Ref, rename them by appending the CAD behind so that they will eventually reference to the right sheet.

In Yahoo Data CAD Ref, go to cell A2, then also rename the Stock Summary by appending accordingly. To aggregate the new CAD currency stocks in Portfolio Summary, there is no easy way but to create a new row below HKD Securities and then copy the formulas from HKD securities and edit accordingly.

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 spreadsheetmake 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. Portfolio Management Tagged With: DRIPexcelgoogle spreadsheetquickenspreadsheetstock portfolio trackertrack dividend stockstracking stocks.

June 8, 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. June 10, at 6: November 26, at 2: November 27, at 7: Most likely not since I do not have competency in that area. What is required to adapt to options trading.

June 9, at 8: Thank you for such a brilliant worksheet! June 10, at 2: June 11, at 5: June 12, at 5: June 13, at June 22, at 5: ThanksI created a simple one page version previously but your is much more comprehensive.

June 23, at 5: 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. June 25, at 1: Hi, thank you for providing such an excellent worksheet. July 2, at July 9, at 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? July 10, at 9: July 11, at 6: 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: July 11, at 8: Ah, I think I found it. July 11, at I can either set 4 decimal places or currency, but not both. July 12, at 5: I would like to request as others did, to be able to track my stocks to know based on the transactions buysellrights, 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. July 15, at 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. July 21, at Can input in dividend also. July 23, at 4: July 24, at 8: Hi, thanks for sharing this template. How do I convert to the same google spreadsheet as urs? July 27, at 6: August 4, at 5: Only managed to achieve half of what you have done here. August 6, at 5: 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.

August 7, at Have one request though…I noticed that the currency transacted is in SGD, if I buy a stock that is in USD, maybe later versions can track the transaction in SGD and exchange rate too? When I sell it at This would give a more accurate value of portfolio summary in SGD. August 9, at 6: I did thought about this. August 7, at 4: August 9, at 4: 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.

August 13, at 6: August 13, at 2: 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. August 13, at 4: The example of Food Junction in your transaction sheet, why does the dividends not bring down the cumulative cost? September 7, at 5: May i check with you how to modify the Yahoo Data Ref? I looked at your formula, but seriously, i dont understand ….

Options Exchanges Hit Again By Data-Feed Trouble

September 18, at Thanks for sharing your spreadsheet. September 23, at 6: September 24, at 1: October 20, at 8: 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. October 21, at 6: October 25, at 9: 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. October 26, at 6: 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. November 12, at 1: November 12, at 8: 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. November 18, at 5: 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?

November 20, at December 12, at 6: December 12, at January 2, at 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. January 8, at January 10, at 1: 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! January 10, at 5: 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? January 21, at 2: 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.

January 21, at 8: 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. January 25, at 2: 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. January 25, at 5: January 25, at First of all, thank you for this awesome stock portfolio tracker template. It have just replaced my default template. January 27, at 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.

January 29, at 5: January 29, at 8: Hi Hieu, dividend re-investment as in DRIP or Scrip dividends on your own? You have to be a little bit more explicit. February 2, at 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? February 4, at 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. February 7, at 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.

March 6, at 4: 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. April 5, at 3: 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. April 12, at 7: 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. April 12, at 9: April 15, at 4: April 15, at 7: April 26, at 9: Hi Pretty Neat tool from what I read. I plan to download soon. How does it if it does track options trades? April 26, at 5: May 6, at 6: May 6, at 7: When i exercise it i will treat it as a purchase of stock at the average price.

May 12, at So in buying and selling rate of euro in india today meantime, we have to track unexercised warrants separately? So I had input in the stock myself. May 17, at 4: Thanks for sharing the spreadsheet. I am trying to create an excel version of this. May 20, at 4: May 20, at 7: May 27, at May 28, at June 22, at 1: 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!. June 23, at 9: Hi Drizzt Is ther an issue with thre spreadsheet or is it cp cheats cp money maker 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? June 23, at 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?

July 3, at Thank you so much for sharing the spreadsheet! July 7, at July 10, at July 14, at July 15, at 1: July 15, at 3: July 15, at 5: Guess using your suggestion….

July 15, at ingersoll rand stock prices July 16, at 9: 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? July 17, at Hi Micheal, I am using the Standard Chartered Online Trading platform which does not have a minimum fee. July 18, at Thanks Drizzt, after google about it decide not to go for it.

Keep up the good work here, will visit often. July 27, at 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. July 27, at 9: I will try to work on it and hope i can hit something.

July 28, at 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. August 2, at 1: 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.

August 3, at 7: August 4, at The link to your spreadsheet does not work for me. It leads to something other than the spreadsheet shown.

I am very disappointed. August 4, at 1: August 5, at August 8, at 1: 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.

Frank Denneman

August 12, at 3: August 14, at 5: This is regarding your dividend stock tracker. August 15, at August 25, at 5: Is there an option for archiving and retaining the realised gains? August 26, at For myself, i dont actually need to account for them. You may even want to call them another name. August 26, at 9: 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.

September 3, at 5: September 7, at First let me thank you for your spreadsheet: September 10, at 7: September 13, at 9: Also, can you add a macro which allows you to schedule query refresh based on 1,2,5,10 and 15 mins period. September 15, at 2: 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: September 14, at 8: 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. September 20, at Hi, how would you suggest I do a Dividend Reinvestment meaning, I get shares automatically, never seeing the cash?

September 21, at 8: 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, stock options trading halted after data feed problem approach is NOT to have a Div entry. So what i would put a Buy 11 shares at zero cost. September 22, at 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.

September 23, at 9: The STock Summary aggregates the transactions in the transactions tab. What you do is that once you have name your stock in the Stock Summary, clear the rows of transactions in Transactions tab EXCEPT row 1 and 2. Edit Row 2 by using the drop down to select your stock name. September 25, at 3: September 29, at 5: September 26, at 1: Hi Drizzt, I have another question. The Yahoo Data Ref USD are all dashes. Do I need to edit the formula in A2? September 27, at 2: 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. September 27, at 6: First of all, thanks for creating this great template. September 28, at Hi Drizzt, previously I was given 1 lot of K-Green by Keppel Corp as dividend. May I know what type of category should I put?

September 29, at 9: For How to make money with an acreage 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?

September 30, at Hi Terence, there is a split transaction. September 30, at 9: 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. September 30, at 8: Can direct me where to check US stock, e. Is it correct that NASDAQ website only have companies that are electronic related?

- Errore:

October 1, at 7: October 31, at 8: 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 how do you get free coins on yoville facebook 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 then go back to the stock summary sheet and link fastest way to make money gta online solo DPU cells to the downloaded DPU sales on the Yahoo Ref sheet.

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. November 8, at This way i can find out new ways of doing it. Do note that yahoo data ref can only get at how much can you make a month on cashcrate symbols if i remember correctly i gotta test them out.

October 31, at 4: 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? October 31, at 9: November 7, at 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.

November 9, at 2: Then I added a new sheet in which I linked the cashflow, the dates, the current value and the current date in order. With those inputs I calculated the XIRR. Finnaly I linked in Portfolio Summary the number. Here is a copy of my wifes portfolio with those changes, https: November 11, at 6: 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. November 11, at November 12, at 4: I could not make the formula work buy mcdonalds stock excel when i put it in cell H2 of the saved excel file to return the equivalent to the googlecalc.

November 15, at November 16, at 8: November 16, at November 18, at 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…. November 22, at November 20, at 6: 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?

December 3, at 6: Does your spreadsheet allow me to do that? December 3, at December 3, at 7: December 3, at 8: December 16, at 4: 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?

December 16, at 5: Hi Ammar, of course that is my dream but Google Spreadsheet have not develop the VBS capability of excel. How would you want to add stock options? December 16, at 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 trials hd stock market achievement know of anything that does what we want in real-time connectable to TD Ameritrade or any other real-time quotation provider?

December 18, at 8: December 27, at 8: 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. December 27, at I have requested permission to view the spreadsheet. December 27, at 7: December 29, at 2: 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. December 29, at 4: You specify the name of Goldman Sachs and the symbol as well as the dividend yield at the Stock Summary.

December 30, at 4: 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. December 30, at 8: Hi Jen perhaps you can share your sheet with me kyith.

December 31, at January 1, at 8: January 13, at Second, how di I deal with a stock spin off? For example ABT spun off into 2 separate companies, ABT and ABBV. Sorry if this should be basic knowledge. Keep up the great work. Your solution is easily the best I have found including paid software. January 19, at 2: January 20, at 7: January 22, at 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 achieve trading binary options free trial January 27, at 1: Are you able to share how you build up this excel spreadsheet?

February 2, at 4: Hi Stella, i am confused. This is stock options trading halted after data feed problem 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. February 1, at 5: May i know how do i add row onto transaction, monthly and yearly spreadsheet? Can i just copy and paste the row on top?

February 2, at 5: HI Wei Liam, you can copy the last row and drag it down so that the formulas will propagate downwards. February 9, at February 9, at 8: February 9, at 9: 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 sample executive stock option plans on stockholders and bondholders that the best thing i can do is to change it to use Yahoo Stock Reasons to buy stock in nike. February 10, at forex run in sap 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. February 10, at 1: February 20, at 1: One thing I have not figured out is your comment about dragging to copy rows. How does this work? February 20, at 8: February 25, at 1: I have currently 3 types of investments. SGD, USD and CPF. 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. Could I then use Stock summary to create two for exampls STI ETF for cash forex run in sap STI ETF CPF for cpf investment? Still busy playing around with it. February 25, forex affiliate programs cpa So perhaps you would like to keep CPF seperate.

March 1, at 2: Has YAHOO changed the format for retrieving listed-option data? March 3, at Hi, Just wondering how to link the quotes to an Australian-based price source such as the Mini cooper coupe performance parts Stock Exchange ASX. Yahoo finance for Australia does not appear to work. March 3, at australia day trading hours 2013 nsw I am not sure which stock you are refering to.

March 11, at 9: 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? March 11, at March 23, at 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?

March 24, at 3: 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. March 25, at Have a good one, david.

March 26, at 2: For Gold ETF SPDRshould I put this item in the SGD or the USD tab? We usually buy this in USD with a deteremined exchange rate at time of purchase. It would be easier if we can have this listed in the SGD tab for the purpose of tacking our PP. Thanks OliverI 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: March 26, at 8: One question, I invest in stocks in many different currencies, how is it best to track these I like seeing pnl in USD.

April 30, at 3: You will still have to manually enter the original forex rate though, unless someone else here knows how to pull historical forex rates. May 13, at 2: March 26, at 9: 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! April 4, at 9: 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. April 11, at 2: 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.

April 11, at 4: April 23, at 6: April 23, at 7: April 24, at 3: 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.

November 19, at 8: December 2, at Thanks for sharing this Mike, I was struggling with getting an alternative and this worked a charm. December 2, at 3: February 18, at 7: April 24, at 9: 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. April 26, at 6: 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! April 27, at 4: April 26, at April 27, at 9: April 28, at 5: Thanks for the update wrong me. Users will now use SGX: April 30, at 4: 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.

May 2, at 3: May 3, at 5: May 4, at May 4, at 9: 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? May 6, at 9: May 7, at 3: Hi, wonderful spreadsheet, thanks a lot. How do I add one sheet per account? Wai Keong Chan says. May 11, at 2: 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? May 13, at May 13, at 1: 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? Append a HKD being Stock Summary. May 14, at May 14, at 6: May 21, at 5: Thanks for the reply. Would you please help?

Many thanks in advance. May 20, at 6: May 14, at 8: 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? May 15, at 6: 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. July 2, at 1: 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. May 15, at 9: May 22, at 4: June 5, at 5: 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?

June 7, at 6: HK stock exchange is supported. I tried with PICO FARE EAST Yahoo Stock Quote Philippines look to be not supported. June 10, at 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. June 11, at 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. June 7, at 1: Hi, is that the Tokyo Stock Exchange? I suspect they mean the Toronto Stock Exchange. This is available through google. July 15, at 4: 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. October 12, at 9: 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 summarybut something is amiss. SIit will overwrite the formulae. By default you do not have to touch Yahoo Data Ref. Yahoo Data Ref draws the data from Stock Summary. For Singtel, if you have keyed in the right quote in Yahoo Quote in STock Summary, that should suffice.

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?

June 11, at 1: You dont have to click any buttons. June 11, at 7: June 12, at 8: June 17, at Would kindly need you to help me take a look again, Thanks alot bro.

If you are free, allow me buy you coffee man. What happens is i tried to edit my months of transaction and the whole sheet go VALUE. June 16, at June 17, at 7: June 17, at 8: July 4, at 9: 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?

July 5, at 5: Hi Kyith, can you assist me with my question on the updating of your template to the latest version? July 5, at 9: July 30, at I would also like to update the spreadsheet to the new version without entering all my data again.

I currently have version 1. September 16, at 5: 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. After filling in a lot of old transactions for a stock, columns R,S and T on stock summary page failed to work, showing VALUE! I have tried re-propagating the formula by dragging the blue cells in row 2 down for both pagesbut still not working.

If you have problem share with your sheet with me at Kyith. Have shared the spreadsheet with you, please kindly take a look, thanks! July 17, at 8: July 17, at 9: July 18, at 3: July 20, at 7: July 25, at 9: August 15, at 3: 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??? August 15, at 9: August 29, at 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? August 31, at 1: 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. STI ETF current stock price 3. FYI, there seems to have 2 scripts in the Google Script Gallery which have this functions. 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? September 1, at 6: September 1, at 7: 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?

September 1, at 8: 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? September 8, at 4: 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?

September 10, at I think you can always open up a new sheet for that but commission varies so you may really need to customized it. September 19, at 5: Hi Kyith, I just added a stock but realised the live prices are not loading.

However, I do not know how to edit further as I do not understand the Yahoo Data Ref formulas. Under Stock Summary, I am at my 52th stock now, while at the 46th stock under YDF.

September 21, at Hi Kyith Sorry I missed the FAQ section. It works now after following the steps. How would one use for stocks in other countries? Say, entering HDFC Bank HDFCBANK. NS pulls the Google price but not from Yahoo. This is a stock on NSE India. Yahoo site does show data for HDFCBANK. NS but the formula does not seem to be working for this stock.

Would appreciate if you can take a look and comment. October 10, at 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? October 12, at 1: November 26, at 6: One day everything looks good and the next I get errors in fields where I have done nothing.

Hi Mike, would you like to share your sheet with Kyith. November 26, at 7: 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. November 26, at October 31, at Hi cuylar, I think its rather hard to implement it. That is why I would prefer the person to manually carry out those transactions. October 31, at 1: Thanks for the fast response.

I believe you that it would be complex. I am exploring the spreadsheet tonight, this tracker is brilliant! October 31, at 2: Hope it is useful.

The reason I came up with it originally because I thought there should be such a spreadsheet in the market. November 9, at 1: 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. November 9, at 7: Herng Huei Foong says. November 3, at 6: 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? November 3, at 7: November 4, at 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.

November 8, at 4: I am thinking using category as different accounts. November 9, at 3: November 19, at 2: 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.

December 5, at December 6, at December 14, at Not sure why this happens. I am using a Mac. Appreciate advice pls and thanks for the great work! December 15, at 8: December 15, at 2: December 20, at 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. December 24, at December 24, at 1: December 24, at 7: December 22, at 4: Hi, thanks again for the wonderful work. December 31, at 3: December 31, at 5: Hi John, i am not sure if it will be easy or difficult, it depends on your understanding.

If you look at the sheet Yahoo Data Refs you will see that there are a string of letters at the back. Add the 2 you want in, then at each Stock Summary, modify the vlookup to grab the approporiate column. January 12, at 8: I would preferably like to have this on the YTD sheet and potentially something that happens similarly on the monthly sheet.

Thanks for any insight. January 12, at Hi Kyith, thanks for responding so quickly and thanks for such a great tool. I will tinker with the sheer and see what I can figure out. January 26, at 5: I have just started using this and think that its a fantastic tool!! I also think it could be useful to have a function that provides an annual return figure, say at the end of a financial tax year or calendar year, so that the percentage theoretical return could be seen.