error message function `LN' has the wrong data type ....

jagmanvdp

Good day

I am moving all my transactions etc from version 2.3 to version 2.4. For the most part it seems like most of the data is being accepted and most of the results make sense. (i have a few problems, but we'll start with this one please) When i hit refresh all, the process takes a long time and then i get an error that states.

MdxScript (Model) (7,40) Calculation error in measure Report (Profit%)
An argument of function LN has the wrong data type or the result is too large or too small.

Where should I start to look for the source of this error?

Thanks

Darren

VidasMatelis

Hi Darren,

Technical explanation - function LN is used because in Excel 2010 PowerPivot does not have function PRODUCT, so this function is converted to LN + EXP functions. In Excel 2016 PowerPivot introduced PRODUCT function, so this specific issue should not be in Excel 2016.

Usually, this indicates that your calculations for Profit % cannot be "reasonably" made. This happens mostly when your data does not make sense.
Examples:
1. Your cash balance at any day is below 0. This is not supported. This can happen if you buy on Jan 1st, but deposit cash on Jan 2nd. So for transaction on Jan 1st we cannot calculate Profit % as starting point is negative. To fix - make sure that at then end of any day cash balance is equal or above 0. You can see CashBalance in Trans table - there is calculated column for that. Note - it is possible that cash balance value is negative when you have multiple transactions on the same day, but at the end of the day cash balance must be >=0.
2. You have gaps in data, for example missing quotes or exchange rates for some months.

jagmanvdp

Thank you
I am using excel 2013
I am not tracking cash

Upon reviewing the files in psdata/quotes I see that the quotes tend to end around september 9, 2019 even though it is currently january 2020. The quotes created by the scripts are complete and up to date. What could be causing the quote sin psdata\quotes to end months ago....

Thanks again for your help

VidasMatelis

Hi,

Excel 2010 and 2013 use the same code. Excel 2016 has updated code.

Regarding quotes - first please make sure that you are using latest scripts - last update was in Aug 2019.
Then please check log file for Scripts\Log\GetQuotes-Yahoo.txt to see what error is reported. Common issue is when IE is updated, you have to start it at least once and go through configuration prompts, otherwise scripts will not work.

jagmanvdp

Hello

I was getting many errors when i switched from 2.3 to 2.4 so I decided to just put in three transactions. They were all buys of TD bank, 100 shares each time in three different years. No other transactions. I had all my symbols for all my various investment vehicles in the SRC symbols tab and all the various accounts listed in the SRC tab.

When i run that (refresh all) I get the LN profit % is too big or too small error. I could not understand how this would happen with only three simple transactions.

just trying to understand what portfolio slicer is doing from a technical point of view before i go further in setting up version 2.4 Is portfolio sliicer doing calculations for all the symbols and all the quotes even if there are no transactions? Is it looking at the quote files for items for which there are no transactions and finding errors in those files, even though it is not using that data?

just trying to narrow down how this is happening so i can rebuild the portfolio ina way to figure out where the problem lies.

Thanks

VidasMatelis

One option is for you to remove measures from Excel Pivot tables: Profit % and Cap Gain %. You can add them later after you are happy with other reports.
Regarding this specific error, can you send me your workbook with these few transactions and all files from PSData folder - I will investigate. My email is posted on "Contact" page.

VidasMatelis

Hello, I sent you a private email with the specific issue, but basically you have no quotes for one symbol from 2019-10-31, because that symbol quotes have slight change in symbol - instead of : you now have -.

jagmanvdp

Thank you very much!! That is incredibly helpful.
I cannot count the number of times i reviewed that file and missed that error.

Thank you for your time with this.