Hackathon 1: Series 1 - Use Case 2 | AI in Excel for Chartered Accountants | CA Shantam Agrawal

Summary

In this session, Shantam Magal demonstrates how AI, specifically ChatGPT, can enhance Excel by creating custom formulas using VBA. He illustrates how to automate tasks like extracting phone numbers from bank statements with UPI transactions and calculating the frequency of phone numbers across transactions. Additionally, he shows how AI can generate formulas for summing only odd or even numbers, providing practical applications to streamline data processing. This session highlights the potential of ChatGPT in automating Excel tasks, saving time, and improving efficiency in everyday work.

Takeaways

• 😀 The speaker, Shantam Magal, demonstrates how AI, specifically ChatGPT, can enhance productivity by creating custom formulas in Microsoft Excel.
• 😀 Custom Excel formulas can be generated using ChatGPT and VBA (Visual Basic for Applications), helping automate complex data analysis tasks.
• 😀 An example is given where ChatGPT helps create a custom formula, `GET PHONE`, to extract 10-digit phone numbers from bank statements with UPI transaction data.
• 😀 The custom formula `GET PHONE` can be used in Excel to automatically extract phone numbers from cells, saving time in data processing.
• 😀 ChatGPT can generate code that can be pasted into Excel’s VBA editor (Alt + F11) to create custom Excel functions that are not natively available.
• 😀 The speaker demonstrates using Excel’s `COUNTIF` and `UNIQUE` functions to count and summarize which phone numbers appear most frequently in the dataset.
• 😀 Custom formulas can be applied to large datasets, such as bank statements, to quickly analyze transaction trends without manual intervention.
• 😀 ChatGPT also generates custom formulas to perform specialized tasks in Excel, such as summing only odd or even numbers from a dataset.
• 😀 The process of creating a custom formula involves: generating the code with ChatGPT, inserting it into Excel via VBA, and applying it to the data.
• 😀 The use of custom formulas generated by ChatGPT allows users to automate tasks like extracting phone numbers or summing odd/even values, which would otherwise require manual effort and be prone to error.

Q & A

What is the main topic discussed in the video?

The main topic is how AI, particularly ChatGPT, can be used to create custom Excel formulas using VBA, automating tasks like extracting phone numbers from bank transaction data.

Why is AI integration into Excel workflows beneficial?

AI integration, such as using ChatGPT to create custom formulas, helps automate repetitive tasks, reducing manual effort and time, and allows users to handle complex data more efficiently.

Can Excel’s native functions perform the same tasks as the custom formulas created using AI?

No, Excel does not have native functions for specific tasks like extracting phone numbers from transaction data or summing only odd or even numbers. Custom formulas generated by ChatGPT fill these gaps.

What is the specific use case demonstrated in the video?

The specific use case involves a bank statement where most transactions are made through UPI. The custom formula extracts phone numbers associated with these transactions to identify which numbers were used most frequently during the financial year.

How does the custom 'GetPhone' formula work?

The 'GetPhone' formula, created through ChatGPT, scans a cell for a 10-digit number (representing a phone number) and extracts it. This is done by using VBA to loop through the cell's contents and capture the digits.

What are the steps to use the custom formula in Excel?

To use the custom formula, you first need to open the Visual Basic editor by pressing Alt + F11, insert a new module, and paste the generated VBA code. Then, you can use the formula directly in your Excel sheet.

How can we count the frequency of specific phone numbers using the custom formula?

Once the phone numbers are extracted using the custom 'GetPhone' formula, you can use Excel’s 'COUNTIF' function to count how often each phone number appears in the dataset, helping identify the most frequent transaction numbers.

What is the additional use case demonstrated with summing odd and even numbers?

Another example shows how ChatGPT can create a custom formula to sum only odd or even numbers in a dataset, addressing the limitation in Excel where such specific calculations are not available by default.

What is the 'TotalOdd' formula and how does it work?

The 'TotalOdd' formula, created by ChatGPT, scans a range of cells and sums only the odd numbers. It works by checking each number's modulus with 2 and adding it to the total if the result is not zero (indicating an odd number).

How can the data be sorted based on the frequency of phone numbers?

After counting the frequency of each phone number, you can sort the data using Excel's 'Sort' function. By selecting the range of phone numbers and their counts, you can sort them in descending order to see which phone number was used the most.