Error with v2.4

botschild

Hi Vidas,

I am moving from 2.3 to 2.4 by pasting in my transactions and other data. When I tried to update the model, I received the following error:

"Excel couldn't refresh the table 'CurrencyConv' from connection 'CurrencyConv'. Here's the error message from the external data source:

The operation failed because the source does not contain the requested column. You can fix this problem by updating the column mappings.

More details:
The 'CurrencyFrom' column does not exist in the rowset.
An error occurred while processing the table 'CurrencyConv'.
The current operations was cancelled because another operation in the transaction failed."

Any ideas? The same data works in 2.3.

Thank you.

VidasMatelis

1 - Make sure you are using latest scripts
2 - Check psConfig.txt file. Make sure that ColumnSeparator section use Tab as value. Please also check PSDataFolder section to confirm where PSData files will be placed.
3 - Make sure that in previous version you did not specify PSData folder location to be different!
4 - is there error.txt file in psdata folder?
5 - check c:\PortfolioSlicer\PSData\CurrencyConv.csv file, first line should be like this (with tabs between column names):
Date ExchRate CurrencyFrom CurrencyTo

Error means that step 5 in previous list finds different than expected first line.

xcerik

Hi Vidas,

I am trying the new version 2.4.05. I copied Accounts, Allocation, Symbol, SymbolSector & Trans from 2.3, and added the new index configuration as required.

When I refresh, I get this error (twice):
MdxScript(Model) (7,40) Calculation error in measure 'Report'[Profit %]: An argument for function 'LN' has the wrong type or the result is too large or too small.

Also, the refresh now takes much longer, over 0.5 hours - it was 2-3min for my data in v2.3. It seems somewhat CPU-bound, while saying "Reading Data..", it has not much disk activity, but steady CPU usage.

Thanks,
Erik

VidasMatelis

I am assuming you are using Excel 2010. This error indicates that in some months calculations for Profit % are so wrong, so results makes formula to crash. THis happens when you have negative cash balance or you are missing quotes or exchange rates.
1. If you are tracking case, review Transactions table and make sure that cash balance values are NOT negative at the end of the day. Note - there could be negative intermediate day cash balance values, but not end of day.
2. Check c:\PortfolioSlicer\PSData\CurrencyConv.csv file and make sure you have exchange rates from the MinDate up to now.
3. Check if you have Quotes for all symbols.

If you cannot figure out yourself, you can send me your workbook and ALL files from c:\PortfolioSlicer\PSData folder.

In PowerPivot for Excel when data breaks referential integrity (for examle, you have quotes without symbol), that Portfolio Slicer starts to work much slower. But 0.5h - I never heard of that. But lets first fix data issues, the we will look into performance.

xcerik

I searched the forums and found that the error I saw is often related to missing data for a symbol. I added VT, VEA and VWO to psConfig.txt and now I receive just one alert.

Do I need an index to track the Cash allocation?

My Yearly tab has not updated at all - is that caused by the error? It shows the results for the sample data, as if it hasn't seen my data.

I hope this makes sense, and I appreciate your patience!

VidasMatelis

You do not need index to track cash.
Your results are not updated because of the error.
Still something is missing - check quotes, currency exchange and cash balance.

xcerik

Hi Vidas,

Thanks for your quick reply. I am using Excel 2013 (32 bit). During some refreshes as I try to figure things out, I get memory errors, recommending to switch to 64 bit version.

I am tracking cash, and have many negative end-of-day cash balances on the Trans tab (sorted by Account, Date). This is due to dividend income making up the difference. I don't think it's possible to always have positive cash balances, without entering the dividends into the Trans tab.

My CurrencyConv.csv has data back to Min-date. Is this first line normal though? I'm not sure where it comes from, it is not in the files in CurrencyConv folder. Deleting it did not solve the refresh error.
Date ExchRate CurrencyFrom CurrencyTo
2014-12-31 1.0000000001 N/A N/A

Figuring out missing quotes is a challenge with 80+ symbols. Is there a way to narrow it down?

VidasMatelis

