NotebookLM Feb 2025

NotebookLM — Research Assistant
In my previous post, I presented how to use PyXLL, Jupyter, and PyQt to build professional quality macros in Excel.
Today, I illustrate my experience using NotebookLM to enhance, or reinforce that last presentation.
Brief Explanation

NotebookLM accepts various sources as input:

  • text documents
  • PDFs
  • webpages
  • audio files
  • YouTube videos
  • etc

NotebookLM can produce the following output:

  • summary document
  • briefing document
  • study guide
  • audio overview

I experimented with NotebookLM and now post my results. Please note that each of the red boxes below were produced entirely by NotebookLM. I did only one simple thing: I entered the URL of one of my blog posts; NotebookLM did the rest.

Audio Overview (by NotebookLM)

Audio Overview transcript (also by NotebookLM)

Briefing Document (by NotebookLM)
Briefing Document: PyQt, PyXLL, and Jupyter Macros in Excel

Date: January 29, 2025 (Based on the assumption that “Feb 2025” is referring to a future date, and that the blog was published prior.)
Source: Excerpts from “PyQt PyXLL Jupyter macros Feb 2025 – Hoitsma Blog”
Author: khoitsma

  1. Overview

This blog post by khoitsma focuses on demonstrating how to create and use powerful Excel macros using a combination of Python, the PyXLL library, Jupyter notebooks, and the PyQt (or PySide2) framework. The core idea is to move beyond the limitations of VBA macros by leveraging Python’s capabilities for complex calculations, user interface design, and data manipulation. The author presents this through a video demonstration which includes a simple macro, and a far more complex macro with a custom user interface.

  1. Key Technologies and Concepts
  • PyXLL: This library is a crucial bridge, enabling Python code to be executed within Excel. As the author notes, “PyXLL code is not written or saved in the Excel worksheets; this means that the code is universally available to all of a user’s worksheets” . This makes the code reusable across workbooks.
  • Jupyter Notebooks: These interactive environments are used to develop and test the Python code that will eventually become the Excel macros. The author uses a Jupyter notebook to define two macros.
  • PyQt/PySide2: This Python binding to the Qt framework is used to create custom graphical user interfaces (GUIs) for the macros. The author uses “trial_pyqt_des_exp_wind_2a” which shows an example of this. The specific GUI shown is for structural engineering calculations.
  • Excel Macros: The post showcases how Python code developed in Jupyter can be exposed as Excel macros, which can then be assigned to Excel objects (shapes, buttons).
  • VBA Macros: While the post demonstrates the use of Python macros as superior alternatives to VBA, the author acknowledges that VBA macros are a familiar starting point for Excel users. A basic VBA macro is shown as a reference point
Sub SomeVBASubroutine() 
x = Selection.Value MsgBox "Value of selected cell is: " & x 
End Sub
  1. Core Demonstration

The blog post, along with an associated video, demonstrates the following:

  • Simple VBA Macro: A basic VBA macro is used to retrieve the value of the currently selected Excel cell and display it in a message box. This provides a basic point of comparison.
  • PyXLL Jupyter Pane: The video demonstrates the use of a PyXLL Custom Task Pane (CTP), to open a Jupyter notebook from within Excel.
  • Two Python Macros:
  • A trivial macro that displays “Hello” in an alert box “@xl_macro def popup_messagebox2(): xlcAlert(“Hello”)”
  • A more complex macro that opens a PyQt dialog for performing structural engineering calculations, specifically related to ASCE wind loadings. This macro utilizes a custom form called “trial_pyqt_des_exp_wind_2a”. This example has significant interface elements such as

usual input/output,
tabbed output,
filtering of dropdown,
tabular output of all shape properties,
tabular output of all wind calculations,
output to the windows clipboard (cut and paste),
drawing cross sections to scale,
user specified elevation datum,
X or Y axis wind direction, and
fireproofing.

  • Macro Assignment: The macros, defined in the Jupyter notebook, are then assigned to Excel objects. “popup_messagebox2′ is assigned to the shape (Ritchie) and ‘show_trial_pyside2X’ is assigned to the button (show pyqt)”. This allows users to interact with the Python code through familiar Excel UI elements.
  1. Key Advantages of this Approach
  • Power and Flexibility: Python’s strength in data manipulation, scientific computing, and GUI development provides capabilities far beyond VBA.
  • Reusable Code: The use of PyXLL means that the Python code is not tied to a specific worksheet, making it universally available across a user’s Excel workbooks.
  • Persistent Macro Assignment: Once the Jupyter pane is closed, the assigned macros remain associated with their Excel objects. “After the Jupyter pane is dismissed, the macros remain assigned to the objects”.
  • Custom User Interfaces: PyQt/PySide2 allows for the creation of sophisticated and user-friendly GUIs, significantly enhancing the user experience of the macros.
  1. Target Audience

