Streamlining WSO Reporting and Automating Accounting Workbook Creation for Efficient Month-End Close

In investment and asset management environments, accurate and timely financial reconciliation is crucial for ensuring reliable and compliant financial statements. Wall Street Office (WSO) is a key system for tracking positions, asset values, and cash flows, but for many finance teams, the reconciliation of WSO data with the admins can be complex and time-consuming. Traditionally, WSO does not have a built-in cut-off date for monthly or period-end reporting, which complicates the close process.

To overcome this challenge and optimize financial reporting workflows, I developed an automated solution that streamlines accounting workbook creation and accelerates the reconciliation process. This solution integrates WSO reports with Microsoft Access, enabling fund accountants to utilize comprehensive accounting workbooks for journal entry (JE) creation, ensuring accurate period-end accounting even without a fixed cut-off date in WSO.

Here’s a breakdown of the process that automates WSO report extraction, accounting workbook creation, and reconciliation, ultimately improving the accuracy, speed, and efficiency of the month-end close.

The Challenge: No Fixed Cut-off Date for Period-End in WSO

One of the core challenges finance teams face when using WSO for asset and position tracking is the lack of a fixed cut-off date for monthly or periodic reporting. In systems with cut-off dates, the financial data for the period being closed is finalized, ensuring that reconciliations and reports reflect only the activities within that period. However, WSO does not inherently offer a cut-off mechanism for monthly close, making it difficult to execute period-end accounting processes without manually handling data discrepancies.

This presents a challenge for closing out periods efficiently because the finance team must manually pull reports for each fund and adjust for any discrepancies between the reporting period and the actual close date.

The Solution: Automated WSO Reporting and Accounting Workbook Creation Process

To address these challenges, I developed an automated accounting workbook creation and WSO reconciliation process using Microsoft Access. This solution integrates WSO reports at the portfolio level, automates the creation of accounting workbooks, and ensures that all necessary financial data is available for reconciliation and reporting — even without a defined cut-off date in WSO.

Key Features of the Automated Process:

1. Automated WSO Report Retrieval
   The process begins with an automated job that retrieves all necessary WSO reports using API integration. This job is scheduled to run on the 5th business day of each month (BD5), ensuring that the most up-to-date data is retrieved in alignment with the month-end close schedule. The reports are automatically saved to a read-only shared folder to ensure data integrity and prevent unauthorized changes.

2. MS Access Database Integration
   Data from various WSO reports — such as Book Value Positions, Accrued Income, Ledger Account Detail, and Miscellaneous Fees — is stored in a centralized ‘Master’ Access database. This database contains several linked tables, each corresponding to a different WSO report for the current period, the prior period, and the prior-period rerun (PMRR) reporting periods. These linked tables allow the system to maintain multiple views of the same report across different reporting cycles, enabling accurate period-over-period comparisons, retroactive activity calculations, and fund-level reconciliation. The Access database consolidates the data, allowing analysts to access the latest financial information for all portfolios and funds.

3. Dynamic Mapping Tables for Accurate Reconciliation
   The process leverages several mapping tables within the Access database, which automatically link important data points, such as:

   • Asset Types by Position

   • Currency Types

   • Portfolio and Ledger Names

   These mapping tables ensure that the right data is associated with each portfolio and ledger, enabling accurate reconciliation and accounting workbook creation.

4. Automated Accounting Workbook Creation
   Once the necessary reports are retrieved and stored in the database, the process automatically generates accounting workbooks for each fund using VBA (Visual Basic for Applications). These workbooks include:

   • Summary Reports

   • Detailed WSO Reports

   • Reconciliation Data

   The VBA-driven automation compiles the data into a structured workbook for each fund, saving analysts significant time and ensuring that the data is accurate and consistent.

5. Fund Accountants Utilize Workbooks for JE Creation
   Once the accounting workbooks are created, fund accountants can use them to generate journal entries (JEs) in line with the fund’s specific financial reporting requirements. The workbooks provide the necessary data, including financial values, asset positions, accrued income, and other essential financial information, making it easier for accountants to generate accurate JEs for each fund.

6. Reporting & Transparency
   The solution provides finance teams with a comprehensive accounting workbook, which acts as a centralized repository, allowing analysts to quickly assess fund-level financial data, identify variances, and review key metrics before diving into the detailed WSO reports contained within the workbook.

How This Process Solves the Cut-off Date Issue

By automating the process of pulling WSO reports and integrating them into a centralized database, this workflow ensures that the monthly reconciliation process can proceed without needing a specific cut-off date. Here’s how the system works to ensure a smooth month-end close:

• Automated Data Retrieval on BD5: The process pulls the latest available data from WSO on the 5th business day of each month, ensuring that finance teams can reconcile financial data for the current period without relying on a fixed cut-off date.

• Flexible Reporting Periods: With the automated API process, reports are pulled for current month-to-date (MTD) and year-to-date (YTD) periods, allowing for a rolling period to be tracked without needing to wait for a set cut-off.

• Integrated Workbooks for JE Creation: The system generates detailed accounting workbooks that fund accountants can then utilize to generate journal entries, ensuring a timely period close even without a fixed cut-off date in WSO.

This solution ensures that finance teams can perform period-end reconciliations efficiently, even in the absence of a traditional cut-off date, making it easier to close the books for each reporting period.

Retroactive Activity Calculations: Comparing ITD Current and Prior Periods

An additional feature of the Access database is its ability to calculate retroactive activities by comparing Inception to Date (ITD) data for the current period with the ITD data from the prior period.

This comparison allows the finance team to track changes in positions’ financial data that may have occurred due to back-dated adjustments in the prior period’s close, helping to identify any discrepancies that need to be addressed. The database automatically calculates the differences between the two periods and generates a retroactive variance report, which can then be used to create retroactivity journal entries where necessary.

This process allows fund accountants to clearly segregate retroactivity from the actual financial activity occurring in the current period, ensuring accurate reporting and proper classification of adjustments..

The Benefits:

1. Faster Month-End Close
   With the automated process, month-end reporting is completed in less time, allowing finance teams to close the books more quickly and efficiently.

2. Reduced Risk of Errors
   Automation minimizes the potential for human error by eliminating manual data entry and calculations, ensuring that the reconciliation and accounting workbooks are accurate.

3. Increased Transparency
   The accounting workbook provides clear visibility into the financial data, making it easier for auditors and stakeholders to review and verify the results.

4. Scalability
   The process can easily scale to accommodate additional funds or portfolios without requiring significant manual intervention.

5. Accurate Retroactive Adjustments
   The system’s ability to compare ITD current and prior period data ensures that any adjustments made after the previous close are accurately captured and reflected in the financial reports.

6. Efficient Fund-Level Accounting
   The VBA-driven automated workbook creation ensures that every fund’s data is compiled accurately and efficiently, saving analysts significant time and reducing errors.

Conclusion: A More Efficient Way to Close Periods

By automating the reconciliation of WSO reports using Microsoft Access and streamlining the creation of accounting workbooks, finance teams can now perform timely month-end closes and achieve period-end accounting with greater accuracy and efficiency. This solution enhances financial visibility, reduces reconciliation errors, and ultimately supports more reliable and timely financial reporting across all funds, regardless of the lack of a fixed cut-off date in WSO.

If you’re looking to streamline your WSO reconciliation process and ensure timely closes, this solution could be the key to a more efficient and error-free reporting workflow.