Excel-lent Conversations: Unleashing the Power of ChatGPT in Spreadsheets

-

In the dynamic world of data, Excel’s Copilot feature emerged as a beacon of innovation. It brought with it the promise of transforming our interaction with spreadsheets. Yet, the true potential of Copilot was not just in conversation but in elevating Excel’s analytical prowess.

Copilot, much like a guide, provided users with a broad understanding of their data. But the real magic can happen when this feature is made available at the cell level using the old school VBA and the ingenuity of the Azure OpenAI API. This powerful combination can propel the Excel to new heights, enabling detailed, row-by-row analysis that delved deep into the data.



Imagine having a comprehensive list of customer feedback. With this VBA+Copilot, you could now extract nuanced insights, understand the depth of customer sentiments, and extract their future needs—all neatly detailed in adjacent columns.

Here’s how to harness this power:

  1. Develop a custom Copilot Subroutine and integrate it with the OpenAI API using SystemPrompt and UserPrompt.
  2. The SystemPrompt defines the analytical tasks for ChatGPT.
  3. The UserPrompt identifies the specific cell data for ChatGPT’s in-depth analysis, such as UserFeedbacks.
  4. Now, extract the refined response into a new column from the HTTP.Response.

And for the finer details:

  1. Address special characters in cells, like \n, that JSON may misinterpret by creating a JsonEscape function.
  2. Apply VBA-JSON to parse the HTTP Response, extracting a user-friendly response with JsonConverter.ParseJson.
  3. Navigate the token limit per minute with tactical retries and a 60-second delay to throttle the API calls.
  4. Utilize VBA forms and the Statusbar to enhance the user experience, collecting column information and displaying the API’s status during the analysis process, which may take a while to complete.

This narrative showcases how Excel, powered by time-tested VBA and Azure OpenAPI, can not only listen but also analyze with unprecedented depth with excel-lency. It’s a testament to how the integration of classic and contemporary technologies can lead to groundbreaking advancements in data analysis. And the icing on the cake? You can direct Copilot to craft the VBA code for you, making this not just a story, but a reality of efficiency and innovation. Refer Build natural language solutions with Azure OpenAI Service - Training | Microsoft Learn and GitHub - VBA-tools/VBA-JSON: JSON conversion and parsing for VBA for more details. 

Comments

Post a Comment

Popular posts from this blog

Are EVs eco-friendly in India?

-
Image
For sometime, the thought troubled me. Electric Vehicles get the juice from power grid so are they really eco-friendly or its just a marketing stunt. Lets analyze: We need 20KWh at the production site to charge EVs for 100 km run. A coal based thermal plant (in India) emits 980 grams of CO2 per 1 kWh as per CEA  considering Gross Calorific Value and Station Heat Rate. So 100 KM by EV emits 19.4 KG of CO2. And the same 100 m run by a Petrol hatchback will emit 13.2 KG ! Oh my my!!! However, Indian grid does not provide power only from coal based thermal plant. It's still 80:20. Simple algebra will tell us we need 68% power from thermal and remaining from renewable to match the emission level of Petrol Hatchback.  But renewable need balancing plants to mitigate variability in grid. Normally gas based plants are used for this purpose. Thankfully gas only emits 50% of CO2 than that of coal based plants. So we need renewable plants towards 60% to make EVs "ECO-FRIENDLY". Long
Read more

Field force tracking app - free app made using Google services in weekend !

-
Image
There are many SMEs who may want to track their field force but such application development is not so pocket friendly. One such company reached out to me and I took it as a hobby project. To my surprise, it came out so nicely that kept me thinking how powerful Goggle services are.  Customer required  Configurability in drafting information the company will like to capture during field activities Geo tagging field activities  Dashboard and analytics Solution: Google form was at the center in creating the solution. Google form integrated with Google Script was able to Geocode and reverse geocode. This link was then opened from native Android App using webview. Pre-filled form gave convenience to ground users of not filling redundant info every time e.g. employee details. Google form was then integrated with Sheet and then Map to create complete GIS based dashboard. Google Sheet charts, maps were then integrated with Google site to create a dashboard for manager.  Google search ensured t
Read more

COVID: LoC and my need of premise to blame

-
Image
Long back in my college, I studied about LoC - not "Line of Control" but "Locus of Control". Locus of Control, introduced by Julian B. Rotter in 1954, explained the degree to which individuals attribute success or failure to themselves (internal LoC) or to others (external LoC).  It is also found that people with internal LoC are happier than people with external LoC. And I see many unhappy people in social media during this COVID period blaming others. Even I would like to do so but somehow I am not getting right premises. People will say there are many premises readily available: Premise 1: Indian Govt was not at all prepared for 2nd wave! Below graph shows daily COVID cases for different countries (source: worldometer ). Nobody expected such serious scene with such magnitude in India considering wave patterns, summer season and antibodies among people. But the reality was different from projection.  Was Govt. solely responsible for such stiff increase in cases? W
Read more