Forum Discussion

Xunxing2019's avatar
Xunxing2019
Copper Contributor
Sep 25, 2023

python for excel : python packages

Is it possible to install or include any other python package in excel, like NumPy-Financial package? besides supported packages, such as pandas, seaborn, scikit-learn, matplotlib. Thanks.

  • Hi Leon,

    Thanks for your guide, I have tried xlwings library, as I thought it may also be benefit from quick runtime by the help of jupyter notebook, the sync between is really quick so far, will try more cases with more work book open. Thanks!
  • seriam's avatar
    seriam
    Copper Contributor

    As of my last update in January 2022, Excel's Python integration primarily supports a limited set of libraries like pandas, seaborn, scikit-learn, and matplotlib. However, you cannot directly install or include additional Python packages, such as NumPy-Financial, within Excel's integrated Python environment.

    Workarounds:

    1. External Python Environment: You can use an external Python environment to run your code with the desired packages and then import the results into Excel using methods like data import or linking to external data sources.

    2. Custom Functions with Excel-DNA: If you are comfortable with .NET development, you can create custom Excel functions using Excel-DNA that leverage the full capabilities of Python, including any package you want to use.

    3. Excel Add-ins: Consider creating an Excel Add-in using technologies like VBA or .NET that integrates with an external Python script. This approach allows you to utilize any Python package and seamlessly interact with Excel.

    4. Power Query: For data manipulation tasks, you can use Power Query to connect to external data sources, including Python scripts, and then load the data into Excel.

    5. Python API Calls: If the package provides an API or web service, you can make API calls directly from Excel using VBA or Power Query to retrieve and manipulate the data.

    Example with Power Query:

    You can use Power Query to run a Python script and fetch the data into Excel.

    1. Go to Data > Get Data > From Other Sources > Blank Query.
    2. In the formula bar, enter your Python script using the Python.Execute function.
    let
        Source = Python.Execute("import numpy_financial as npf; data = [100, 200, 300]; result = npf.npv(0.05, data); result")
    in
        Source

    Conclusion:

    While Excel's built-in Python integration has limitations on supported packages, you can leverage various workarounds to use additional Python packages in combination with Excel. Choose the approach that best fits your needs and technical expertise.

  • LeonPavesic's avatar
    LeonPavesic
    Silver Contributor

    Hi Xunxing2019,

    it is possible to install or include Python package in Excel, including NumPy-Financial.

    There are two main ways to do this:

    1. Use the PyXLL add-in. PyXLL is a commercial add-in for Excel that allows you to write and run Python code directly in Excel. To use PyXLL, you first need to install the Python package on your computer. Then, you can install the PyXLL add-in from the PyXLL website. Once PyXLL is installed, you can start writing and running Python code in Excel by creating a new Excel workbook and enabling the PyXLL add-in.

    2. Use the xlwings library. xlwings is an open-source library that allows you to run Python code from Excel. To use xlwings, you first need to install the Python package on your computer. Then, you can install the xlwings library using pip:

     

    pip install xlwings

     

     

    Once xlwings is installed, you can start running Python code from Excel by creating a new Excel workbook and importing the xlwings library.

    Here is an example of how to use NumPy-Financial in Excel using xlwings:

     

     

    import xlwings as xw
    
    # Create a new Excel workbook
    wb = xw.Book()
    
    # Import the NumPy-Financial library
    import numpy_financial as npf
    
    # Calculate the present value of an annuity
    present_value = npf.pv(rate=0.05, nper=10, pmt=100)
    
    # Write the present value to Excel
    wb.sheets[0].range('A1').value = present_value
    
    # Save the workbook
    wb.save('my_excel_file.xlsx')

     

    Here are some additional tips for using Python packages in Excel:

    • Use a Python environment manager such as Anaconda or Conda to manage your Python packages. This will make it easier to install and manage the packages you need.
    • Make sure that you have the latest version of Excel installed. Excel 2016 and later versions support Python 3.
    • If you are using PyXLL, make sure that you have the latest version of the add-in installed.
    • If you are using xlwings, make sure that you have the latest version of the library installed.
    • Test the Python packages you want to use in Excel to make sure that they are compatible and perform as expected.

     

    Please click Mark as Best Response & Like if my post helped you to solve your issue.
    This will help others to find the correct solution easily. It also closes the item.


    If the post was useful in other ways, please consider giving it Like.


    Kindest regards,


    Leon Pavesic

    • Xunxing2019's avatar
      Xunxing2019
      Copper Contributor
      Hi Leon,

      Thanks for your guide, I have tried xlwings library, as I thought it may also be benefit from quick runtime by the help of jupyter notebook, the sync between is really quick so far, will try more cases with more work book open. Thanks!

Resources