I got the idea for this post after I saw the one titled “Can Julius read and analyse spreadsheets with multiple tabs?”.
Besides reading spreadsheets (with multiple tabs), can Julius help you populate tabs with Excel functions? Absolutely!
This can be a bit more finicky so it’s important to keep a few considerations in mind when prompting. Julius writes Python to achieve all these tasks, and therefore operates within those bounds. Some basic knowledge of what to expect can go a long way in reducing the error rates.
The data I will be using here is the superstore data, easily found on Kaggle. It helps me make my point and it is easily accessible to anyone else.
Figure 1. Superstore Dataset
As always, I start by asking for only a part of my overall task to be completed, because Julius operates really well in Steps. I just ask for the Excel workbook to be read and displayed for inspection. An important part here is this:
Hint: don’t use the read_all_sheets method, it will throw an error because some tables are empty. Use some other approach that will let you retrieve all tabs and their names and also let you write functions back into the sheets later on.
Julius can use several Python libraries to handle Excel files and the choice can be very important depending on what you want it to do with this data and what the data looks like. In our case, I want Julius to write Excel functions for me that will actually work when the file is saved and I want it to load a few tabs where some tables might be empty and confusing to the data import process. This hint lets it know ahead of time what the expectation is. In the past I’ve had it tell me it doesn’t have the ability to write to Excel, but that’s because it already loaded the wrong library.
After that comes the next most important step in ensuring that Julius correctly writes Excel functions. After it imports the data from my first tab, it will only know column names but it won’t have a direct link to their positions in the table (like for example how in Figure 1 you can see that “Row ID” is column A). So I want to specifically ensure it creates this mapping so that it can use it later on, instead of doing it on it’s own. I have found that it often messes up column locations and renders most of the functions useless and Excel functions really depend on column locations.
Then, the final step, I ask Julius to fill the second tab, tell it kinda in general what I want there and remind it to use the mapping we just created.I can verify that it does indeed use the mapping that was just created by pulling up the code it writes and checking what the Excel functions look like.
Figure 1. Mapping
As you can see, mapping was used and all the required functions were created that way.
This is what my table looked like before Julius:
and this is after:
This can be further elaborated on, by asking Julius to round the numbers, concatenate, write VLOOKUPs, etc.
Tips for lowering the error rate:
- Work in Steps
- Let it know to skip the read_all_sheets() method to avoid errors
- Map column names to Excel column locations to ensure data integrity
These steps can be added to the general instructions as well, I just opted to leave them in the conversation this time so it is clearer what I did.
You can read my original conversation here.