The Lemon Fool

Posted: **January 9th, 2018, 5:08 pm**

Does anyone have, or can point me to, sample OFX format files for importing investment transactions ( buy / dividend / sell ) into MS Money ?

I can do it in QIF, but I'd like something less interactive. But I'm not sure I care enough to plough through the OFX schema from scratch.

Posted: **January 10th, 2018, 8:49 pm**

genou wrote:Does anyone have, or can point me to, sample OFX format files for importing investment transactions ( buy / dividend / sell ) into MS Money ?

I can do it in QIF, but I'd like something less interactive. But I'm not sure I care enough to plough through the OFX schema from scratch.

I suggest using hleOFXquotes (which I think can be had from https://github.com/hleofxquotes )

I use this to convert a .csv file which is in FT.com format into OFX. (the .csv file comes from a webserver cgi script which scrapes the quotes I want, renders them on a webpage, and makes a csv file available). The OFX file contains transactions for a dummy account which have the effect of updating the latest prices in Money.

You could use this tool to generate some OFX files to play with.

Another tool you need is the OFXAnalyser tool - moneymvps.org/faq/article/366.aspx
Use that to check the correctness of OFX files before you attempt to load them into Money.

If you want a sample OFX file generated by hleOFXquotes, send me a PM with your enail address and I'll send you a copy.

PochiSoldi

Posted: **January 11th, 2018, 10:13 am**

genou wrote:Does anyone have, or can point me to, sample OFX format files for importing investment transactions ( buy / dividend / sell ) into MS Money ?

I can do it in QIF, but I'd like something less interactive. But I'm not sure I care enough to plough through the OFX schema from scratch.

I wrote a simple CSV to OFX converter to import FT portfolios into MS Money. https://github.com/gbjbaanb/ft2ofx
It doesn't do transactions though, but it is simple enough to see the ofx results.

Posted: **January 11th, 2018, 5:34 pm**

gbjbaanb wrote:
I wrote a simple CSV to OFX converter to import FT portfolios into MS Money. https://github.com/gbjbaanb/ft2ofx
It doesn't do transactions though, but it is simple enough to see the ofx results.

pochisoldi wrote:If you want a sample OFX file generated by hleOFXquotes, send me a PM with your enail address and I'll send you a copy.

Thanks to you both. I have now got an OFX that the analyser is nearly happy with, and Money says the file imports OK .

Analyser reckons

Running Data Through Parser
No Parse Errors

Reading Data Into Buffer
Tokenizing Data
Initializing Data Structures
Analyzing Data
Verifying Security Types
WARNING: Transaction skipped <INCOME>:
No matching INFO security
<FITID>12345
<UNIQUEID>LLPC

WARNING: Position skipped <POSSTOCK>:
No matching INFO security
<UNIQUEID>LLPC

Verifying Security Uniqueness
Verifying FITID Uniqueness
Verifying Sign Correctness
Verifying Total Calculations
Verifying Other
Done Analyzing File

And MSMoney doesn't do anything with the info - no dividend created, no price update. I'm at a loss what the issue is, since the parser Warning returns nothing useful from Google.

Posted: **January 11th, 2018, 8:58 pm**

You'll need a POSSTOCK entry to describe the stock your adding, iirc.

So you have a working example ofx that adds a transaction?

Posted: **January 11th, 2018, 10:17 pm**

gbjbaanb wrote:You'll need a POSSTOCK entry to describe the stock your adding, iirc.

So you have a working example ofx that adds a transaction?

I don't have a working ofx. The analyser is happy with a file that contains only a dividend ( i.e INVTRANLIST with no INVPOSLIST ). Money is also happy ( at the syntax level I assume ) , but I get no dividend entry. With only INVTRANSLIST I get

"WARNING: Transaction skipped <INCOME>:
No matching INFO security"

but the file still parses OK.

I went down the route you are remembering by adding INVPOSLIST and still no joy - I get the two Warnings in the last post.

The whole show looks like this:

<?xml version="1.0" encoding="UTF-8"?>
<?OFX OFXHEADER="200" VERSION="200" SECURITY="NONE" OLDFILEUID="NONE" NEWFILEUID="NONE"?>
<OFX>
<SIGNONMSGSRSV1>
<SONRS>
<STATUS>
<CODE>0</CODE>
<SEVERITY>INFO</SEVERITY>
<MESSAGE>Successful Sign On</MESSAGE>
</STATUS>
<DTSERVER>20180111143811</DTSERVER>
<LANGUAGE>ENG</LANGUAGE>
</SONRS>
</SIGNONMSGSRSV1>
<INVSTMTMSGSRSV1>
<INVSTMTTRNRS>
<TRNUID>a054c19d-3331-4d3f-b4b5-474337768039</TRNUID>
<STATUS>
<CODE>0</CODE>
<SEVERITY>INFO</SEVERITY>
</STATUS>
<INVSTMTRS>
<DTASOF>20180111143811</DTASOF>
<CURDEF>GBP</CURDEF>
<INVACCTFROM>
<BROKERID>le.com</BROKERID>
<ACCTID>12345</ACCTID>
</INVACCTFROM>

<INVTRANLIST>
<DTSTART>20171201</DTSTART>
<DTEND>20180111</DTEND>

<INCOME>
<INVTRAN>
<FITID>12345</FITID>
<DTTRADE>20180104133404</DTTRADE>
</INVTRAN>
<SECID>
<UNIQUEID>LLPC</UNIQUEID>
<UNIQUEIDTYPE>TICKER</UNIQUEIDTYPE>
</SECID>
<INCOMETYPE>DIV</INCOMETYPE>
<TOTAL>2.00</TOTAL>
<SUBACCTSEC>OTHER</SUBACCTSEC>
<SUBACCTFUND>OTHER</SUBACCTFUND>
</INCOME>

</INVTRANLIST>

<INVPOSLIST>
<POSSTOCK>
<INVPOS>
<SECID>
<UNIQUEID>LLPC</UNIQUEID>
<UNIQUEIDTYPE>TICKER</UNIQUEIDTYPE>
</SECID>
<HELDINACCT>OTHER</HELDINACCT>
<POSTYPE>LONG</POSTYPE>
<UNITS>1</UNITS>
<UNITPRICE>1.78</UNITPRICE>
<MKTVAL>1.78</MKTVAL>
<DTPRICEASOF>20180111</DTPRICEASOF>
</INVPOS>
</POSSTOCK>
</INVPOSLIST>

</INVSTMTRS>
</INVSTMTTRNRS>
</INVSTMTMSGSRSV1>

</OFX>

If I fiddle with ACCTID, I get a prompt to match the import to an account, so Money does see a valid file - it is just skipping the entries as the analyser suggests it will. But I have no idea what I'm looking for. I may be being idle - I haven't tried to start at the beginning of the OFX documentation . But there's a lot of that documentation and this was supposed to be an easy way to enter dividends, and it is a lot of documentation.

I can't see how it could tie together transaction and position other than by UNIQUEID, and AFAIK Money has no logging function, so the failure is opaque. Which is where I grind to a halt. But it is tantalising close.

Posted: **January 11th, 2018, 11:15 pm**

Would this page be helpful in determining if your dividend transaction is correctly formatted?
https://lists.gnucash.org/pipermail/gnu ... 58424.html

(Hint - set the sub account type to Cash instead of other?)

From a bit of googling around, it looks as if the gnucash developers mailing list might be a source for sample OFX files.

Pochisoldi

Posted: **January 12th, 2018, 10:39 am**

pochisoldi wrote:
(Hint - set the sub account type to Cash instead of other?)

Pochisoldi

Tried that, with no result. According to MS documentation, Money ignores SUBACCTSEC, SUBACCTFUND, and also HELDINACCT . But the parser regards the file as malformed if they are missing. I suspect you can put anything in these fields.

I'll have a wander around on the GNUCash archive, thanks for that.

Posted: **January 12th, 2018, 11:36 am**

pochisoldi wrote:
From a bit of googling around, it looks as if the gnucash developers mailing list might be a source for sample OFX files.

Pochisoldi

Indeed it was. The missing INFO was that the file requires an additional section detailing the securities referred to in the INVTRANLIST, being the SECLIST , so you need

<SECLISTMSGSRSV1>
<SECLIST>
<STOCKINFO>
<SECINFO>
<SECID>
<UNIQUEID>LLPC</UNIQUEID>
<UNIQUEIDTYPE>TICKER</UNIQUEIDTYPE>
</SECID>
<SECNAME>Lloyds Banking Group 9.25% Non Cum Pref Shs</SECNAME>
<TICKER>LLPC</TICKER>
</SECINFO>
</STOCKINFO>
</SECLIST>
</SECLISTMSGSRSV1>

in addition. At that point Money will import the transaction and lead you through accepting it.

I don't have an INVPOSLIST since I am not, for this exercise, looking to import prices. This has been an interesting exercise. Thanks for the assistance,

Posted: **January 12th, 2018, 12:07 pm**

whoops =- sorry old chap, I meant SECLIST - as in a list of the stocks you were affecting. been so long I got POSSTOCK and SECLIST confused. That might have saved you a bit of work.

The Lemon Fool

OFX examples

OFX examples

Re: OFX examples

Re: OFX examples

Re: OFX examples

Re: OFX examples

Re: OFX examples

Re: OFX examples

Re: OFX examples

Re: OFX examples

Re: OFX examples