The content appears targeted at advanced Excel users or developers who want to create powerful, customized macros for specific tasks or business needs using the Python language, and who are familiar with concepts like: VBA, macros, and Jupyter notebooks. The example given of ASCE wind loading suggests the target audience may have a background in engineering or construction, or other technical fields where complex calculations and data presentations are needed.

  1. Further Research

The author includes the following documentation links to understand the context further:

  1. Conclusion

This blog post provides a practical demonstration of how to combine PyXLL, Jupyter, and PyQt/PySide2 to create powerful and reusable Excel macros. The use of Python provides far more functionality than traditional VBA macros, and the ability to create custom interfaces using PyQt/PySide2 makes the approach very user-friendly for complex tasks. The ability to create and run these macros from within Excel is a key advantage. The approach is especially valuable in fields requiring complicated analysis and calculations, as shown by the example of ASCE wind loading in structural engineering.

FAQ (by NotebookLM)

FAQ

What is the main purpose of the described system in the blog post?

The primary goal is to demonstrate how to build and use Python-based macros within Microsoft Excel by leveraging PyXLL and Jupyter. It showcases the capability to create both simple and complex macros, including those with graphical user interfaces (GUIs), and to assign these macros to Excel objects for interactive use.

How does PyXLL facilitate the use of Python macros in Excel?

PyXLL acts as a bridge, allowing Python code to be seamlessly integrated with Excel. It enables you to define Python functions as Excel macros, which can be invoked from within Excel workbooks. Importantly, the Python code is not stored directly within the worksheet but instead resides separately, making it universally available across all of a user’s workbooks, which promotes code reusability and maintainability.

What is Jupyter’s role in creating the Python-based macros?

Jupyter Notebooks are utilized as a development environment for writing and testing the Python code that defines the macros. Within the Jupyter environment, one can create and run Python code, as well as incorporate the necessary libraries (like PyQt for GUIs and PyXLL itself). Once the code is functioning as desired in the notebook, it can then be deployed as an Excel macro via PyXLL.

What type of macros can be developed using this approach?

The system allows for a wide range of macros, from simple tasks like displaying a basic message box to creating sophisticated macros with graphical interfaces (GUIs). The example provided includes a macro that performs engineering calculations related to ASCE (building code) wind loadings, displaying results in a tabbed output and allows various user interactions, including cut and paste to the clipboard.

How are the Python-based macros made accessible within Excel?

After the Python code is developed in Jupyter, it is converted to an Excel macro using PyXLL’s @xl_macro decorator. These macros are then assigned to Excel objects such as shapes or buttons. The blog post example shows assigning a popup_messagebox2 macro to a shape and show_trial_pyside2X to a button within Excel. Once assigned, clicking or interacting with those objects triggers the execution of the linked Python code.

What is the benefit of using PyQt in this context?

PyQt enables the creation of custom graphical user interfaces (GUIs) for Excel macros. In the example, a PyQt dialog is created within the Python code to allow for more complex interactions. This demonstrates how a macro can have controls like dropdown menus, tables and allow user input to perform calculations and display results in a user-friendly manner beyond a simple message box or alert.

What is meant by “the macros remain assigned to the objects” after the Jupyter pane is dismissed?

This highlights that the connection between the Excel objects (like buttons and shapes) and the underlying Python macro is persistent. After the Jupyter notebook pane is closed, the Excel objects will still trigger the macros assigned to them. This means the macros continue to work within Excel even after the developer has closed the Jupyter notebook session they used to create them, which is a crucial feature for making them usable for end users.

How does this approach differ from traditional VBA macros?

While VBA macros are stored directly in the Excel workbook, the Python macros enabled by PyXLL exist separately. This centralizes the code and makes it available across multiple worksheets for the same user without being embedded within each Excel file. This provides a more organized structure, prevents code duplication, and simplifies updating code for all workbooks, a significant advantage over traditional VBA macros.

