---
title: "Mutual Fund Portfolio Analysis with ChatGPT: Merging and analyzing across multiple excel files"
slug: fa18de05
date_published: 2024-02-10T16:01:26.248Z
original_url: https://www.tigzig.com/post/fa18de05
source: migrated
processed_at: 2025-12-02T10:00:00.000Z
---

# Mutual Fund Portfolio Analysis with ChatGPT: Merging and analyzing across multiple excel files

A very common analysis in the investment world is to track changes in equity portfolio allocations within a scheme over time. This often needs to be conducted monthly, and sometimes even weekly or fortnightly, across varying time periods.

This type of analysis requires merging multiple Excel files, typically print-formatted and may contain images, as was in this case.

Will ChatGPT be able to do it? With just prompts. In a replicable manner. Even if I provide files for different months / multiple months? That was the specific use case requested.

Amazingly: Yes

Had prepared a prompt for two files. That worked. Asked it to merge another 10 using same logic. And it did. In one shot. 30 sec. And for one of the files, the name format was also different...ChatGPT handled it seamlessly...!

See for yourself.

Prompt preparation secret: **The main prompt might look fancy and intimidating. I did not type out a single line of that.** I was voice typing using Google docs when doing for the first time. It was iterative. Used to dump the prompt with uuh, aaah, spellings, and horrible formatting into ChatGPT. At the end just asked ChatGPT to prepare a consolidated prompt that I can use, and it will understand. And voila.. the magic prompt...!

---

## Main Prompt

> A very common analysis in the investment world is to track changes in equity portfolio allocations within a scheme over time. This often needs to be conducted monthly, and sometimes even weekly or fortnightly, across varying time periods.
>
> This type of analysis requires merging multiple Excel files, typically print-formatted and may contain images, as was in this case.
>
> Will ChatGPT be able to do it? With just prompts. In a replicable manner. Even if I provide files for different months / multiple months? That was the specific use case requested.
>
> Amazingly: Yes
>
> Had prepared a prompt for two files. That worked. Asked it to merge another 10 using same logic. And it did. In one shot. 30 sec. And for one of the files, the name format was also different...ChatGPT handled it seamlessly...!
>
> See for yourself.
>
> Prompt preparation secret: **The main prompt might look fancy and intimidating. I did not type out a single line of that.** I was voice typing using Google docs when doing for the first time. It was iterative. Used to dump the prompt with uuh, aaah, spellings, and horrible formatting into ChatGPT. At the end just asked ChatGPT to prepare a consolidated prompt that I can use, and it will understand. And voila.. the magic prompt...!
>
> This prompt will only work for HDFC Top 100 Monthly Portfolio disclosures. That was the specific use case I was working on. Next, would be trying to generalize it, so that it can work for monthly portfolio disclosure of any scheme of any fund house. And publish as a GPT or a web app so that there is no need to worry about the prompt. And a user can simply upload files and proceed.
>
> [Monthly Portfolio - HDFC Mutual Fund](https://www.hdfcfund.com/)
>
> While doing it first time had to iterate with ChatGPT to get it right. It took me around an hour and a half to iterate and get the main prompt out. And after that it was just copy paste.
>
> We need to validate, same as we would do had we processed the data with Excel / Python. For instance, validating the totals against main files. I had also asked ChatGPT to prepare a validation summary to make this a bit easier. And also instructed ChatGPT to apply these validation checks on its end.
>
> And if you would rather do it in Python, then you can ask ChatGPT to provide a base code such that you can pass the file names, time periods etc. as variables....offering flexibility on data engineering side.