The import framework is largely complete. We're still missing some field mappings, advanced features, and the imports themselves, but the hard part is done. (This was actually completed more than a month ago but I'm a bit behind on my posts.) The difficult part was getting the transaction imports to run quickly with our object model, which doesn't lend itself well to bulk data insertion.
The biggest hurdle is that our event model needs to analyze and update other data items at the same time the transactions are saved in the database. For example, when a new Sell transaction is imported, the object model does things like:
- close open tax lots,
- update the running share balance for all activities,
- update the current share balance at the position level,
- verify the current share balance matches the custodian's share (and create an alert if it doesn't).
To do all that, our object needs to load data like:
- any open tax lots,
- the tax allocation method, and
- any existing activities that occur after the current activity.
To import one single transaction, we potentially need to load a *lot* of information. I'm actually simplifying it a bit--different types of error checks need information I'm not listing, and some of the tax laws (like wash sales) may even need to look at transactions in other accounts. So that's what we we're dealing with.
We currently have transactions importing between 10-70 a second on a single-processor machine. There's a wide variation because the speed depends on factors like current account size, whether you're importing multiple transactions for the same account, tax allocation strategy, etc.
As a basis for comparison, Techfi's imports on a similar machine would run at 8-10 a second. Our goal was to be 10x the speed of Techfi. Obviously, we're not there yet, but we've only done basic optimization and are reasonably confident we can eventually get it that fast.
Now that you appreciate all the hard work ;-), here are the features:
Data Acquisition - The framework has "data grabbing" capabilities. If a custodian stores their data someplace we can access via the Internet (like an FTP site), FinFolio will automatically download it. However, if a custodian uses proprietary software, we will *not* be able to automate the file acquisition and you'll have to use the custodian's software before importing.
Backing Out Files - You will be able to back out certain import files and remove the data from the database. (ie. If your custodian sends you incorrect prices, you can simply back out the prices and wait for a new file.)
Historical Share Balances - Each time the custodian sends you share balances, they are stored in a Share Balance table. You will know exactly which day a particular position goes out of balance.
On-the-fly Error Checking - All balance checks and most other deep reconciliation tools happen on the fly. So whether you hand-enter your data or import it, you will immediately see any problems. These errors are visible on a centralized error list and on client/account lists so you are always aware of data problems. This "deep reconcilation" capability goes way beyond simple share-balance checking and I'll be talking much more about it later.
Import Blotter - This is an advanced version of a standard trade blotter, with a holding queue for transactions, prices, FX rates, and custodian share/value balances. Import data hits the import blotter, before being "posted" into your database. All the balance checks and other deep reconciliation tools will run both on your live data and on the Import Blotter data. This lets you do troubleshooting and data cleaning without your front office (or clients) seeing bad data.
Background Importing - Once you start your imports, they will run as a batch process that you monitor on the status bar or in an import tracking panel. During the import, you can use the software to do other things (or start troubleshooting import errors as the data starts coming in).
Overwrite Existing Data - Numerous options for determining whether pieces of data already exist in the system, and whether to overwrite, ignore, or update the existing data.
Conversions - Conversions (from competing portfolio management systems) run through the same import framework, giving you access to the same troubleshooting tools.
Automation - All imports can be automated via Microsoft Visual Studio. This lets you build sophisticated macros that automate your import processes.
Extensible Framework - Create imports within the framework, allowing your operations team to use the same features and troubleshooting tools. Hook to internal systems and custodians, only worrying about your field mappings.