Problem with generic import of dividends

[robd]

Options House doesn't have an OFX server nor do they export in OFX format. The only export is a csv file. I have been working on that file and the generic format string to get things to work. So far the stocks, options, and most cash transactions seem to be working. However, I am having trouble with the dividend transactions. Either they end up in a new investment or they don't get imported at all. What am I doing wrong? Below is the format string I am using and a stock line and a dividend line.

ED,TRAN,NAME,MEMO,SYMB,BNUM,ANUM,CNUM,XX,ITYPE,CUSIP
20120323,BUY,"PAYCHEX, INC.",,PAYX,100,31.75,0,3175,Stock,704326107
20120515,DIST_D,,Cash Dividend,PAYX,32,0,0,32,Dividend,704326107

I've tried many different ways of changing data in the dividend line. With the one above, nothing is imported. If I enter the same text for the name as is in the name of the stock, it creates another investment and puts the dividend there. What do I need to do to get the dividends imported into the corresponding stock investment?

Thanks,
Rob

[Mark]

Hi Rob,

There are several problems with this sample import.

1) You have a comma in the name on the BUY line. You have the name in double quotes, but that doesn't matter, unless you add double quotes into the format string. So, remove the comma from the name, or add double quotes to the format string. If you choose to add then double quotes to the format string then you would need the double quotes on every line. If you just remove the comma, the double quotes will be considered part of the name, so you may want to remove those double quotes as well.

2) Your DIST_D has the value in the BNUM field. DIST_D requires the value be in the ANUM field.

3) On your DIST_D line you have "Dividend" as ITYPE. This is not a valid investment type. ITYPE stands for the investment type (not transaction type) for newly created investments. So, it has to be one of the investment types shown in the online help, like Stock or No-Load Mutual Fund. This error will not prevent the import, but could prevent your new investment from getting assigned to the proper investment type.

4) You should put the name on the DIST_D line too. This will not prevent the import either, but depending on the order of your data you may end up with an investment with an empty name instead of your desired name.

Here is a fixed up example:

Use the format string:
ED,TRAN,NAME,MEMO,SYMB,BNUM,ANUM,CNUM,XX,ITYPE,CUSIP

and your data lines should look like:
20120323,BUY,PAYCHEX INC.,,PAYX,100,31.75,0,3175,Stock,704326107
20120515,DIST_D,PAYCHEX INC.,Cash Dividend,PAYX,0,32,0,32,Stock,704326107

[robd]

Mark,

Thank you, that works well.

I am still having trouble with a couple of cash transactions, so I am eager to find out what I am doing wrong with them. I tried to use the same format that comes from the export for cash transactions from my Fidelity subportfolio. Since the transactions for Fidelity were downloaded directly by Fund Manager, I thought that would work. The only place cash transactions appear in the Fidelity subportfolio is in the default cash account. Fund Manager creates the name of the default cash account as "Cash - Fidelity". When I attempt to import the transactions below into my Options House subportfolio, they end up in an investment called "Cash" rather than in the default cash account created by Fund Manager called "Cash - Options House". So what am I doing wrong here?

ED,TRAN,NAME,MEMO,SYMB,BNUM,ANUM,CNUM,XX,ITYPE,CUSIP
20120227,BUY,Cash,100 ACAT REFUND,Cash,100,1,0,100,Cash,CashCUSIP
20121101,DIST_D,Cash,Money Market Dividends Paid,Cash,0,.3,0,.3,Cash,CashCUSIP

Thanks,
Rob

[Mark]

Hi Rob,

When you import transactions using the "Generic" import, incoming transactions are matched up with existing investments based on a matching symbol. So, if you have any investment with the symbol CASH, and you import transactions with the symbol of CASH, then they will go in there, independent of what investment is assigned as the default cash account.

[robd]

Maybe I'm not understanding. I am creating a new subportfolio. Then I use the import generic transactions menu option to import this file. When it doesn't work right, I just close the subportfolio and create it again and try the import again once I fix the data. Doing this it looks like I have been able to get all transactions to import correctly except these two types of cash transactions.

The action of importing the transactions ends up creating both a default cash account investment called "Cash - Options House" and another investment called "Cash". Am I supposed to end up with an investment called "Cash"? When I imported my Fidelity and TD Ameritrade transactions directly from each brokerage. I did not end up with an investment in them called "Cash". In both of them I ended up with a default cash investment called "Cash - Fidelity" and "Cash - TD Ameritrade" respectively.

I was thinking my Options House portfolio would look the same once I properly imported it's file using the generic import option. Is that possible or will I end up with both the "Cash - Options House" and "Cash" accounts because I have to use the generic import?

Thanks,
Rob

[Mark]

Hi Rob,

When you did the import into an empty sub-portfolio you should have been prompted for what to use for a default cash account. What did you choose? If you choose the 3rd option you can select from a list of investments coming in during this import. You can then choose the "Cash" investment, so you'll just end up with "Cash", and not also "Cash - Options House". You would get the latter if you chose to create a new investment for the cash account, instead of telling it that one of the importing investments is your cash account.

[robd]

That fixes it. I was using the first option. When I use the third option everything goes into one cash account called "Cash".

Thanks,
Rob

[robd]

I've had something else come up with my import of dividends. Here are two types.

ED,TRAN,NAME,MEMO,SYMB,BNUM,ANUM,CNUM,XX,ITYPE,CUSIP
20121115,DIST_D,PAYCHEX INC.,Cash Dividend,PAYX,0,33,0,33,Stock,704326107
20121101,DIST_D,Cash,Money Market Dividends Paid,Cash,0,.3,0,.3,Cash,

These are the two corresponding lines that show on the Data Register for Cash.

Code: Select all

Date      Price  Shares  Total   Fee    Type       Memo
11/15/12  1.000  33.000  33.00   0.00   Purchase   Distributed Dividend
11/1/12                  0.30           Dividend   Money Market Dividends Paid


The second dividend has no price or shares. This causes it not to be included in the Market Value at the bottom of the Data Register for the Cash account.

Do I have something wrong in one of the two import lines above?

Thanks,
Rob

[Mark]

Hi Rob,

You want to record this as a reinvested distribution for the cash case. The cash paid a distribution, and then it was reinvested back into cash. The first one is right because the PAYX paid a dividend, and then the money gets automatically deposited into your default cash account. However transactions in the default cash account itself aren't automatically deposited into itself, they need to be specified that way if they were reinvested. You would change that line to:

20121101,REINV_D,Cash,Money Market Dividends Paid,Cash,0.3,1.0,0,.3,Cash,

In the case of any REINV_* the price is in ANUM, shares in BNUM, and commission in CNUM (just like a purchase)