Merrill Lynch not able to retrieve transactions

[Mark]

HI JeanneMarie,

That is quite unfortunate... You can import a CSV file, but it is more difficult. The OFX/QFX file format is well defined, so you can easily import it. The CSV file can have data in any order, and there is no definition of how to report anything, so you likely will need to massage the data, and then you enter a "format string" to tell FM how the data is formatted. See:

File / Import / Transactions / Generic...

Press "Help..." in that dialog box for instructions, or read them online here.

[JeanneMarie]

Thanks, Mark. I had already found that page. As I pondered it, I realized that downloading activity left me without anything to reconcile against. So I do see this page
https://www.fundmanagersoftware.com/hel ... mport.html
and the "generic position file" option in the reconciliation wizard.

Bummer, but we soldier on... I'm pretty sure I'll be back for more help. But in the meantime, are there other major problems (like reconciling) that I haven't thought of?

[surendar jeyadev]

Yes, Merrill seems to have put even tighter restrictions than Vanguard.

My attempts to reach out did not go far ....

.... and I was not surprised. While my complaints/suggestions were logged and were sent to the IT folks, I was told that in matters of "security" the retail side has little say. Not an unexpected line.

Keeping my eyes open on how we can navigate the new world.

[Mark]

I realized that downloading activity left me without anything to reconcile against

If they make holdings available (not just transactions) in CSV you can reconcile against that format.

[JeanneMarie]

So, I'm retrieving transactions from an Merrill Lynch text file and it's looking good so far. But I haven't clicked FINISH to accept them because of just one problem:

- Instead of a MMF, in one account, the default cash account is ML Bank Deposit, which has no trading symbol but does have CUSIP 990286916. In FM, I plugged that CUSIP into the trading symbol field in Investment Properties. (I always did that anyway with all investments lacking a trading symbol so that I could sort on the symbol field in the Portfolio Editor.)

- When ML Bank Deposit pays a little interest, this file identifies it with that CUSIP in a "symbol/cusip" field. For all other "banking" transactions (bill payment, direct deposit), which always do hit that default cash account, this symbol/cusip field is blank. So I figured okay, I'll just plug in that cusip for those other transactions.

- Now other investments without trading symbols but with CUSIPs seem to import fine (e.g., bonds). But those assigned to this CUSIP consistently import as new investments--whether ML's file identifies them with this CUSIP or I did.

So if I have to tweak a few transactions a month manually, I can do that. I assume I'll just move transactions from the "new investment" to the existing investment. But before I give up on this, I figured I'd ask if there's some way to avoid creating a new investment like this. I don't want to turn this feature off in the IMPORT dialog since then I wouldn't even see the missing transactions.

I've stared at old OFX files but can't glean anything useful from them.

Your thoughts? And many thanks.

[Mark]

Hi JeanneMarie,

Is the symbol/cusip field importing with the SYMB keyword in the input format string? If so, and an existing investment has that field's value as the symbol, it should map that transaction into the existing investment. It would only create a new investment if no existing investment in the account being imported into has that symbol.

[JeanneMarie]

Right. And as I say, other investments without trading symbols but with CUSIPs seem to import fine. It does seem possible that this is just some exceptional thing at Merrill. But as long as it's clearly just this single investment, and you see no obvious troubleshooting strategy, maybe I won't waste too much more time on this.

Onward!

[surendar jeyadev]

So, I'm retrieving transactions from an Merrill Lynch text file and it's looking good so far. But I haven't clicked FINISH to accept them because of just one problem:

- Instead of a MMF, in one account, the default cash account is ML Bank Deposit, which has no trading symbol but does have CUSIP 990286916. In FM, I plugged that CUSIP into the trading symbol field in Investment Properties. (I always did that anyway with all investments lacking a trading symbol so that I could sort on the symbol field in the Portfolio Editor.)

Mark adn JeanneMarie,

I am no power user, and so far the furtherest that I have gone is the import of QFX files. That brought in all the transactions and I could work around some of the anomalous entries I had previously mentioned. I have not tried importing a text file as yet, but is it the same way as one exported a QFX or OFX files, except we do

Import > Transactions > Generic

