Lofty Chakrabarti

Go to background by giving a brief introduction about yourself. I can give a brief introduction about myself. I am associated with Decimal Point Analytics for the past three and a half years. Like, I'll be completing four years by December this year. I have had a holistic experience managing machine learning projects and Fintech projects. I constructed mobile apps, responsive web designs, progressive web apps, and native apps for the core finance part of mutual funds and hedge funds, as well as blockchain. And, I've worked with technologies like header a hash graph. Apart from that, I have also managed real estate projects. I've written research reports. I have handled clients and done stakeholder management. Prior to this, I was working with Prop Equity Analytics as a data analyst, where I managed a broad spectrum of 25 plus clients in the BFSI sector. I've written research reports, handled tons of data, done visualization, data crunching, and client visits, as well as presentation making.

In leveraging BBA within Excel, what are your preferred methods for error handling to ensure robustness in financial models? See, I have written a lot of BBA codes, and now through the help of chat GPT, it has become quite easier. But when you're trying to leverage the VBA technology within Excel, I think the work gets pretty much easier. The error handling methods could be when you just run the code, and there are errors. Okay. And when it throws you errors, you just get into the code in order to understand where the error is. Maybe there's an error in the path. Maybe there's an error in the code itself. Like, there are color-changing VBA macros wherein the colors of the cells could be changed or data-crunching VBA macros. Like, I've written macros which can automatically calculate the formula through the formula, like, the weighted average of prices based on your sales amount or based on the count of sales, or you can just automate certain things where the information gets fetched into the other column, right from the time the data is uploaded. So when you just run the model, you get to realize that if the data is not getting captured as you've said as you've mentioned that it should be in Excel. If the data does not get captured in the right way that you want, there could be some possible errors. And especially since I've written macros on financial models, on mutual funds data, on the private equity data, on the real estate data, this is a very easy task, especially with the invention of chat GPT.

I was working on a project for mutual funds, and I was tasked with writing a complex query in Microsoft SQL for a fund accounting report. To start, I created a unified modeling language (UML) diagram to understand the tables, columns, primary keys, and foreign keys involved in the project. Once the documentation was complete, I created a table in Microsoft SQL with several headings for different reports, such as transaction reports, asset under management, and client details. I then wrote queries using inner and outer joins to combine data from these tables. Finally, I sorted the results in ascending order to display the percentage of AUM calculation.

What strategies do you employ to debug and fine-tune VBA code for performance and maintainability in complex Excel workbooks? Okay, so, see, debugging and fine-tuning of VBA codes have become pretty much easier with the invention of chat GPT as I've said. And everything should be written under the code. So, there is no defined pre-defined strategy to write VBA codes. As I've said, that everything should be under a private sub. And in order to fine-tune the code, you can analyze where exactly it went wrong. Maybe with the formulation or with the path that you're giving. Most of the challenges come from there itself. So, given a kind of any complex exercise, which I have also done previously, I can send it out to you. It is far more easier to analyze and debug and send them off within a very short period of time. Like, I have sort of written VBA codes within a day, you know, complex VBA codes. And during that time, even chat GPT was not there. So, I think it's just your attention to details that makes you stand out from the crowd.

What methodologies would you apply to optimize a VBS script for processing large datasets in Excel without impacting the user experience? So to maintain large datasets, I think, the strategy that I have personally felt and it should be used would be a combination of either a power query in Microsoft Excel that I have also done following a waterfall methodology in one of my automation projects, and also Power Automate. Like, you are capturing data from a certain PDF by using Microsoft power automate. And once the data comes into the Excel file, then writing down a VBA code for crunching the data. So I think, these are the methodologies like combination of several experience.

If there are issues with the financial reports being slow to generate, I think the approach to optimize the SQL queries in Excel VBA code would be, as I've said earlier, to use a combination of technologies available for free, such as Microsoft Power Automate to capture the financial report from a system or a web scraper to capture the financial information, and then implement the VBA queries, including inner joins, left joins, or minimum and average functions, or VBA code under private sub and end sub. It is pretty much easier to reduce the downtime.

A thorough understanding of fund accounting can improve the deployment of a new SQL database because it requires a holistic comprehension of the industry, including the regulatory environment. For instance, when working on a fintech project, analysts study the guidelines and regulatory bodies to understand the scenario. Understanding fund accounting is also useful for knowing accounting standards and principles, as well as the financial market, including corporate finance and its impact on personal finance. This knowledge can be applied to a top asset management company, where fund accounting played a crucial role. Understanding financial ratios, particularly the equity market, technical, and fundamental analysis, can significantly aid in the deployment of new SQL databases. This is because a deeper understanding of the database enables the creation of tables, performing joins, understanding primary and foreign keys, and ultimately, data crunching.

Can you just demonstrate how to use VBA for automating reconciliation process and fund accounting? Yeah, definitely. I have performed a sit under management reconciliation. And, see, for reconciliation, it is pretty much imperative to have two databases. Without two databases, you cannot perform reconciliation. So, I would just give you a scenario where I was performing AUM reconciliation. That is, asset under management reconciliation. So, there were two sets of data. One was available from the BSC star, which is one of the greatest transaction aggregators in India, and the other one was the RTA, that is, the registrar and transfer agents file. So, I wrote a VBA script to fetch the AUM on a particular date and match the AUM of that file date with the RTA files and understand the difference between the two. If there are discrepancies that get captured and the model is trained in such a way that if BSC star is throwing an error or a mismatch in terms of numbers with the RTA files, the numbers from the RTA files would get auto fetched and filled into the database. Maybe the Excel database which goes into the SQL database through an automation script, which can be performed through Microsoft Power Automate or we can write down a web scraper as well. Or it could be put into a path through which the SQL databases get automatically updated.