Investment Reporting Database (IRDB)®
Increasingly complex reporting mandates from myriad regulatory bodies require investment companies to deal with vast volumes of transactional, trading, and related data — and to interface with intermediaries of all types. As a consequence, IT staffs increasingly find themselves overwhelmed with requests for help and complaints about data inaccuracies. Paradoxically, though a variety of technical and related investment information is readily available externally, business users complain that such information is more difficult to access within the organization – even by those required to make critical investment decisions.
As specialists in the investment industry, we created the IRDB to analyze, measure, and report investment data to meet the needs of constituents with differing requirements, skill sets, and timetables. The tool provides a ready-made staging area that contains almost all the data elements necessary for a large conversion. We use it in all the conversions we perform. And it’s used by a number of our clients to make daily and monthly reporting easier and faster.
Given all the tasks required to complete, document, and reconcile accounts with the general ledger, monthly financial closes can be hectic. In our opinion, none of the popular investment accounting software packages does a great job of reconciliation. In fact, in most organizations, these tasks continue to be completed manually.
Even though the reconciliation process is fairly straightforward and somewhat routine, there can be occasional circumstances in which the financial closes can become extremely complicated and cumbersome. Production volumes, reporting requirements, staff resources, and market occurrences often create challenges. For mutual fund companies, these tasks must be completed quickly every day, or there is a danger of missing NAV deadlines.
For tax accountants, there are periodic needs to reconcile tax basis income and book values to the same values from GAAP (and STAT, for insurance companies, or IFRS for firms operating outside the US).
For portfolio accountants and treasury people, there are periodic needs to ensure that several disparate software systems are synchronized and consistent at the end of each accounting period, including investment management and accounting systems, general ledger packages, and custody banks.
If the corresponding systems are out of synch, the portfolio accountants must identify and explain the discrepancies. In some situations, particularly with high trading volumes, identifying the cause of a discrepancy can be like looking for a needle in a haystack. To continue the analogy, the IRDB acts like a magnet to find the needles quickly and systematically, dramatically streamlining the detective work for which investment accountants are responsible.
In most financial institutions, the aforementioned software packages stand on their own, independent of one another, requiring users to gather data from multiple sources. Many institutions spend millions of dollars trying to define and implement a robust, effective, easy-to-use repository or data warehouse to store all the relevant data in one place. However, it is often unwieldy to organize the data in such a way that makes it convenient to perform the necessary business functions. The reconciliation modules in the IRDB are designed to do just that.
The design objective of the IRDB (and its reconciliation module) is more than providing a simple method of collecting the position balances from various sources. It also serves as a means to display the differences and the underlying transactions to simplify analysis and facilitate research. The reconciliation module provides easy-to-use screens for cash reconciliation (including cash that is non-security related), asset reconciliation, and other reconciliation functions – including a reconciliation of ledger values to statutory reporting numbers. All this is provided in an application that accesses all the pertinent sources of data.
If you’re concerned about the quality of the data in your investment accounting system, you can ensure it’s correct by matching it against publicly available data. There are significant advantages to this approach:
- Accounting information is more accurate, thereby reducing bank reconciliations and those with other outside sources.
- Accounting values such as accrued interest are more accurate, since you have validated the accuracy and correctness of the coupon rate.
- Matching cash flows and bank cash positions against your accounting records is more trouble-free since you are assured the periodicity and interest calculations are correct for your mortgage backed securities and CMOs.
- Your amortizations are more accurate, and you are less likely to miss a put date if you have validated your call dates and put dates against those schedules.
- When other departments (such as tax or portfolio management) receive a report from the accounting system, they have greater confidence in the system when they see the data they expect to see and know it has been validated.
- Data such as shares outstanding can be retrieved, making it possible to create more accurate SEC 13f and 13g reports.
The IRDB lets you compare internal data to new market data. You review the report of the comparisons and decide if you need to make changes to your transaction systems. The market data is not automatically loaded into your transaction processing system for several reasons:
- Not all clients have the necessary software from their transaction system vendors to automatically load such data from a transaction file.
- You may have created your data for a specific reason, and you do not want to automatically override it. For example, you may store the original issue date and original issue price only if it is an OID bond, and you don’t want to store it for other bonds. The market data will show the original issue dates and the original issue prices for any securities, where available.
- The transaction system may store data in a specific way for certain types of data. An example of this is sinking fund payment data. For the small number of bonds that have sinking fund data, you can look at the market data and decide whether to put the data into the system, depending on whether the sinking fund schedule is mandatory or optional.
When the IRDB process is complete, you not only get the comparison report, but you also get the market data stored in the IRDB. This allows you to use it for reporting, or later updates, if you so desire. Some outside services might offer a one-time scrub of the securities you hold, for a fee. But when you use the scrubbing capability of the IRDB, you get an initial (one-time) scrub of the securities you hold, plus you may periodically redo the scrubbing process to include any new securities you add. This keeps your database scrubbed and current.
EZ Data Dictionary®
If you’re looking for a more effective database documentation tool than Excel®, you’re come to the right place. Here’s why:
- In Excel you can misspell the names of tables and columns, but not in EZ Data Dictionary.
- In Excel, you can miss some tables and columns, but not in EZ Data Dictionary.
- You can still copy and paste documentation into Excel from EZ Data Dictionary.
- Maintaining spreadsheet documentation is difficult In Excel when you add, change, or delete tables and columns. With EZ Data Dictionary, it’s automatic and easy.
- Custom reports are hard in Excel but easy in EZ Data Dictionary.
- You can’t update an Excel spreadsheet from multiple users. You can with EZ Data Dictionary.
- Documenting interfaces is like a whole new event in Excel, but it’s easy with EZ Data Dictionary®.
- Displaying the relationships between tables is not usually done in Excel, but it’s automatic in EZ Data Dictionary®.
- All the data you capture in EZ Data Dictionary is stored in a database so it’s easier for your IT department to access it.
In short, EZ Data Dictionary:
- Improves the productivity of your people
- Improves your system documentation
- Satisfies audit requirements
- Makes report building faster and easier
- Improves communication between systems professionals and end users for projects like data warehousing
- Saves time and effort by providing easy access to database design and definitions
- Is an easy, inexpensive way to distribute information about databases
- Allows users to review database elements and definitions in a friendly environment
- Integrates easily with SQL Server®, Oracle®, and Microsoft Access®
- Can be updated easily in a friendly multi-user environment.
EZ Data Dictionary® Videos
To find out more about the ways in which we can help, please call us at 603-912-5406 or complete the form below.