Can you iterate the result of a convert() ?

[ChristopherMaurer]

First of all, thanks very much for this package. I'm new to Python (but 35+ years of C and relational DBMSs) and as a first project have taken on a program to transform OFX statement data to a PostgreSQL data model, starting with my brokerage accounts. As with most of my projects, this one is being over-engineered so that the definition of OFXList-to-Table and OFXTag-to-TableColumn resides in tabular form in the data base (with the idea that other forms of OFX data could be accommodated without code changes). As such, the OFX side of the program has no knowledge either of what the Financial Institution supplied in the file, nor what the data base model wants to store. Its job is to iterate the lists and hand every element to the data base side for processing.

As such, it is pretty easy to navigate the xml.etree.elementtree returned by OFXTree.parse():
for seclist in XMLTree.iter("SECLIST"):
for sec in seclist:
for secdata in sec.iter():
print(secdata.tag, secdata.text)

will navigate the SECLIST list(s), delivering all the data elements in text form, which is pretty inconvenient for the data base writer, I would rather use the results of the convert() with numbers in DECIMAL form and dates in DATETIME form which will automatically cause the proper formatting for the data base INSERTs. Now I know I can iterate the individual securities with:
ConvertedTree = XMLTree.convert()
for sec in ConvertedTree.securities:

I am stuck on how to deliver all the data elements for each security. I've read Chapter 4 of your documentation many times and tried numerous ways to iterate this sub-tree and also Googled for iterating an OFX Tree. No joy.

So, before I give up on convert() entirely, is there any way to iterate the elements in each list entry that I don't understand because I'm new to Python and do not understand your data structures?

[csingley]

Sorry I just saw your question. Yeah you really want to pick up on the results of type conversion, for sure.

Just to standardize some conventions - naturally ofxtools tries to conform to the spec, which defines "Aggregate" container types and "Element" data primitives - so we use those terms.

So you're looking to scrutinize ofxtools.models.base.Aggregate - in particular I think you're looking for this method:

    @classproperty
    @classmethod
    def elements(cls) -> Mapping[str, Types.Element]:
        """
        Mapping of all class attributes that are Elements but not SubAggregates.

        N.B. Types.SubAggregate is a subclass of Element.
        """
        return cls._filter_attrs(
            lambda v: isinstance(v, Types.Element)
            and not isinstance(v, Types.SubAggregate)
        )

That will get you a mapping of attribute name to Element type - you can use the keys of that map as an iterator; feeding that into getattr() along with your security instance, and I think that gets what you are looking for.

Interested to see what you get up to here! It's fairly tricky to schematize OFX trees into flat DB tables comprehensively & regularly.

[ChristopherMaurer]

Replying to "Interested to see what you get up to here!".

Here's my first effort. I've run it successfully on all my account relationships at BofA(investment account), Wells Fargo(checking and credit card), Vanguard(investment), and Barclay's(credit card). Unfortunately my PNC mortgage does not offer this service, so LOANSTMTTRN remains untested.

Interestingly, in the end I decided to let the data base dictate the internal data formats because ultimately they need to end up there anyway, and if someone is dumb enough to specify a MEMO field as numeric they deserve every error they are going to get. So, thanks very much for OFXTree - it was a very big help and the casting of it into a xml.etree.ElementTree data structure enabled some very useful searching and iteration. But I did not end up using Convert() at all.

I'd be very much interested in any feedback that occurs to you.

[csingley]

Your other way to go would be to start by parsing the OFX DTD, which is closer to the original vision of how this SGML protocol was intended to be used. I only spent a short time trying to build Python bindings for sgmlop or Jade before dropping that in favor of lots of repetitive manual typing to visually validate against the PDF spec... since I am not, in point of fact, a C programmer with decades of working experience (just a finance geek generously supplied with Adderall).

By this time I've built up a large enough edifice of these manually defined types that it's likely not worth going back to basics there... but if you're looking to maximize code reuse by achieving generality through introspection & generation... you might indeed want to play around with feeding the OFX DTD into OpenJade, see what that opens up for you.

Good luck!