and set the correct format and specify the portfolio? I would appreciate in JeanneMarie would share the format used that makes thing look "good"

From what I understand, importing a text file gives JeanneMarie most of it. I will also have the same problem with the "MMF" which has neither a CUSIP of a symbol within FundManager (I mean in the Properties/General tab). I assume I can fix that by hand.

Mark, I would very much appreciate if you can post the instructions for us ML users?

Thank you both.

[JeanneMarie]

First, make no mistake: This is hard. As Mark said, one has to massage the data. It's not as "well defined" as a OFX file. It will help if you are pretty proficient with a spreadsheet program like Excel. When I said "looking good so far" that included a problem I did not solve and will be making a few manual entries to adjust for.

I'm going to download new files after COB Friday and go thru this process once more. Hopefully I'll finish over the weekend, altho I do have one other major project. Then I'll update my rather extensive notes, and I'd be happy to share them. They will be a bit idiosyncratic: first because they are centered at Merrill (which is fine with you, surendar jeyadev) and second because they'll tackle problems peculiar to my holdings (might be instructive with other investments, might not).

[surendar jeyadev]

First, make no mistake: This is hard. As Mark said, one has to massage the data. It's not as "well defined" as a OFX file. It will help if you are pretty proficient with a spreadsheet program like Excel.

Thank you are looking forward to it.

I am very familiar with Excel (though I use Open Office) and can "massage" spreadsheets.

It is a question what has to be massaged and how. That is why I am looking for some kind of recipe. But, I do backup every time I do something with FM and so I do not mind experimenting with the imports.

Can you tell me why plain text is preferred to CSV?

Thanks again.

[JeanneMarie]

Can you tell me why plain text is preferred to CSV?

CSV is effectively plain text with commas separating values. I'm not 100% sure that the distinction is important (except that you wouldn't want to confuse your computer). I downloaded plain text, in the hope (possibly vain) of avoiding any complications, and imported the file into the spreadsheet as TAB DELIMITED. But I ended up exporting the file as CSV and importing CSV into Fund Manager.

That's one thing I'll be trying to clarify for myself in this second round. But it does appear that you can stick with CSV all the way. [EDIT: I do think tab delimited makes more trouble. Stick with *.CSV all the way. I have to drop this now, but I didn't want to leave that notion out there.]

By the way, I'm currently using LibreOffice Calc for spreadsheets. Good to know that you are flexible, surendar jeyadev, and can adapt.

Cheers!

[Mark]

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.

[JeanneMarie]

I think your instructions need to include removing the headings in the columns and/or deleting columns and sequencing the columns to align with the input string and/or including those placeholders like XX mentioned under RECONCILING on your instructions page. I'm not sure whether all of that is equally important and can't test it right now. Your input?

On a more substantive level, this may work for plain vanilla brokerage accounts. But for those who use any banking features of the Cash Management Account (and I don't use them nearly as much as some clients), it's only the beginning.

And as I mentioned elsewhere in this thread, and related to those banking transactions, there is the odd ML Bank Deposit (as a default cash account), which just refuses to update like other investments that have CUSIPs but lack trading symbols. At the moment, my observation is that I have to adjust that manually. Happily for me, that's only for pennies in interest once a month plus a handful of banking transactions.

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?)

If I'm misunderstanding something, please advise. It will be a kindness.

[Mark]

Hi JeanneMarie,

You shouldn't need to delete any headings to use this. The downloaded activity file should have columns that exactly align with the columns on the "Merrill CSV" sheet. Copy columns A-M from the downloaded CSV and paste them over the same columns on this sheet. The "Fund Manager Import" sheet is the one you save to a new CSV file, and import that.

You're right, there is no special support for bonds in this spreadsheet. This spreadsheet only covers the basics now. It isn't nearly as sophisticated as the QFX/OFX import is. If you have some example CSV file with some basic bond transactions you could share, maybe we could improve the spreadsheet.

[JeanneMarie]

If you have some example CSV file with some basic bond transactions you could share, maybe we could improve the spreadsheet.

Well, I wasn't referring to transactions, just to the data exported in the POSITION file and needing to massage the QUANTITY for bonds.

Have to drop this for now. Will return.