Howdy, Stranger!
It looks like you're new here. If you want to get involved, click one of these buttons!
Quick Links
While refreshing excel it hangsup
Dear Vidas
New version looks great. But i got stuck while refreshing data, my excel sheet is not responding after some time.....
New version looks great. But i got stuck while refreshing data, my excel sheet is not responding after some time.....
Comments
I am using MS office excel version 2016 and getting error message as attached hereiwth after long time after hangup excel.
Error says we couldn't get data from data model. Here is error message we got:
The 'AttributeRelationship' with 'AttributeID' = with 'TranID' doesn't exist in the connection.
This error message usually indicates that Excel model is corrupted. On March 18-20th there was v2.0 released with issue that could have caused this error message (in special situation where you do not track cash), but Excel file was re-released on March 20 with fix. Did you by any chance using Excel downloaded before March 20st? If yes, then please re-download and setup again.
If no, then could we do following:
Setup Portfolio Slicer Excel file and add just extra 2-3 transactions and try to refresh. If no success, then check "Contact" page and send me workbook. If success, then add another group of transactions and refresh again. If success, then save workbook with new name and add rest of transactions.
If you still having troubles, then you can send me configuration file (psConfig.txt) and your workbook and I will investigate.
Will check for other errors and let you know if any.
One more thing i would like to share that speed of refreshing the excel sheet is bit low compared to earlier versions.... The whole process took 20 minutes as compared to earlier versions. In Earlier versions there were hardly 5 minutes.
it would be great if calculation speed could be increased if possible...
Please look in to this if possible...
I am glad it is working now.
About refresh speed - with about 100 symbols and 5000 transactions on Excel 2016 it takes me about 140sec to do full refresh. Are you saying that when you click on "Data"->"Refresh All", it takes you 20min for refresh to complete? That is way too much.
I am actually working last 3 days on trying to improve refresh speed, I have some success, but I still have some work to do.
As most of your data is already entered, you should consider doing "Fast refresh", as described here: http://portfolioslicer.com/docs/excel2013-refresh-data.html#fast-refresh. Please share if this helps you to speed up refresh and by how much.
Here are my updates to you for slower refreshing speed.
Excel refreshing took 480 sec to update only transactions table as suggested in Fast refresh
Excel refreshing took 360 sec for external data after unchecking dividends and currency conv.
These number seem very large. How many symbols do you have? How many transaction records. What type of processor does your PC have?
Symbols are 143, Transactions are 912 and My processor is Intel Core i3 with 4GB Ram.
Actually in old version 1.0.2 there was no problem for speed... it took hardly 2-3 minutes max to refreshing all the sheets...
To give you ideal - last days I was working on improving performance. Step to refresh all Excel internal tables took 47 sec, I reduced it to 30sec. Step to recalculate pivot table took 30sec, I reduced to 27sec. So I am dealing with seconds, and you reporting multi-minute refresh times, so something is not right.
I have sent you an email as per your request.
in that email I have raised another query which might be helpful to you.
I run refresh for your workbook on my PC.
Here are my results:
Refresh just one table - Trans (linked table) : 1min 55sec
Refresh using "Refresh All" as you configured: 5min 57sec
Refresh using "Refresh All" as I configured based on your usage: 3min 59sec (tables: Dates, Dividends, Quotes, Account, Symbol, SymbolSector, Trans)
I am not very happy with these results, but they are what I would expect with 2.02 release. I am working on improving them and will do another release within weeks - most likely then this would be faster. But my tests shows no huge issue with refresh, again I will address other issues in the next release.
In the meantime, could you please do one test:
1. Get baseline results - start Portfolio Slicer Excel workbook, go to Data, connections, select "Trans" table (connection name will be WorksheetConnection_PortfolioSlicerV2.0-XL13.xlsx!Trans and hit "Refresh". Write down how long refresh took.
2. Go to "Control Panel"->"Power Options", remember what plan your PC is at now, then choose "Show additional plans" at the bottom and then choose "High performance".
3. Perform "Trans" table refresh again, record results and see if there is any difference.
If you current plan is "Balanced" and you are on "Windows 7", then changing power plan most certainly have significant impact. But if you are Windows 8 or 10, then it is unlikely going to help you, but still if you could do test.
After test you can restore your Power Plan option back.
As per your suggestion refreshing "Trans" Table before choose "High performance" took 480 Sec. and after "High performance" took 470 sec.
I am using windows 10.
I send you by email zip file with 2 Excel workbooks. Could you please test for speed (Refresh "Trans" table and "Refresh All") and post results here?
These files contains your data but new code for version v2.1.
These Excel workbooks have Excel 2016 specific code.
New version has some improvements in refresh speed, so I need to see if they are visible on your PC.
My tests show that slowest performance is when in Portfolio Slicer tracking of cash is enabled and at the same time "Generated Dividends" functionality is used. That is the case with your workbook.
That is why I send you one workbook just with code change (v2.1) and data as you send me and another workbook with setting to track cash as no.
Thanks,
Amazing speed...
Results
01. With Cash transaction sheet "refresh all" speed now decreased to 220 sec
02. No Cash transaction sheet "refresh all" speed now decreased to 105 sec
Three Cheers....
Now only one problem was left for cost basis as it was not there is older version.