Merrill Lynch not able to retrieve transactions

[surendar jeyadev]

.....

Also there are just strange data anomalies. Like, I've always wondered why a $10,000 bond shows up as 100 shares at $100. Why is not 10@$1,000 or 10,000@$1 ? (Same at Fidelity, by the way.) In any case, if you've got bonds in there and hope to reconcile (EDIT: from the POSITION file), you need to divide the "quantity" (really face value) by 100 in order to conform with what is in Fund Manager. (Does this foretell changes coming? Who knows?)

Yes, I noticed this even with QFX with some CDs bought via Fidelity. The CDs can only be bought in units of 1000s of dollars, but appear on the statements as though they were $ 100 each.

[surendar jeyadev]

Hello,

We've updated the instructions for importing from Merrill since they removed the QFX download option. Please see the updated instructions here.

These new instructions include a "helper spreadsheet" which maps the download Merrill activity CSV file into one you can import into Fund Manager with the generic transaction import command. Please let me know if you have any questions or ideas for improving this.

Thank you very much for this. I will give this a try. Luckily I have just the pesky ML Deposit .... thing as my cash account. But, I can fix that by hand.

Appreciate you putting out a spreadsheet to map the from Merrill to FM format. Having put some time reading the FM manual on the formats, I was dreading writing an awk programme to do the job. (I am an old Unix hand, but it has been nearly a couple of decades since I did that kind of stuff!!) Phew ... I will give the spreadsheet a go tomorrow.

[surendar jeyadev]

Mark,

Please advise on this tangential topic. I have looked the issue on the Message Board, but I would like to verify it as this is related to the Merrill issue and I do not want two bombs falling at the same time.

As luck would have it, I transferred 3 mutual fund positions from TR Price to Merrill this month. They were moved in kind and Merrill has the full transaction history (going back to 2010). I believe that the right thing for me to do is just to drag and drop the 3 funds from the TRP folder to the Merrill folder in Portfolio Manager. That should preserve all the past history, right?

OTOH, wouldn't my Merrill transaction download for this month show all those transactions all over again? If so, should I just go the routine reconciliation route and NOT drag and drop and just Transfer Out the mutual funds in the TRP folder?

Please let me know which way/best way to proceed.

My apologies for asking this, but this is going to be a tricky month as it is and I do not want to add to my problems.

Thank you (and JeanneMarie) for all your help.

(I am surprised that nobody else has chimed in on this conversation. I would guess that there are more Merrill customers ....)

[surendar jeyadev]

Mark,

I tried the follow your process, but got an error.

I downloaded the CSV file from Merrill and copied the transactions in the CSV file into
your reformatting spreadsheet. The "Fund Manager Import" sheet looked fine. I saved
that sheet alone as CSV file and when importing I set the format to

"MM/DD/YY,SYMB,ACCT,TRAN,ANUM,BNUM,CNUM,MEMO"

as stated. I got got this error: " One or more errors were encountered during import.
Would you like to view a log of the errors?"

I hit "Yes" and saved the log.

Curiously, after doing so, the "Preview Importing Transactions" window seems to correctly
display the Distribution transactions. At his point, I hit "Cancel" and got out of the process.
While things looked ok, I want to be sure that I am not putting my foot on a mine.

I decided to hold off downloading Holdings and then reconciling till I hear from you.
If you want to see the log file, I will be more than happy to send it to you.

Thanks for all your help.

[Mark]

Hi surendar jeyadev,

2 things...

1) The format string you enter shouldn't have double quotes around it. Maybe you just wrote it that way on the forum, but wanted to clarify...

2) If you don't adjust the number of rows with equations on the "Fund Manager Import" sheet after pasting you may very well get some errors. It is just data rows with no data. You can send me the log file if you'd like me to take a look.

Basically, it sounds like it worked okay, but you're getting some unexpected errors from extra rows in the data file, that really don't hurt anything, but are just alarming...

[JeanneMarie]

My file has no bill payment or other "banking" transactions. In fact, it's all distributions, no trades either. I'm basing my import on your sample file with my data pasted over it and your format string.

But I'm rather frozen in place. The message FM gives me is: Could not find all the specified fields in the importing file.

I tried deleting the header row, but that met my low expectations. Same message.

Even before I spend much more time on this, I can't wrap my brain around how FM will recognize the 8 columns named in the format string from the 13 columns in the spreadsheet--when the names at the top of the columns are not even consistent and the 8 needed columns are not simply the first 8 columns in the same order. Can you make sense of this for me? Or am I looking for problems in the wrong place?

