Error refreshing table AttributeID = TransID doesn't exist

jrwsteelers

Hello,
I'm new to Portfolio Slicer and I've been working on entering all of my old transactions from 2006 up until today. I've been copying and pasting transaction information from another spreadsheet that I set up to reformat the data I downloaded from my brokerage account. Everything was going fine until I entered the data from 2013. I'd been copying data over a year or two at a time and reviewing it as I went along. I hadn't run into any issues except for the warnings about pivot tables overlapping until I tried updating the 2013 data. When I try refreshing the "TransInfo" sheet I get a "Attribute ID = TransID doesn't exist" error. If I try updating the Trans Table directly in PowerPivot I get an error that says "Errors trying to update one or more linked tables". I've also tried to correct the pivot tables overlapping error, but making sure all of the filter were set to "Yes", but I can't seem to get rid of it.

I have downloaded another copy of Portfolio Slicer from the website in case my version was corrupted. I also reviewed my separate transaction spreadsheet to ensure the formatting and formulas were consistent from 2012 to 2013 (everything looks good). I tried with a newly downloaded version of the spreadsheet and still get the same error.

I tested to see if I could copy over one month of 2013 data and try updating (still failed). I then tried copying over 1 transaction for 2013 and that failed as well. The strange thing is I'm able to manually input the same transactions and not receive an error when I update the tables (I still get the pivot tables overlapping error, even in the newly downloaded spreadsheet where I know I didn't adjust the tables). Nothing appears to be different and it will be very time consuming for me to not be able to copy and paste my transaction data from 2013 until now.

Does anyone know what might be causing PowerPivot to not like the copy and pasted data but seem to have a problem with the same manually entered data? The copy and paste process was working fine for me for my 2006 thru 2012 data. If anyone has any ideas where I could try to look for the problem I would appreciate it.

Thanks in advance,
Joe

VidasMatelis

Hi Joe,

First, please make sure that you did not delete the first transaction record in your source Trans table. This error you are reporting would happen when you override first record OR it can happen when first "System" transaction record does not have the earliest date in relationship to other transactions. Maybe you set it up to 2014-01-01 and now when you entering data for 2013, you did not make it as the first date in your transaction list.
When PowerPivot loads data into model it uses first x records (could be 8, could be more) to decide what type of data (text, whole number, decimal number) is in each column. Default value is text. So lets say your first x records have no values in Fee column, so PowerPivot model decides that Fee filed will be text type. But then later in calculations I will expect decimal number type of field, so you will get error that is similar to what you are getting.

If that is not the case for you, can you please send me your workbook - I will investigate. You can just include few transactions, but make sure that with your transactions you can reproduce problem. I'll investigate and report back here in the forum. My email is posted on this page: http://www.portfolioslicer.com/contact.html

jrwsteelers

The first record is still 2005-12-31 and it works fine until I try copying in data from 2013. It does allow me to manually enter the same data from 2013 without error. I entered zeros for the fee column when there wasn't an actual fee for the specific transaction, so I think I'm okay there. I didn't have anything entered into the cost basis override column for the first hundred lines or so, but then I had plenty of numerical values in that column later on without having issues refreshing. I don't have access to Excel 2010 this evening, so I'll have to send you the example tomorrow.

Thanks for the help,
Joe

VidasMatelis

There is the possibility that for some numeric fields you setting the value to an empty string (space) - please check that. These type of errors are usually hard to find. That is why when you enter data manually - you do not get any error, but when you copy data, you do not see that you just copied space symbol into the numeric column.

jrwsteelers

I am trying to recreate the error this morning but it seems to be working fine now. When I tried to save my separate transaction spreadsheet yesterday it wouldn't allow me to save a current copy of it. Right now, I'm thinking that separate spreadsheet got corrupted yesterday right when I was trying to copy and paste new data from 2013 to Portfolio Slicer. When I reopened an earlier saved version of my transaction spreadsheet this morning, I was able to copy and paste data without an issue refreshing.

Thanks again for your help,
Joe

jrwsteelers

I did some additional testing on this and found what was causing my spreadsheet to get corrupted and give portfolio slicer problems. In my transaction spreadsheet I added a formula to set the cost basis override value to 0.001 for certain transactions for all other transactions the value was set to "" in my excel workbook. If I exclude that column of data from my copy and paste( (values only) portfolio slicer updates. I have other numerical values in the cost basis override column early but it doesn't seem to like the format of my transaction spreadsheet cost override column. I'm only pasting the values but it still causes problems.

It will be easy for me to go back and enter the cost override on these transactions, so this isn't a big issue now.

Thanks,
Joe

stephenc

Hello

I'm getting a similar problem. I'm getting an error message "The 'AttributeRelationship' with 'AttributeID' = 'Allocation' doesn't exist in the collection" when I run an update. I.e. when updating data it gets part way, then the error message appears and the update is only partially complete.

I've tried looking for extra rows or columns, or spaces in numberical cells, in the sheets where I can see "Allocation" references, i.e. 'src', 'srcSymbol'. I've also checked that the allocation names (e.g. "US Shares", "Reits", "Other Developed") used in those two sheets are identical.

I don't know if its relevant to what Joe mentioned, but as I have some UK shares listed in pence I do have some '0.01' adjustments in the PSConfig file to bring it back to pounds, e.g.

BARC.L,,,,Y,0.01,0.01,0.01
BP.L,,,,Y,0.01,0.01,0.01
LGEN.L,,,,Y,0.01,0.01,0.01


Any ideas on next steps, as I'm stumped? Any help is very much appreciated!

VidasMatelis

stephenc, can you please send me your workbook and PSData folder files? This way it would be much easier for me to investigate. My email is posted on this website: http://www.portfolioslicer.com/contact.html

stephenc

Thanks Vidas, just sent!

VidasMatelis

Hi stephenc,

Thanks for sending me your workbook. There was no way I could figure out your issue without looking at the workbook.
When I tried to refresh I got error message:

We couldn't get data from the data model. Here's the error message we got:
Cannot create relationship from the 'Symbol' table to the 'Allocation' table because the 'Allocation' column in the 'Allocation' table has duplicate values.

I looked at allocation table and noticed that for TargetPercent you used decimal numbers. At this point you cannot do that - for Allocation.TargetPercent you must use whole numbers. As soon as I fixed that - I was able to refresh your workbook. This is clearly my documentation issue - I need to better described values that are acceptable for this column - sorry about that.

Let me know if you have any other issues.

stephenc

Thanks Vidas! That worked! Really appreciate the help.

SiGmA_X

This thread helped me get things up and running! I was fine with the first portfolio I entered - a very simple 6mo old test portfolio.

When I started to add my real portfolios, I ran into issues first with non-listed mutual funds, which I resolved thanks for another thread.

Next, I ran into the AttributeID issues which was related to copy/pasting blank data into the Fee column. I had cleared cells in that column minus the actual trade fees, but it didn't fix it. Setting all of the blank cells to zero fixed it up. Now things refresh pretty quickly too, with 1320 trades and 40k quotes (need to set min/max dates on many of the funds...)

Great tool and forum posts, thanks Vidas!