The query did not run or the Data Model could not be accessed ...

RalphE

Hello Vidas,

I hope this finds you all right. I am doing my weekly PS routing and run into the following issue when updating the portfolio.

"The query did not run or the Data Model could not be accessed. Here's the error message we got:
The query referenced calculated column 'Quotes'[Price2] which does not hold any data becasue evaluation of one of the rows caused an error."

"The query did not run or the Data Model could not be accessed. Here's the error message we got:
The query referenced calculated column 'Trans'[ExchRate2] which does not hold any data becasue evaluation of one of the rows caused an error."

I am trying to get my head around what is causing these errors. Typically I am updating the portfolio at the end of the week with Friday's closing prices. I have had a couple of sells (in FX) recently, which happened on days other than Fridays. What I do in such cases, is making sure that the exchange rate files include the FX valid for the sell; this FX could be different from the closing FX of the day. Additionally, in the case of the sell transaction, the quote file will get the price at which the sell happened.

I realized that there is one transaction (March 10) where for one the FX was 1.1394 and for the other 1.1364 ... I assume PS can work only with one FX per day. But I am not sure whether this is causing the above errors.

Thank you for your help and stay healthy.
Rgds.
Ralph

VidasMatelis

First please check quotes.csv and CurrencyConv.csv files and make sure they look OK and there are no strage records. Also make sure that folder PSData does not have error.txt file.

Then I would try the following:
1. Make a copy of your workbook and the next steps do on the copy version of your workbook.
2. Remove the last few transactions from your workbook and try to refresh it. If works, you need to investigate these few transactions - add them one by one back and refresh, until you find transaction with the issue.
3. If above does not work - start removing blocks of transactions - best to remove them by account. As your error is about Price2, then account with Currency2 should be kept and others could be deleted.

When you narrow down your error, but cannot figure out actual issue, you can send me your workbook and all files from PSData folder - I'll investigate. My email is posted on "Contact US" page.

RalphE

Hi Vidas,

Thank you for getting back so quickly. I was able to enter the new transactions one by one and it worked. The Dashboard calculates the value correctly.

However, the other spreadsheets Daily, Weekly ... are basically empty. They list the symbols and the months, but no values whatsoever. TransInfo and Sales show only the sold quantities, but no values. The only values are shown in Daily with the bars of the Daily Currency Exchange Rate.

Any ideas?
Thank you

VidasMatelis

Can you please clarify:
- What is your Excel version
- Do you have CORRECT values in Holdings tab?
- Is it just Daily values are incorrect? How about other tabs (like Yearly) - do they show correct value?

RalphE

Hi Vidas,

My Excel version is the most current one in Office 365.

Daily, Monthly, Yearly and Holdings do not show any value. Only the stocks are listed and the dates, but no values.

Thank you for your help.
Ralph

VidasMatelis

Ralph, could you send me your workbook and all files from PSData folder. My email is posted on this page : http://www.portfolioslicer.com/contact.html

RalphE

Hi Vidas,

I just wanted to give you a feedback. Not sure what had caused this issue, but I was able to restore all the data with a previous backup PS file. So all is ok again ... reminds of backups in principal.

Thank you for your assistance and stay well.

Rgds., Rlaph