randywc - thanks for sending me workbook and reporting this issue. I am reproducing it, but not sure why you are getting this error. But the fix is easy: In dividends worksheet, at the top very right there is a last report (AR-AW) "Dividend amount by type" with filter for TransTypeGroup that has multiselect. Click on that filter and choose values: Dividend and Div Reinv. Or you can just delete that report. I'll look more into this and will try to understand why this error is there. I'll release fix later.
First of all, I want to thank you for creating this great tool for us! I'm trying to upgrade to 2.4 version with latest 2.4.02. I'm using 2.3.0 version, which I have a history from 1999. But it seems the new version only takes year 2014-2017. Here is what I've done for upgrade: 1, Updated psconfig.txt,
1998-12-31
Which is working fine, as quote and dividend history are fetched completely from 1999 to now, using Yahoo. 2, Updated src tab with the same MinDate, and copied data for srcSymbol, srcTrans. 3, After refresh all, it seems only 2014-2017 transactions are taken in.
Not sure what do I miss?
Then I used the original 2.4.0.2 spreadsheet, with just MinDate and some transaction dates modified. It seems only showing 2014-2017 as well. Attached the workbook and config file here. I'm using Excel 2016 version. Any insight is appreciated!
Inside Excel workbook PowerPivot model Date table has values just up to 2017-12-09. So 2 possible options for that: - in the file c:\PortfolioSlicer\PSData\Dates.csv you have old dates. Maybe you change PSData location in old version? - you have not refreshed Dates table
You're right! I didn't change the path of PSData locations in the connections. After change the PSData location, a full data refresh caused Excel to crash. Though Excel was able to recover by itself. It complained about some invalid conditional format. I attached the the recovered workbook if you're interested.
when you say "caused Excel to crash" - did you see a message "Excel not responding", or did Excel stopped working? Would you be able to post exact message about "invalid conditional format"?
Refresh would run a while, Excel showed "Excel stopped responding", then "Excel is trying to recover your information", which is able to generate a recovered version. Below is the log file details from the repair: <?xml version="1.0" encoding="UTF-8" standalone="yes"?> error203120_01.xmlErrors were detected in file 'C:\Users\Lenovo\AppData\Roaming\Microsoft\Excel\PortfolioSlicerV2 (version 1).xlsb'One or more invalid conditional formats were removed from the workbook
You have not setup symbols, yet you are loading quotes for symbols that are not defined in symbol table. This makes Excel "go nuts" and use memory until in crashes.
So - get new Excel workbook from Download page. Update PSData location in that workbook. - get a list of symbols from your Quotes.csv and add symbols to Excel Symbol table. Even if you do not have transactions for these symbols, if you have Quotes, you need to have symbols in the table. - refresh data
Thanks VidasMatelis! This time it works. I downloaded the new workbook, synced symbols from Quotes.csv with Symbol table, it refreshed fine. Though it would be nicer if it wouldn't require the symbol list have to be the same. Attached a workbook with added sector/sensitivity lookup table, so sensitivity value can be lookuped up. I figure it would be easier to maintain sensitivity values this way.
I noticed that ReturnOf Capital (SrcTrans sheet) reduces the cost base, but also adds a cash holding to the account. Not sure if that is the intended operation, I though the cost base should be reduced only.
That is intended behaviour, because ReturnOfCapital is actual dividend type that is paid to you. Here is how I handle it: Lets say I have 1000 shares of ZCN.TO. During the year it paid regular dividends and at the end of 2017 it reported 0.103493/share as "Return of Capital" distributions and 0.25$/share as "Reinvested Distributions", that is NotionalDistributions.
During the year I receive dividend payments and record them as "DivTA". Sometime in March of next year, I receive information that some of that dividend was classified as "Return Of Capital". Then For last day of the year, I enter 2 transactions - one to reduce dividends for that symbol by amount (0.10349 per share) of "Return of Capital" and another to add transaction "ReturnOfCapital": 2017-12-31 DivTA ZCN.TO 1000 -100.35 2017-12-31 ReturnOfCapital ZCN.TO 1000 100.35
For NotionalDistribution (Reinvested distributions) I add just one transaction: 2017-12-31 NotionalDistribution ZCN.TO 1000 250.00
I have about 400 transactions. The Excel crashed while refreshing. I'm trying to add a small number transactions each time. It still crashes now and then, seems to be an issue.
Comments
In dividends worksheet, at the top very right there is a last report (AR-AW) "Dividend amount by type" with filter for TransTypeGroup that has multiselect. Click on that filter and choose values: Dividend and Div Reinv. Or you can just delete that report. I'll look more into this and will try to understand why this error is there. I'll release fix later.
Thanks again for reporting this!
First of all, I want to thank you for creating this great tool for us!
I'm trying to upgrade to 2.4 version with latest 2.4.02. I'm using 2.3.0 version, which I have a history from 1999. But it seems the new version only takes year 2014-2017. Here is what I've done for upgrade:
1, Updated psconfig.txt,
1998-12-31
Which is working fine, as quote and dividend history are fetched completely from 1999 to now, using Yahoo.
2, Updated src tab with the same MinDate, and copied data for srcSymbol, srcTrans.
3, After refresh all, it seems only 2014-2017 transactions are taken in.
Not sure what do I miss?
Then I used the original 2.4.0.2 spreadsheet, with just MinDate and some transaction dates modified. It seems only showing 2014-2017 as well. Attached the workbook and config file here. I'm using Excel 2016 version. Any insight is appreciated!
Regards
Weidong
Inside Excel workbook PowerPivot model Date table has values just up to 2017-12-09. So 2 possible options for that:
- in the file c:\PortfolioSlicer\PSData\Dates.csv you have old dates. Maybe you change PSData location in old version?
- you have not refreshed Dates table
Please check these options.
You're right! I didn't change the path of PSData locations in the connections. After change the PSData location, a full data refresh caused Excel to crash. Though Excel was able to recover by itself. It complained about some invalid conditional format.
I attached the the recovered workbook if you're interested.
Regards
Weidong
when you say "caused Excel to crash" - did you see a message "Excel not responding", or did Excel stopped working? Would you be able to post exact message about "invalid conditional format"?
Refresh would run a while, Excel showed "Excel stopped responding", then "Excel is trying to recover your information", which is able to generate a recovered version. Below is the log file details from the repair:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
error203120_01.xml
Regards
Weidong
You have not setup symbols, yet you are loading quotes for symbols that are not defined in symbol table. This makes Excel "go nuts" and use memory until in crashes.
So
- get new Excel workbook from Download page. Update PSData location in that workbook.
- get a list of symbols from your Quotes.csv and add symbols to Excel Symbol table. Even if you do not have transactions for these symbols, if you have Quotes, you need to have symbols in the table.
- refresh data
Attached a workbook with added sector/sensitivity lookup table, so sensitivity value can be lookuped up. I figure it would be easier to maintain sensitivity values this way.
That is intended behaviour, because ReturnOfCapital is actual dividend type that is paid to you. Here is how I handle it:
Lets say I have 1000 shares of ZCN.TO. During the year it paid regular dividends and at the end of 2017 it reported 0.103493/share as "Return of Capital" distributions and 0.25$/share as "Reinvested Distributions", that is NotionalDistributions.
During the year I receive dividend payments and record them as "DivTA". Sometime in March of next year, I receive information that some of that dividend was classified as "Return Of Capital". Then For last day of the year, I enter 2 transactions - one to reduce dividends for that symbol by amount (0.10349 per share) of "Return of Capital" and another to add transaction "ReturnOfCapital":
2017-12-31 DivTA ZCN.TO 1000 -100.35
2017-12-31 ReturnOfCapital ZCN.TO 1000 100.35
For NotionalDistribution (Reinvested distributions) I add just one transaction:
2017-12-31 NotionalDistribution ZCN.TO 1000 250.00