A NeuGroup member shares his company’s success using data analytics and automation to optimize portfolio returns.
Data analytics and automation are increasingly popular tools for corporates looking to optimize the management of their investment portfolios. At a recent meeting of NeuGroup for Cash Investment 1, sponsored by Lord Abbett, two members from a Silicon Valley technology company presented on how they led a four-month project leveraging Python to pull critical data into an Excel spreadsheet on a daily basis to monitor the company’s CD and commercial paper investment portfolio—saving approximately 40 minutes a day.
- Multiple other members at the meeting were surprised by how quickly the member was able to implement this project, as well as how smoothly the Python script interfaces with the Bloomberg Terminal.
- “By using Python, we’re able to save time and improve yields by quickly analyzing thousands of tickers and providing our portfolio managers with real-time data on market conditions and investment opportunities,” said the treasury manager who coded the solution.
A snapshot ready in a snap. In the past, treasury used Excel to execute the daily process of analyzing the company’s investments in daily commercial paper and CDs, including breakdowns by sector, maturity, rating, country and issuer.
- “Each morning, before we look at what’s available on the CP and CD market, we run this report so that we know what industries, issuers, countries, and financial vs. non-financial investments we can focus on—and which we should filter out if we are already fully allocated,” the treasury manager said.
- The previous process was time-consuming, prone to errors and had to be executed early in the morning, so traders could have access to data when markets opened; this was especially difficult for a team located on the West Coast
- Prior to switching to the new approach, treasury staff had to paste Clearwater data into Excel and manually scan it for any errors that prevented Excel from completing its calculations, which could take up to half an hour. Errors could occur due to corrupt data inputs or an issue with Excel’s connection to Bloomberg.
- Now, each morning, the treasury team still downloads data from Clearwater but simply executes the code once it is downloaded, which takes less than two minutes. The code generates individual tables in a new Excel file based on the portfolio’s performance.
- The treasury manager said that the impact of the seemingly small automation adds up in a big way. This process could take up to 40 minutes each trading day, which adds up to 21 full working days of productivity a year lost to the manual task. “The impact of time saved is greatly magnified and improves the overall investment process,” he said.
Extracting from Bloomberg. Meeting participants were particularly impressed by the seamless connectivity with Bloomberg. While there is an existing API that transfers most of Bloomberg’s data directly into Excel, it does not include a crucial subset of the data that’s required to get a complete view of the portfolio’s performance.
- “On the ‘BOOM’ screen, there is no way to [fully] connect, you literally have to click download,” the treasury manager said. “I decided that was frustrating, so I created a way for Python to automatically enter the command to download that, so as long as you’re logged into the terminal, you don’t have to do anything else.”
Critical success factors. The treasury manager who coded the project, along with the senior treasury director who oversees the company’s investment portfolio, outlined the following steps for building a successful automation.
- Talent. Even before the start of the Python project, the senior director was pushing for more technical solutions to be developed within treasury. This required acquiring or developing a specific skill set, which the company approached by emphasizing programming proficiency in new hires and providing training to existing staff. The in-house creation of the Python script is evidence of the success of this approach.
- “All of us started taking Python classes,” the senior director said. “I’ll be the first to admit I didn’t get anywhere near proficiency, but what it allowed me to do as the portfolio manager is to be able to communicate—I now know what Python can do and what it can’t do.”
- Innovation. After ensuring everyone had a base-level knowledge of programming languages, the team met regularly to identify additional manual tasks that were ripe for automation.
- The treasury manager who coded the program said he was inspired to launch the portfolio snapshot project by conversations at NeuGroup meetings.
- Execution. It’s also essential to ensure that the project lead has the capacity to execute on it, which sometimes can mean prioritizing it over day-to-day activities. It took the treasury manager an average of 12 hours of work per week over the roughly four months of development time.
- Continuous improvement. The work does not end with implementation. To ensure the solution remains effective, and to see how it can be improved, the team has weekly meetings to discuss the status of the project.
- “I show the team what I’ve done, and they can share ideas for enhancements and fixes,” the coder said. “It’s a continuous process of every week, people giving feedback, working on that, and building it out. It’s been a valuable process for us, and we’re excited to see what the future holds.”
