Our client, a leading custodial services and financial management company, was looking to update their processing around securities lending. We developed a system to store the information about all broker-dealers, agency-lenders, securities and stocks, loans, earnings and other financial information required in the course of operating a securities lending business.
Challenge
The Collateral/Securities Lending initiative is a back-end project, for the development of a data warehouse across the United States and United Kingdom. This mutual fund accounting system processes loan and collateral information at the end of business day.
Typical processes include a series of validations, transformations, and aggregations to derive the data required to update asset and loan information. The feeds for inventory, loan, earnings, security data, investment, fund, group and account information are loaded into they application from systems like DML, MCH, BRS, SSgA, LCM/NCE etc.
The processed data is then used by internal paradigms that relate to agency lending disclosures and securities transaction automated risk analysis for the necessary calculations and reports.
Mutual fund accounting system data is subsequently used for client reporting in the form of SLPR On-Demand and SFIR Scheduled Reports. This data is subsequently sent to various external clients via client feed at various levels of data granularity.
This system enables users to:
• Calculate the audited/unaudited earnings for all loan allocations at fund level
• Analyze the non-cash collateral funds
• Use it as a repository for all types of earnings/expenses and income at daily/monthly levels
• Generate daily/monthly reports after the data loads
Solution
Our team participated in full system development life cycle including design, coding, testing, implementation, documentation, application maintenance and support. We performed analysis, developed PL/SQL scripts for complex business requirements and SQL scripting, tuning and optimization (schema and SQL) and data quality issues fixing. Our team developed data access and data load processes throughout the application.
We used the following techniques and technologies to provide the Client with required functionality: B-tree, bitmap, function-based indexes creation depending on execution plan analysis; JavaScript/ExtJS(2-4) technology for GUI frontend, starting 2015 we opted for React JS; Java, C++ and XML/XSLT for custom service engine part (data processing, web service).
The system provides users with Jasper-based reports generating on demand. SLD receives feeds from various upstream systems for a given business day. All the feeds are processed through ETL concepts; data is loaded into respective core Oracle tables for further report generation. Loan and earnings data for particular business day is processed through batch process driven by shell scripts.
Result
Our team has been developing and supporting mutual fund accounting systems for over 7 years. This application allows the securities lending team to access information for generating daily and monthly reports. Feeds received by the system from other sources provide detailed, granular data for loans booked, earnings on loans, security level information, investment vehicle, investment pools and borrower details.
Users can generate the reports as they require.
Moreover, users can easily track trades and settlements, and manage their trade risk.
Tools and environment:
Subversion: Git; Front-end: JavaScript (ES6, ES7), NodeJS, ReactJS; Package management: WebPack, npm, Grunt; Unit tests: Karma, Jasmine; Documentation: ESDoc; Styles: LESS, BEM-methodology; Data processing: JSON, Web/REST Services; Back- end: Java SE/EE, JDBC, JSP/Servlet; DB: Oracle PL/SQL, Oracle 10g/11g; OS: Linux, Windows.