Howdy, Stranger!
It looks like you're new here. If you want to get involved, click one of these buttons!
Quick Links
Calculation for Profit%
I have a stock (AAPL) in which the dividends have paid for the original Cost Basis and I believe it is throwing the Profit % in the Holdings tab. Currently, the Profit % is showing -100% where my information on the stock is:
Cost Basis Total Value Capital Gain Dividends Profit
270 5,463 5,193 341 5,534
Is there anything I can do to set this correctly?
Cost Basis Total Value Capital Gain Dividends Profit
270 5,463 5,193 341 5,534
Is there anything I can do to set this correctly?
Comments
2. Go to Mthly worksheet and at the very bottom check report that shows capital gain for last 12 months- check AAPL - do you see any strange behavior - example one month there is huge negative capital gain.
3. Make sure that you do not have dividend payments for months when you did not have any shares. If you received dividends after you sold shares, then change dividend day to be the same as sold date.
4. Did you check this: http://support.portfolioslicer.com/discussion/216/calcs-not-working#latest ?
If above does not help, you can send me your workbook. You can delete transactions for all other symbols (if you want for privacy), but make sure you refresh data and reproduce problem with existing data. My email address is posted on this page: http://www.portfolioslicer.com/contact.html
Thanks for the comments and the quick reply! I have reviewed all the items you suggested and none seem to affect the calculation. I have done the following:
1. The split is documented as a Buy with the appropriate additional shares.
2. Nothing looks out of the ordinary on the Mthly sheet.
3. I still own the shares so the dividend payments shouldn't be an issue.
4. I don't have any tags in my psConfig file per the other discussion.
If you don't mind, I will take you up on your offer for your review of the spreadsheet. I will reference this discussion and again, I appreciate you helping me out.
As a side note, I am a member of Motley Fool and they have the same issue with dividend stocks in their scorecard. I asked them for help on the calculation and this is what they suggested:
"Return = (Sale Price - Total Cost + Total Dividends) / Total Cost
TotalCost = $7.72
Dividends=$9.76
SalePrice=$155.85 (as I compose this message)
If you plug these in, your return is something like 2045%. Which is roughly the same as adding the 1924% that scorecard will give you for capital gains, plus the 126% which is the dividend gain versus the cost basis."
Thanks again Vidas!
--CubbieFan
Thanks for sending me your workbook. I investigated just one symbol - AAPL.
You have first buy transaction for this symbol on 2011-12-30. But quotes for this symbol start just from 2012-12-31. As you do not have year of quotes - that distracts Profit % calculation for that year and that affects your total value.
Solution:
Backup quotes file Quotes\_AAPL.txt and Quotes\_AAPL_Archive.txt, delete them from Quotes folder, make sure that in PSConfig.txt your MinDate matches what you have in Excel config and re-run quote extract.
Profit % calculation is more complicated than what you described above. For example I am getting dividens every quarter and re-investing them into same symbol and on top of that I am buying more of the shares for same symbol. In such case calc becomes more complicated.
In PS I calculated Profit % monthly - so that any additional buys are properly reflexted and then that monthly data is geometrically joined.
Not surprisingly, you are 100% correct. I'm sure that I had a mismatch in my start dates when I stared trialing your spreadsheet and didn't clear the quotes folder before I re-ran all the quotes with my "production" data. Thanks so much for your help with this!
--CubbieFan
Thanks again for the above solution which worked. I have since imported all my holdings and accounts into PS and have a similar issue with Zillow (NASDAQ:Z) on the calculation of Profit %. I looked in the Quotes.csv to discover that the quotes don't go back to 2014 which was when I purchased the equity. I then added quotes for Zillow into the Manual folder of the Quotes directory which worked for UA quotes prior to the split between what was UA.C and the present UAA. THe issue seems to be that every time I run the UpdatePSData.dat, it moves those quotes to the quotes_Archive file. I have attached them here and would appreciate any guidance you have. I was hoping to figure this one out on my own, but no such luck. Thanks again.
PS for last 40 days uses daily quotes and before that it used monthly quotes. So to load less quotes all historical quotes are divided into 2 files - original files keep just one quote per month and another file _Archive.txt is created to keep daily quotes. There is no benefit for you to load daily old quotes into PS - they will not be really used.
So what I see in your files is - there is one quote per month per symbol in Quotes.txt and daily quotes in _Archive.txt file - exactly as expected.
If for any reason you want also include _Archive.txt quotes (daily), you can change psConfig.txt parameter IncludeQuoteArchiveFolder.
Scripts that splits quotes is called "ArchiveOldQuotes.ps1" and if you do not want archiving, you can remove that script execution in .bat files.
I appreciate the monthly pull of the quote but the issue is that every time I run UpdatePSData.dat, it moves all the monthly quotes that I listed in the manual folder for Zillow because they aren't available in the Historical back in 2014 to the archive folder and they don't show in the Quotes.csv file for the spreadsheet to process. In the Archive file that I attached, you can see all the NASDAQ:Z quotes that I added originally to my non-Archive file. Sorry for confusing this and thanks again for your guidance here.
I think now I understood your problem. You put all symbols into one file and archiving script does not properly handle that. What you should do - for each symbol create new file (example: nasdaq-z.txt, ua.txt) and save these files in manual subfolder. Then run your .bat file and all should be OK. Let me know if you still have issues after that.
When creating these separate files at first also include records from _archive file. Then there will be archive file created for each symbol.