Got a credit card? use our Credit Card & Finance Calculators
Thanks to Wasron,jfgw,Rhyd6,eyeball08,Wondergirly, for Donating to support the site
OFX examples
-
- Lemon Quarter
- Posts: 1085
- Joined: November 4th, 2016, 1:12 pm
- Has thanked: 179 times
- Been thanked: 374 times
OFX examples
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 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.
-
- Lemon Slice
- Posts: 943
- Joined: November 4th, 2016, 11:33 am
- Has thanked: 32 times
- Been thanked: 462 times
Re: OFX examples
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
-
- Lemon Slice
- Posts: 582
- Joined: November 4th, 2016, 1:17 pm
- Has thanked: 192 times
- Been thanked: 126 times
Re: OFX examples
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.
-
- Lemon Quarter
- Posts: 1085
- Joined: November 4th, 2016, 1:12 pm
- Has thanked: 179 times
- Been thanked: 374 times
Re: OFX examples
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.
-
- Lemon Slice
- Posts: 582
- Joined: November 4th, 2016, 1:17 pm
- Has thanked: 192 times
- Been thanked: 126 times
Re: OFX examples
You'll need a POSSTOCK entry to describe the stock your adding, iirc.
So you have a working example ofx that adds a transaction?
So you have a working example ofx that adds a transaction?
-
- Lemon Quarter
- Posts: 1085
- Joined: November 4th, 2016, 1:12 pm
- Has thanked: 179 times
- Been thanked: 374 times
Re: OFX examples
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.
-
- Lemon Slice
- Posts: 943
- Joined: November 4th, 2016, 11:33 am
- Has thanked: 32 times
- Been thanked: 462 times
Re: OFX examples
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
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
-
- Lemon Quarter
- Posts: 1085
- Joined: November 4th, 2016, 1:12 pm
- Has thanked: 179 times
- Been thanked: 374 times
Re: OFX examples
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.
-
- Lemon Quarter
- Posts: 1085
- Joined: November 4th, 2016, 1:12 pm
- Has thanked: 179 times
- Been thanked: 374 times
Re: OFX examples
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,
-
- Lemon Slice
- Posts: 582
- Joined: November 4th, 2016, 1:17 pm
- Has thanked: 192 times
- Been thanked: 126 times
Re: OFX examples
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.
Return to “Financial Software - Discussion”
Who is online
Users browsing this forum: No registered users and 36 guests