Importing Dividend Distributions

[Steven_M]

Hello,
I am having difficulty importing dividend distributions. Here is a simple example.

#Date Symbol Action Quantity Price Comm
2013-12-27 XFN.TO TXIN 10 28.353 0
2014-01-31 XFN.TO DIST_D 1 1 0
2014-02-28 XFN.TO DIST_D 1 1 0
2014-03-31 XFN.TO DIST_D 1 1 0
2014-08-22 XFN.TO SELL 10 32.44 9.95

I am saying for the sake of simplicity the total value of each distribution is $1.00. The capital gains should be $40.90. The total distributions should be $3.00.
But when I import this file into FM using the string
YY-MM-DD,SYMB,TRAN,BNUM,ANUM,CNUM

FM calculates the total added in the cash account as $10,068.53.
I am definitely doing something wrong.

Thank you for your help,
Steven

[Mark]

Hi Steven,

What transactions get imported into your XFN.TO investment?

The TXIN should not generate any default cash account entry. The dividends would deposit $1 each, and the sell would deposit 10 * 32.44 - 9.95. Is your default cash account set at a fixed price of $1 for all the closing prices?

I don't see any commas in your importing data, yet you have commas in your format string?

Did you open up your importing file in a text editor, like Notepad, so you can see the raw data, instead of looking at it in Excel?

The TXIN has 0 for tax cost basis, so your realized gain will be (10 * 32.44 - 9.95) - 0, or $314.45. If you want to enter a tax cost basis for a TXIN you have to use the CNUM field. See the online help for an explanation. The ANUM field for a TXIN is importing the market price, not your cost basis price.

[Steven_M]

Hi Mark,
All 5 transactions get imported. The TXIN does not generate a cash entry.

This is what the Cash account looks like:
Date Price Shares Total Fee Type Memo
8/22/14 1.000 314 314.45 0.00 Purchase (Imported) Sold 10 (XFN.TO) XFN.TO
3/31/14 0.000 3,251 1.00 0.00 Purchase (Imported) Distributed Dividend from (XFN.TO) XFN.TO
2/28/14 0.000 3,251 1.00 0.00 Purchase (Imported) Distributed Dividend from (XFN.TO) XFN.TO
1/31/14 0.000 3,251 1.00 0.00 Purchase (Imported) Distributed Dividend from (XFN.TO) XFN.TO

This is what the importing data looks like in Notepad.
#Date,Symbol,Action,Quantity,Price,Comm
2013-12-27,XFN.TO,TXIN,10,28.353,0
2014-01-31,XFN.TO,DIST_D,1,1,0
2014-02-28,XFN.TO,DIST_D,1,1,0
2014-03-31,XFN.TO,DIST_D,1,1,0
2014-08-22,XFN.TO,SELL,10,32.44,9.95

I don’t know how to set default cash account to a fixed price of $1 for all closing prices. But I think I am doing it for each transaction, as I see from the generated cash account.

My problem still is, FM calculates the total added in the cash account as $10,068.53, instead of $314.45 + $3.00.

Thank you,
Steven

[Mark]

Hi Steven,

The problem is your cash account doesn't have a fixed price of $1. All of those transactions in the cash account should have a price of $1, and the shares should match the total value. For example, instead of:

Date Price Shares Total Fee Type Memo
3/31/14 0.000 3,251 1.00 0.00 Purchase (Imported) Distributed Dividend from (XFN.TO) XFN.TO

it should be:

Date Price Shares Total Fee Type Memo
3/31/14 1.000 1 1.00 0.00 Purchase (Imported) Distributed Dividend from (XFN.TO) XFN.TO

You are getting 3,251 shares of cash, instead of 1 for a $1 dividend. This means the price for that transaction is $1/3251, instead of $1. If your cash price is later valued at $1/share, this is $3,251 instead of $1. All cash transactions should be at a price of 1, and the shares should match the value.

Fix all of this in the Data Register. Under the data type of "All Transactions", you can edit/fix your transactions. Then, change the data type (in the upper/right of the Data Register) to "Prices". These are all your recorded closing prices. They should all be $1 for cash. If any aren't $1, you can edit them here.

You may also want to check "Investment Properties... / Internet Retrieve" and make sure "Retrieve Price" is un-checked for your cash investment. If it was checked, that could have been the source of the non-$1 pricing.

[Steven_M]

Hi Mark,
Thank you for clearing that up. If I understand correctly, the method you’re suggesting is a fix I could do after the importing file has been imported.
Is there any way of preventing this problem from occurring, by using a different importing file?
Thanks,
Steven

[Mark]

Hi Steven,

The problem isn't with your importing transactions (or the file), it is a problem with your cash account, not having a price of $1 on all dates. When you enter or import a transaction that uses/generates cash, that cash goes in/out of any assigned default cash account. It will buy/sell however many shares of cash are necessary for the total amount to be correct. Normally the price is $1, so the shares bought/sold will be the same as the value. In your case, you have some incorrect prices for cash recorded, so you're getting incorrect shares purchased. Just fix the cash investment, and all your future transactions will have the correct effect on cash.

[Steven_M]

Hi Mark,
I’m sorry to be bothering you about such a simple thing. But "Investment Properties > Internet Retrieve > Retrieve Price" is un-checked, and yet I still get the same results.
Any thoughts?
Steven

[Mark]

Hi Steven,

Do you mean you imported again, and got the same results, or were you thinking that un-checking that option would fix your already recorded transactions? Un-checking that option will just prevent getting future prices recorded incorrectly. You still have to manually correct anything already imported that is wrong. You have to edit both the cash transactions and prices to fix this issue. Do all of this in the Data Register. Did you edit all your closing prices to be $1, as seen under the Data Type of "Prices"? Did you also edit all your transactions to be at a price of $1, with the shares equal to the value?

[Steven_M]

Hi Mark,
I actually deleted all the imported data. I unchecked Retrieve Price, and re-imported the data and got the same result.
Steven

[Mark]

Hi Steven,

Did you edit all your closing prices to be $1, as seen under the Data Type of "Prices"?

[Steven_M]

Hi Mark,
I must have misunderstood. I thought that with Retrieve Price unchecked, I could set the distributions with the importing file, and I would not have to readjust prices manually afterwards. I am looking for an approach where I can simply import the trades and distributions using an importing file. Is that not possible?
Thanks,
Steven

[Mark]

Hi Steven,

Yes, that is possible, but you have incorrect prices recorded in your cash account now, that have to be corrected. Un-checking that option doesn't change any of your existing data. You have to fix those wrong closing prices in your cash account, and then everything will work properly going forward. To fix the prices, open up the cash investment in the Data Register, and change the "Data Type" to "Prices". Look at all the recorded closing prices in this list. Any that do not have a $1 price in the "Close" column you need to select, and hit "Edit..." and change the price to $1. All the closing prices in your cash investment need to be $1.

[Steven_M]

Hi Mark,
I am so sorry for misinterpreting your advice. I deleted the transactions, but I didn’t delete the prices in the cash account. Once I did that everything worked as expected. Once again, my apologies for being so slow.
Thanks,
Steven

[Mark]

Hi Steven,

Excellent, glad you got it working.