Lofty Chakrabarti

Go to background by giving a brief introduction about yourself, definitely. Um, I can give a brief introduction about myself. Myself, and, uh, I am associated with decimal point analytics from past, uh, three and a half years. Like, I'll be completing 4 years by December this year. Um, I have had holistic experience on managing machine learning projects and Fintech projects. I constructed mobile apps, responsive web designs, uh, progressive web apps, and native apps of the core, uh, finance part of mutual funds and hedge funds and blockchain. And, um, yeah, header a hash graph and all these technologies. Uh, apart from that, I have also managed real estate projects. I've written research reports. I have handled clients and have done, uh, stakeholder management. Prior to this, I was working with Prop Equity Analytics as a data analyst where I have, uh, managed a broad spectrum of 25 plus clients into the BFSI sector. Uh, written research reports, handled tons of data, did visualization, uh, data crunching, uh, research reports, client places visit, presentation making, and so on.

Uh, in leveraging BBA within Excel, what are you what are your preferred methods for error handling to ensure robustness in financial models? See, uh, I have written a lot of BBA codes, uh, and now through the help of chat GPT, it it has become quite easier. But when you're trying to, you know, sort of, um, you know, leverage the VBA technology within Excel, I think the work gets pretty much easier. The error handling methods could be, uh, when you just run the code, Uh, there are if the code stops, it throws you errors. Okay. And when it throws you errors, you just get into the code in order to understand where the error is. Maybe there is an error into the path. Maybe there is an error into the code itself. Like, there are color changing b b o 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, uh, the formula through the formula, like, the weighted average of prices based on your, uh, uh, sales amount or based on the count of sales, uh, or, you know, you can just automate certain things where the, you know, information gets fetched into the other column, uh, right from the time the data is uploaded. So when you just run the model, when you just run the code, uh, you get to realize that, um, 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, um, macros on financial models, uh, on mutual funds data, on the private equity data, uh, also on the real estate data, uh, this is, uh, a very easy task, especially with the invent of chat gbt.

Describe a scenario where you optimized complex SQL query for a fund accounting report. Okay. Uh, so, uh, it was a project of mutual funds, and I was supposed to write a query, a complex query, wherein in Microsoft SQL. And, uh, so what I did was, first, I made a uniformed modeling language, unified modeling language, u u m UML. Uh, for that, you need to be very, very well versed with the with the tables and columns and primary keys and foreign keys. So once it it was done through the documentation in a fintech product, what I did was, um, in Microsoft SQL, I created a table under several headings of different reports, like transaction reports, uh, asset under management, and client details. Uh, and I wrote queries on, um, the joints, like, inner join, outer joins. Uh, and on top of that, I had also, uh, you know, drilled it down through in an ascending order to show the percentage of AUM calculation.

What strategies do you employ to debug and fine tune, uh, VBA code for performance and maintainability in complex Excel workbooks. Okay. Uh, so, see, um, debugging and fine tuning of VBA codes have become pretty much easier with the invent of chat GPT as I've said. Uh, and everything should be written under the code. So the there there is no defined predefined strategy to write VBA codes. As I have said, uh, uh, that everything should be under private sub. And in order to fine tune the code, you can analyze where exactly it went wrong. Maybe with the formulation or, uh, with the, uh, the path that you're giving. Most of the challenges come there itself. So given, uh, a kind of any any kind of complex exercise, which I have also done previously, I can send it out to you. Uh, it is far more easier to, uh, to analyze and debug and send them off within a very short period of time. Like, I have sort of, written BBA codes within, uh, within a day, you know, complex BBA codes. And during that time, even chat gbt was not there. So I think there is no clear strategy. 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, uh, the 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, uh, 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, uh, these are the methodologies like combination of several experience.

Scenario based, the client has reported issues with their financial reports being slow to generate. Describe your approach of of optimizing the SQL queries in Excel DBA to resolve the issue, ensuring minimal downtime. Uh, okay. If there are issues with the financial reports being slow to generate, I think, um, the approach to optimize the SQL queries of Excel VBA code would be, um, you know, I think, uh, as I've said earlier, it is pretty much easier to use a combination of technologies, which are again available free, of course, Microsoft Power Automate as I've mentioned earlier to capture the financial report from a system or writing down a web scraper to capture the financial information and then implementing the VBA queries, be it, uh, inner joins, upper joins, left joins, right joins, or, you know, minimum function or average function or VBA code which is supposed to be under private sub, and end sub. I think, uh, it is pretty much easier to, uh, reduce the downtime.

Describe the situation where a thorough understanding of fund accounting can improve the deployment of new SQL database. Yeah. Definitely. I think, uh, thorough understanding of not only fund accounting, but whatever industry that you're associated with. Like, previously, I had not worked with the mutual fund sector in India, but I, uh, read the ANSI regulations holistically before, you know, actually starting the project, you know. There's a when whenever a fintech product comes into picture, what do you do? What what do the analyst actually do? They study the guidelines. They study the, um, you know, the the regulatory bodies. They study the whole scenario. What is the asset under management? As you talked about fund accounting, it is very, very useful for us to know the accounting standards and principles and, uh, the financial market. The corp corporate finance part. Even the personal finance is affecting the corporate finance for this. I've been working, uh, with, uh, one of the top asset management companies is my client, uh, and their fund accounting played an important role. So understanding the financial ratios, especially understanding the equity market, the technical, uh, and the fundamental understanding. All these things can drastically help the deployment of new SQL databases because you understand the database. And when you just understand the database, it is pretty much easier for you to make tables, perform joints on those tables, understand the primary keys and foreign keys and all these concepts, and then perform the crunching part. Like step by step it has to

Can you just demonstrate how to use VBA for automating reconciliation process and fund accounting? Yeah. Definitely. I have performed, uh, a sit under management reconciliation. And, uh, see, for reconciliation, it is pretty much imperative to have 2 databases. Without 2 databases, you can't 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 2 sets of data. 1 was available from the BSC star, which is, uh, one of the greatest transaction aggregators in India, and the other one was the, uh, RTA that is, uh, registrar and transfer agents file. So I wrote a VBS script to, um, fetch the AUM on a particular date and match the AUM of that but fine date with the RTA files and understand the difference between the 2. If there are fallacies that gets captured and the model is trained in such a way that, uh, if BSC star is throwing an error or or a mismatch in 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, uh, 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, uh, SQL databases gets, uh, automatically updated.