CurrencyConv first first 2 lines are correct - they are there added by scripts.
This is how I test issue that you described for other users:
1. I get Excel workbook from users and make a copy. I do all research on the copy and then fix original workbook, and if there are issues, then make copy again and work on the copy.
2. I delete all tabs, EXCEPT: src, srcSymbol, srcTrans, Mthly-Symbol.
3. In Mthly-Symbol tab I select slicer SYmbol value "* Cash".
4. Try to refresh workbook. If you have files in PSData folder, then do full refresh. But you can do also selective refresh of just Trans table: http://portfolioslicer.com/docs/excel2013-refresh-data.html
5. From Pivot table remove columns Profit % and Cap Gain %.
6. Now for slicer SYmbol remove filter - so that report would be for all symbols

This way you now have report for monthly star,end, Cap Gain values. Review report - look for months where data does not make sense - for example large discrepency between start value and end value, large or large negative capital gain - that is indication that in these months something happens.
For example, if you do not have qutes for one month, EndValue for that months will be 0 and Capital Gain for next month will be equal to recovered value. When you narrow down to months with issues, start clicking on symbol filter and identify symbol that cause these anomalies. When symbol found, then review quotes and transactions tables.

xcerik

Thanks for the detailed instructions. I worked my through each symbol, and found many that I no longer own were missing from the Quotes folder. Some I had to lookup and add files for, since Yahoo could not fetch the old data. The transactions were fine. I finally got a refresh with no errors, and it took a lot less time. As always, great programming, great support, so thanks again!

lgross

@VidasMatelis - I am running into the same error message, and I cannot figure it out. I had been using v2.3 and was upgrading to v2.4 (XL13). After many attempts at reducing the data I had but getting the same error - I went back to a minimal psConfig.txt and workbook, and still hit the error.

The psConfig.txt is unchanged from the download. The workbook was aligned with that config by deleting all symbols from `src`, `srcSymbol` and `srcTrans` not matching the two indexes in the config. And I still get the error.

Where should I go next?

VidasMatelis

Hi Igross, can you send me that minimal workbook and all files from PSData folder. I'll look into this.
My email is posted on this page: http://www.portfolioslicer.com/contact.html

VidasMatelis

Hi Igross,

Thanks for sending me your files. I refreshed them without any errors. For the file you send me - I went to Data->Connections->srcTrans->Refresh and it worked without problem and brought current data up to 2018-07-02 as quotes inside xlsx file are loaded up to that date.
As you are getting this error, can you please send me the screenshot and tell me how long after you hit refresh you have to wait for error to appear?

lgross

@VidasMatelis - I have just been hitting the Data "Refresh All" button. I went into the "Connections" and do not see a "srcTrans" to refresh.

And now I am confused. After it failing many times - when I just tried it again to be able to time it -- it completed successfully!

Now to try adding data again.

VidasMatelis

Third from the bottom, that ends with xlsx!Trans is what I refered to srcTrans - I named worksheet name, not internal worksheet table reference - sorry about the confusion I caused.
When you see strange behaviour as you saw, try restarting your machine and then:
1. Add all symbols into symbol table and create all quotes and then just have 2 transactions: First "System" transaction record and second deposit record. Try refreshing - if it works - that is great fundation for you to build on.
2. As you migrating from old version, in srcTrans worksheet order transactions by Symbol and try adding all transactions related to one symbol and then refresh.

lgross

We think alike @VidasMatelis! - those two steps are essentially what I have started doing - except I started with adding all my accounts first. Just have not had much time in the last couple of days.

Here is my plan (reworked to add ALL symbols first):
1. Add all accounts (update srcTrans to refer to one of the accounts) - done and refresh works
2. Add 2 symbols to psConfig and workbook, updatePsData, refresh - done
3. Add remaining symbols to psConfig and workbook, updatePsData, refresh - in progress
4. Add symbols to psConfig under GoogleWeb and to workbook - refresh

5. Add all transactions for one symbol (from ) to srcTrans - refresh
6. Add all transactions for a few more symbols at a time and refresh

... and just keep adding bits and pieces until I can narrow down what (if anything now) is causing me issues.

lgross

hmm - none of my quotes in <GoogleWeb> are found anymore! I know they used to work! I wonder if this was part of my problem...

proceeding with just my quotes.