Study Guide (by NotebookLM)


PyQt, PyXLL, and Jupyter Macros in Excel Study Guide

Quiz

Instructions:  Answer the following questions in 2-3 sentences each.

  1. What is the primary function of VBA macros as presented in the source material?
  2. How does the source material describe the ease of coding VBA macros?
  3. What is the role of PyXLL in connecting Python code to Excel?
  4. In the described setup, how are Jupyter notebooks utilized?
  5. What are the two key aspects of using Python macros in Excel that are highlighted in the summary?
  6. Why is the fact that PyXLL code isn’t saved in the Excel worksheet considered an advantage?
  7. What is the purpose of the xlcAlert(“Hello”) line in the first Jupyter code cell?
  8. What does the show_trial_pyside2X() macro do, generally?
  9. Why is the get_qt_app() function necessary in the given Jupyter code?
  10. In the second Jupyter cell, what is the purpose of sys.path.append(‘E:\my_pyxll_modules’)?

Quiz Answer Key

  1. The primary function of VBA macros, as presented, is to automate tasks within Excel. The specific example given retrieves the value of a selected cell and displays it in a message box.
  2. The source material explicitly states that writing a VBA macro (subroutine) is considered easy. It implies that this ease of coding is a well-known feature of VBA.
  3. PyXLL acts as a bridge that allows Python code, such as macros created in Jupyter notebooks, to be executed directly within Excel. This allows for greater computational power than VBA and access to Python libraries.
  4. Jupyter notebooks are used to create, write, and execute Python code that can then be deployed as Excel macros. It’s an environment for developing more complex routines to be used in Excel.
  5. The summary highlights that one can easily create Python macros for use in Excel and that the macros remain assigned to Excel objects even after the Jupyter pane is closed. This demonstrates how persistent and shareable the Python/Excel linkage is.
  6. Since PyXLL code is not stored within the worksheet, the code is accessible across all of a user’s worksheets. This promotes code reuse and consistency across many different documents.
  7. The line xlcAlert(“Hello”) creates a basic pop-up message box in Excel that displays the word “Hello”. This serves as a simple example of a PyXLL macro.
  8. The show_trial_pyside2X() macro initiates and executes a PyQt dialog. It specifically shows a dialog containing structural engineering calculations, utilizing the trial_pyqt_des_exp_wind_2a module.
  9. The get_qt_app() function ensures that an instance of the PyQt application is available, creating one if it doesn’t exist, which is necessary before showing any dialogs created with PyQt.
  10. The sys.path.append(‘E:\my_pyxll_modules’) line adds the specified directory to the Python system path, which allows Python to locate and import the trial_pyqt_des_exp_wind_2a module.

Essay Questions

Instructions:  Develop thoughtful essays in response to each of the following prompts.

  1. Discuss the advantages of using Python (via PyXLL and Jupyter) for Excel macros compared to traditional VBA. Consider aspects like library access, code reusability, and complexity handling.
  2. Analyze the role of PyQt in the context of the described Excel macros. Why is it used instead of more basic message box types?
  3. Explain the process of integrating Jupyter notebooks with Excel, as outlined in the video description. Why is this approach beneficial for developing and deploying complex macros?
  4. Describe the potential for using the technologies discussed to implement custom engineering calculations within Excel.
  5. How does using PyXLL and Jupyter improve the process of creating and maintaining custom Excel solutions, as opposed to just using VBA alone?

Glossary

ASCE: American Society of Civil Engineers; A professional organization that sets standards for structural design and engineering. CTP (Custom Task Pane): A custom pane that can be integrated into the Excel user interface to display additional tools or content.

Jupyter: An open-source web application that allows users to create and share documents that contain live code, equations, visualizations, and explanatory text. ***Macro: A recorded or programmed set of instructions that automates a repetitive task.

PyQt: A Python binding for the Qt application framework, enabling the creation of sophisticated graphical user interfaces.

PySide2: An alternative Python binding for the Qt framework, similar to PyQt.

PyXLL: A Python add-in for Microsoft Excel that allows users to write Python functions and macros that can be called directly from Excel.

VBA (Visual Basic for Applications): A programming language built into Microsoft Office applications (like Excel), primarily used to automate tasks.

Leave a Reply

Your email address will not be published. Required fields are marked *