TFF #012 - How to use ChatGPT with Google Sheets - 3 Finance Use Cases

Read Time - 4 Mins

Hello, and welcome to another edition of the Tech for Finance Newsletter.

In this edition, I’ll show you how to use ChatGPT within Google Sheets, and provide 3 use cases + a BONUS function.

For this to work you need 3 things (all free):

1/ An OpenAI API key - Find one here (requires an OpenAI account)

2/ A Google Sheet - Create one here (required a Google Account)

3/ The ChatGPT for Sheets plugin - Install it here

Once installed you’ll need to enter your API key, and then enable GPT functions.

[NOTE - You will have to Enable GPT functions every time you open a new sheet]

Soooo…

What are the use cases?

1/ Tidying up bad data

2/ Creating checklists for routine activities

3/ Listing information

4/ BONUS function

Here are some examples

1/ Tidying up bad data

This uses the ChatGPT for Sheets ‘FILL’ function.

Imagine you’ve received some badly submitted expense lines. The descriptions are poor.

We want to A, correct the date format, B categorise the expense, C, pull out the customer information, and D, update the GL code we want to post to.

Here’s the badly formatted data:

Next, we need to give ChatGPT some examples of what good looks like.

We can then use the ‘FILL’ function to complete the missing data.

The first part of the formula calls the ChatGPT function
The second part selects the example range
The third part specifies the inputs for the new data to be filled

[NOTE - The current version of ChatGPT for Sheets uses Chat GPT3.5 Turbo. As such there is a limit to how much data it can handle, so don’t expect it to process large quantities of data.

I believe they will be updating the plugin to use ChatGPT-4 soon, but at present this is only available for paid ChatGPT users via chat.openai.com/chat

Therefore, if you’re getting timeout errors, you need to reduce the amount of data, or process the data in chunks]

2/ Creating checklists for routine activities

This uses the ChatGPT for Sheets ‘TABLE’ function.

In this example, we’re wanting to create a checklist for the booking of accruals as part of month-end.

We want the checklist item, a column for the date to be completed, and a column for the individual to complete the work.

Here’s the formula / prompt…

=GPT_TABLE("I am working in finance and need an extensive checklist for the booking of accruals in the context of the monthly closing. Create me a table with a column for detailed checklist items, a column for the date to be completed, and a column for the individual carrying out the task",,,,2000,)

The 2000 at the end of the formula increases the amount of ‘tokens’ used for the response.

In short, more tokens = more detailed response - BUT

The more tokens you use, the slower the response will be and the more likely you are to get timeout errors.

Here’s the result…

Now all you’ve got to do is update the dates and your team members 🙂

The inspiration for this table came from Nicolas Boucher’s ChatGPT for finance guide, which you can find here.

3/ Listing information

This uses the ChatGPT for Sheets ‘LIST’ function.

Let’s imagine we’re in the manufacturing industry, and we’re wanting to build a list of KPIs to work to…

Here’s the formula / prompt

=GPT_LIST("Find me 10 finance KPIs for manufacturing companies")

Here’s the result…

BONUS

We can also use the data ChatGPT has produced in our sheet for other prompts.

So let’s say that we want an industry benchmark for each of those KPIs

Here’s the formula / prompt…

=GPT("Find me a manufacturing industry benchmark for this KPI",A1)

Here’s the result…

PLUS

You can drag the formula down in the same way as you would any other

How cool is that?!?!

IMPORTANT - AI generated data is still not 100% accurate. Please bear this in mind when you’re presenting data to others. It’s always a good idea a good idea to check your results…

So there you go.

ChatGPT in Google Sheets, and right now, this is all still FREE!