What seems sure is that two sets of instructions are very substantially at odds:
https://www.fundmanagersoftware.com/hel ... neric.html
and
https://www.fundmanagersoftware.com/faq_merrill.php (+ the spreadsheet)

I have to drop this for a few hours. If you can direct me to what I'm missing, I'll be grateful.

[Mark]

Hi JeanneMarie,

It sounds like maybe you are not saving the "Fund Manager Import" sheet to a new CSV file and importing that... If you look at the helper spreadsheet, there are 4 sheets:

Instructions
Merrill CSV
Fund Manager Import
Mappings

The idea is that you copy/paste from the activity CSV you download from Merrill and replace all the data in the "Merrill CSV" sheet with the data from your activity CSV you downloaded. That CSV file does have 13 columns of data (A - M). However, you are not importing that file, instead you then go to the "Fund Manager Import" sheet and save that as a completely new CSV file. That sheet only has 8 columns of data (A - H). That sheet references the data you pasted on the Merrill CSV sheet, but reformats it, so it can be imported into FM. It uses the format string:

MM/DD/YY,SYMB,ACCT,TRAN,ANUM,BNUM,CNUM,MEMO

If you look at the "Fund Manager Import" sheet, you'll see there are no column headers. They aren't needed to import. There are also some extra instructions about deleting unused rows on the "Fund Manager Import" sheet, but if you don't do this it will just give you some warnings, while still importing the data you did have.

[surendar jeyadev]

My file has no bill payment or other "banking" transactions. In fact, it's all distributions, no trades either. I'm basing my import on your sample file with my data pasted over it and your format string.

But I'm rather frozen in place. The message FM gives me is: Could not find all the specified fields in the importing file.
......
......

I have to drop this for a few hours. If you can direct me to what I'm missing, I'll be grateful.

While messing around the error logs I was generating, I ran into this very error. After one of many attempts to get FM to read all the rows in a massaged CSV (which contained all the
rows in the CSV downloaded from Merrill). That was a surprise! I did not know why that something that, at least, had partially worked was completely failing all of a sudden. And then, I tried the whole process from scratch and got back to the partial success mode.

In hindsight, I believe that in all my editing to the massaged CSV (trimming unwanted trailing rows, etc.) I think that I may have inadvertently saved the the massaged CSV as an OpenOffice spreadsheet rather than as a CSV. I cannot think of anything else, really and am now extra careful. Always "Save As" and never "Save"!!

I have also privately communicated with Mark about manually entering the ANUM, BNUM and CNUM values in the massaged sheet so that transactions that are not read into FM (reinvestment of distributions in my case) can go through and not generate errors. Perhaps even the banking transactions can be handled this way. Waiting for Mark to respond.

[JeanneMarie]

It sounds like maybe you are not saving the "Fund Manager Import" sheet to a new CSV file and importing that... If you look at the helper spreadsheet, there are 4 sheets:

Omigosh. Pardon me while I sew a big, red "J" on my shirt--J for jackass. Really inexcusable, but please forgive me. Don't you want to delete my message? I won't delete it if you think it could be instructive (in the most embarrassing way). But please feel free to delete it.

Actually, I'd suggest that another line or two in your instructions could be helpful, drawing attention to each tab. It's not that your instructions are unclear. But some of us operate too much on autopilot, or jump into things with unexamined assumptions/preconceptions because something looks familiar.

Thanks, I look forward to studying ALL of the tabs of that file far more thoroughly.

[surendar jeyadev]

Thanks, I look forward to studying ALL of the tabs of that file far more thoroughly.

Excuse me if you have already gone through all this and what I say below is old hat.

I would also suggest studying the cell formulae in the "Fund Manager Import" tab for all the
columns. Mark populates these columns in this tab by grabbing the "raw data" from the
"Merrill CSV" tab and "massaging" them as needed to create the entries in the "Fund
Manager Import" tab as and when needed.

