Wdding, could you please confirm that all symbols in quotes.csv file are defined in Excel symbol table? Also, if you have Excel 2016, during refresh you will see Excel not responding message that will go away after 30-40sec
Hi VidasMatelis, There were some mismatch of symbols between quotes.csv and Excel symbol table. I made the correction and cleaned up quotes.csv and dividends.csv. Excel still crashed if I added more transactions, sometimes. just around 20 lines. But it works if I add less lines. I'm adding a small chunk each time for now, currently to 371 transactions.
Thank you for sending me your files. I can see that Symbols are mostly OK, just in Quotes.csv you still have quotes for symbol WN and WN.TO, but in Symbol table you have record just for WN.TO. But I do not think that is the big problem. From the data problem I can see one more possible issue - in Transactions table at some point your Cash Balance goes to negative and that can make Profit % calculations go wrong - so please check that. I refreshed your workbook on my PC without error, but it was extremely slow. Like - unreasonably slow, we are talking like 10+ minutes. I looked into this and it appears you have many years of data and most importantly you are using "generated dividends" functionality that with larger dividend file has a significant impact on performance, much more than I expected. I cannot improve that speed now, but I will look into this for future releases. During refresh, I observed that at some point Excel memory usage jumped to 2.8GB. That tells me that you would not be able to use Portfolio Slicer on 32bit Excel. Could you please check if your Excel is 32bit or 64bit? If you are using Excel 32bit, you will need to look into upgrading it into 64bit. At this point I would strongly strongly (!!!) suggest you consider using Portfolio Slicer for PowerBI Desktop (64bit). You would still enter all data in the same Excel file, but then save it and would refresh Power BI Desktop model to see results and that refresh would be many times faster. Then, from time to time, you would still refresh Excel model too.
I am using 2.4.04 and I notice that when I enter DivTA transactions, column F (qty) is flagged with data validation errors if I leave the cell blank. I thought the whole point of DivTA was to ignore the share quantity and just enter the final price. I don't remember having this issue in v.2.3.
Needless to say, the error goes away if I enter the quantity in the transaction, but you know that's not my point.
Hi Vidas, Thanks for the detailed analysis! I upgraded to 64bit Excel, it's no longer crashing. It seems that 64bit Excel helps. I also installed PowerBI Desktop, it's a much better interface. Though not sure if it requires additional license purchase.
Qosmioamit - I do not see that behavior. Somehow your validation rules were changed from what was released. Could you please select any cell inside srcTrans table and the choose "Home"->"Conditional Formatting"->"Manage Rules". You should see 11 rules and 2 of them applies to columns F. But none of these 2 rules on column F should have "Red background" (that is error) formatting. Could you please check your formatting rules.
wdding - PowerBI is free tool - does not require license to be purchased. But there is an option to buy PowerBI mobile license that allows you to schedule refresh and see reports either on the phone or delivered to your mailbox.
Vidas, The conditional formatting rules are as you said, but I don't get red background errors. I get a data validation error, which is a green flag on the cell. To make it easier, I am attaching a file for you. This file is the default v2.4.04 from the Download section of this site. I have added another transaction with DivTA where I have not entered the qty and you can see the green flag with data validation error.
Thank you. I'll review that rule. For now to clear it: Select all cells in that column except F1 (Header), that is F2:F6 in your case, then choose "Data"->"Data Validation"->change "Allow" Custom" to "Allow" "Any Value".
Comments
There were some mismatch of symbols between quotes.csv and Excel symbol table. I made the correction and cleaned up quotes.csv and dividends.csv. Excel still crashed if I added more transactions, sometimes. just around 20 lines. But it works if I add less lines. I'm adding a small chunk each time for now, currently to 371 transactions.
Thank you for sending me your files. I can see that Symbols are mostly OK, just in Quotes.csv you still have quotes for symbol WN and WN.TO, but in Symbol table you have record just for WN.TO. But I do not think that is the big problem. From the data problem I can see one more possible issue - in Transactions table at some point your Cash Balance goes to negative and that can make Profit % calculations go wrong - so please check that.
I refreshed your workbook on my PC without error, but it was extremely slow. Like - unreasonably slow, we are talking like 10+ minutes. I looked into this and it appears you have many years of data and most importantly you are using "generated dividends" functionality that with larger dividend file has a significant impact on performance, much more than I expected. I cannot improve that speed now, but I will look into this for future releases.
During refresh, I observed that at some point Excel memory usage jumped to 2.8GB. That tells me that you would not be able to use Portfolio Slicer on 32bit Excel. Could you please check if your Excel is 32bit or 64bit? If you are using Excel 32bit, you will need to look into upgrading it into 64bit.
At this point I would strongly strongly (!!!) suggest you consider using Portfolio Slicer for PowerBI Desktop (64bit). You would still enter all data in the same Excel file, but then save it and would refresh Power BI Desktop model to see results and that refresh would be many times faster. Then, from time to time, you would still refresh Excel model too.
I am using 2.4.04 and I notice that when I enter DivTA transactions, column F (qty) is flagged with data validation errors if I leave the cell blank. I thought the whole point of DivTA was to ignore the share quantity and just enter the final price. I don't remember having this issue in v.2.3.
Needless to say, the error goes away if I enter the quantity in the transaction, but you know that's not my point.
The conditional formatting rules are as you said, but I don't get red background errors. I get a data validation error, which is a green flag on the cell. To make it easier, I am attaching a file for you. This file is the default v2.4.04 from the Download section of this site. I have added another transaction with DivTA where I have not entered the qty and you can see the green flag with data validation error.
Thank you. I'll review that rule. For now to clear it:
Select all cells in that column except F1 (Header), that is F2:F6 in your case, then choose "Data"->"Data Validation"->change "Allow" Custom" to "Allow" "Any Value".