Using OpenAI’s ChatGPT to Create an Excel Macro for a Real Estate Model
At some point in the past few months, you’ve undoubtedly heard about ChatGPT. It seems everyone is talking about it. But how useful is the tool actually? And in particular, how useful is it to real estate financial modeling professionals?
In this tutorial I show one very real, very helpful application for the engine that powers ChatGPT: OpenAI’s GPT. Use artificial intelligence to write Excel Macros for inclusion in a real estate financial model.
In this video, I create an Excel Macro using OpenAI’s GPT for my Apartment Development Model. The macro performs a residual land value calculation.
The output created from the OpenAI Playground used for the written tutorial.
How Non-Coders Create Excel Macros – Then and Now
As a bit of background. I’m far from a VBA expert (the programming language used to write Excel Macros). I’d consider my proficiency using VBA somewhere in the novice to intermediate level.
And so, up until a couple of months ago, writing a VBA macro in Excel was a chore. I would use a combination of Excel’s Macro recorder, bits of code I could find on the internet, and my own basic knowledge to create a Macro. I would then test and debug it, often using trial and error to arrive at a workable product.
But that’s all changed now, thanks to OpenAI’s GPT artificial intelligence (AI) tool. Rather than trying to write a Macro myself, the AI does it for me. I simply enter instructions in plain English for what I want it to build for me, and seconds later it’s done.
It is incredible!
Of course, the artificial intelligence doesn’t entirely replace the human. Knowing what you want it to write, the nuances of the functions you’re having it use, and the complexity of the subject matter it’s manipulating is still essential.
To get you started using the tool yourself for writing Excel Macros, I recorded a ~20 minute video where I add a residual land valuation calculation tool to my Apartment Development Model. The AI writes the macro, based on my plain English instructions.
Find the video at the bottom of the post.
Written Tutorial – How to Use GPT to Create an Excel Macro
For those who prefer written instructions in addition to the video tutorial, below find written instructions for how to use OpenAI’s GPT tool to create an Excel Macro. Note that, in the spirit of using as much AI in this blog post as possible, I used OpenAI’s GPT to actually write the following tutorial! It’s not perfect, but pretty good.
To write this tutorial, I used the following prompt in the OpenAI Playground: “Write a tutorial for how to use GPT to create an Excel macro.”
Introduction to Creating an Excel Macro Using GPT
GPT is a powerful artificial intelligence (AI) tool developed by OpenAI. It is a natural language processing (NLP) tool that can be used to generate code from natural language descriptions. This makes it a great tool for automating processes and creating Excel macros. In this tutorial, we will walk through how to use GPT to create an Excel macro.
Step 1: Gather Necessary Information
Before you can use GPT to create an Excel macro, you need to gather some information about the task you want to automate. This should include a description of the task, the inputs and outputs of the task, and any additional information that may be needed. Once you have this information, you can use it to create a natural language description of the task.
Step 2: Create a Natural Language Description
Once you have gathered the necessary information, you can use it to create a natural language description of the task. This should include a detailed explanation of the task, including the inputs, outputs, and any additional information that may be needed. This description should be written in a way that a human would understand, so that GPT can interpret it.
Step 3: Use GPT to Generate Code
Once you have created a natural language description of the task, you can use GPT to generate code from it (Spencer added: go to openai.com, sign up for free, and once signed in go to the ‘Playground’ to add your natural language prompt). GPT will take your description and generate code that can be used in an Excel macro. You can then copy and paste this code into an Excel macro.
Step 4: Test and Debug
Once you have generated the code and copied it into your Excel macro, you should test it to make sure it works as expected. If it does not work as expected, you can use GPT to generate additional code to debug the problem.
Conclusion
Using GPT to create an Excel macro is a great way to automate processes and save time. With GPT, you can create a natural language description of the task and use it to generate code that can be used in an Excel macro. You can then test and debug the macro to make sure it works as expected.
The actual AI prompt I used to create the Excel Macro
Watch the Video Tutorial Where I Use AI to Create an Excel Macro
In the following video, I show you how I use OpenAI’s GPT to create an Excel Macro for my Apartment Development Model. If you’re curious, this is the prompt I fed to the artificial intelligence that created the Macro:
Write an Excel Macro that does the following:
First, changes the value of the cell named Land_Value to 0
Then, uses the Goal Seek feature to set the cell named Dev_Spread to be equal to a value the user enters into a dialog box, by changing the value in the cell named Land_Value.
Finally, name the Macro Residual Land Value.
Let me know if you come up with other ways to use AI to improve real estate financial modeling.
Other use cases for GPT in Commercial Real Estate
I continue to explore other use cases for this technology in commercial real estate. As I find time, I’ll create follow-up tutorials/videos just like this. View our growing AI in Real Estate series page to explore those other use cases.
Frequently Asked Questions about Using GPT to Create an Excel Macro for a Real Estate Model
What does the Excel macro created by GPT do in the tutorial?
The macro sets the Land_Value cell to 0, then uses Excel’s Goal Seek to adjust Land_Value until the Dev_Spread cell equals a user-defined value. The macro is named “Residual Land Value”.
How does GPT help someone who doesn’t know VBA?
GPT allows non-coders to write VBA macros by simply describing what they want the macro to do in plain English. GPT then returns working VBA code that can be pasted directly into Excel.
What is the process for using GPT to write an Excel macro?
Describe the task in natural language.
Input the description into GPT (e.g., via the OpenAI Playground).
Copy the outputted code into Excel’s VBA editor.
Test and debug the macro if needed.
Do you need to know VBA to use GPT for macro creation?
No, but having basic knowledge of Excel and the logic behind what you’re trying to automate will improve the results and your ability to debug or refine the code.
What tools are required to use GPT for writing macros?
You need access to GPT via a platform like OpenAI’s Playground, and Microsoft Excel with the Developer tab enabled to paste and run the generated VBA code.
Where can I see the video tutorial?
The video tutorial showing how GPT is used to create the macro is included at the bottom of the original blog post on Adventures in CRE.
Is this macro specific to apartment development models?
The macro was built for an apartment development model, but the technique shown can be adapted to other real estate financial models with similar variables.
What is a good prompt example for GPT to write a macro?
The prompt used in the video was:
“Write an Excel Macro that does the following:
Set cell named Land_Value to 0.
Use Goal Seek to set Dev_Spread to a user-defined value by changing Land_Value.
Name the macro ‘Residual Land Value’.”