Column D entries (kind of transaction) are translated from the Merrill format (col H in
the "Merrill CSV tab) to the FM format by using the translation table in the "Mappings" tab.

Column E is a good one. Share price is calculated from cols M and K of the Merrill tab.
(The "Merrill CSV" tab does have the price in col L, but Mark prefers to calculate it. I
think that it is smart as the sum and the number of shares are always more basic and
will be present, while price is, kind of, implied.)

Column F is just copied as is from the Merrill tab, but for ignoring the negative
sign for the Sell transactions.

Columns A, B and C are more straightforward copying.

There is error checking and handling everywhere!

This opens up the possibility for further tweaking the "Fund Manager Sheet" before
feeding to the FM. In particular, dividend reinvestments are not handled, but I *think*
one can fiddle with those rows after running the Merrill download through the
massager. Again, this is something I am waiting for advice from Mark.

There *may* be a possibility of handling the pesky "Reinvestment Share(s)" entries
in col H (type of transaction) of the Merrill tab. At present they do not make it
to the FM Import tab as the "Mappings" tab does not contain this type. It would be
great if these transactions can also he handled in massaging part, but mapping
"Reinvestment Share(s)" to BUY. But, I think there is a problem here. The
reinvestment row in the Merrill downloaded CSV has the number of shares
acquired by the reinvestment (col K), but not the amount or the share price
(the cells cols L and M are blank). This is a bummer! But, Column I on the
downloaded CSV has all the numbers we need! I *think* that we can just
pop the correct numbers in cols D, E and F of the massaged CSV and then
feed it to FM. Awaiting Mark's take on this.

Would greatly appreciate your take on this as well.

[JeanneMarie]

Mark:

Your Excel file's MAPPINGS table identifies "foreign dividend" as DIST_1 which the table in the Help file identifies as User Defined. I think the Mappings table should say DIST_F.

On the other hand, the Depository Bank Fee, seems correctly identified as a DIST_F (negative). I do worry whether every Depository Bank Fee would fit DIST_F. Maybe depository bank fees (indeed, fees generally) will always require a little extra attention.... TBD.

[Mark]

Hello,

Reinvestment Issue:
Surendar sent me a sample file with reinvestments. As Surendar pointed out, these transactions with column I set to "Reinvestment Share(s)" don't have a value in column M. There are probably a few ways to deal with this:

• Ignore these during import, and then after the import, manually edit the dividend to be "Reinvested" instead of "Distributed", and enter the number of shares
• Modify the helper spreadsheet, so these import as buy transactions. Unfortunately, they will import as a buy for the correct number of shares, but for $0, since that is what is shown for the amount. You'd still have to manually edit the buy transactions after import. You can do this by editing the "Mapping" sheet, like in cell A13, put in: Reinvestment Share(s) which will map to a BUY.
• Modify the downloaded CSV file to combine the Dividend and Reinvestment Shares(s) onto a single line, and update the helper spreadsheet accordingly, so this imports as a REINV_D

Basically, all of the options require some sort of manual adjustment, given the way the reinvestments are downloaded.

Foreign Dividend Issue:
Yes, I mapped those to DIST_1. I believe they are dividends, but just from a foreign source. I wouldn't think they should be in the same bucket as account fees (DIST_F). You could map them to whatever distribution type you want by editing the cell B11 on the Mappings sheet. If you want to combine these with non-foreign dividends, just make that cell DIST_D. By keeping them in a separate distribution type it allows you to report more precisely. You can also change the label of the DIST_1 distribution type using "Options / Distribution Labels...". You might also want to look at how they were importing previously (when using the transaction retrieve feature), and map them the same way.

I realize this is way more work and prone to error than when Merrill offered an OFX server and also when they offered a QFX file download. Unfortunately, this is the only means remaining to import transactions from Merrill. You might share this feedback with your Merrill representative. Maybe if enough people complain they will restore at least the ability to download a QFX file from their website.

[JeanneMarie]

Sorry, I misread the fine print in the help file. Yes, DIST_F is a fee.
[EDIT: And that is how the Depository fee has turned up historically. ]

What is DIST_X? Does that mean foreign taxable?

[Mark]

DIST_X maps to "foreign tax" distribution type. I believe that foreign tax is different than a foreign dividend. My understanding is that a foreign dividend is a dividend received on a foreign security, where as a foreign tax is a tax paid on dividends from a foreign security.

Really, you can use the distribution types in any way you want. There are only 2 distribution types that have special meaning in Fund Manager. The rest are just for separating them by labels for reporting purposes. The 2 special ones are Account Fees and Return of Capital. Return of Capital is special because it lowers your tax cost basis. Account fees can optionally be included in tax cost basis (Options / Report Preferences... / Capital Gains / Include Account Fees in Basis) and optionally excluded from yield calculations (Options / General Preferences... / Yields / Include Account Fees in Yield Calculations). All other distribution types are treated equally, but can be separated out in reports, like the Distribution Summary report, where you have separate columns for each type. The labels can be adjusted under "Options / Distribution Labels...".

[JeanneMarie]

RE foreign dividends/foreign taxes.

Yes, of course you're right. I have a hard time remembering that a distribution can be negative. A foreign tax paid/charged would be a negative distribution. Quite different from a foreign dividend.

I think I've been at this too long and will try to take a break.