{"id":665,"date":"2018-09-05T16:06:05","date_gmt":"2018-09-05T16:06:05","guid":{"rendered":"https:\/\/www.pyxll.com\/blog\/?p=665"},"modified":"2023-12-20T09:35:28","modified_gmt":"2023-12-20T09:35:28","slug":"how-to-profile-python-code-in-excel","status":"publish","type":"post","link":"https:\/\/www.pyxll.com\/blog\/how-to-profile-python-code-in-excel\/","title":{"rendered":"How to profile Python code in Excel"},"content":{"rendered":"\n
    \n
  1. Introduction<\/a><\/li>\n\n\n\n
  2. Write code to run inside and outside<\/em> of Excel<\/a><\/li>\n\n\n\n
  3. Setting up the Example<\/a>\n
      \n
    1. An example function<\/a><\/li>\n\n\n\n
    2. Setting up Excel<\/a><\/li>\n<\/ol>\n<\/li>\n\n\n\n
    3. How long is it taking?<\/a>\n
        \n
      1. Measuring how long a function takes<\/a><\/li>\n\n\n\n
      2. Profiling an Excel Application<\/a><\/li>\n\n\n\n
      3. Profiling multi-threaded Python code<\/a><\/li>\n<\/ol>\n<\/li>\n\n\n\n
      4. How to speed up your Python code<\/a>\n
          \n
        1. Line Profiling<\/a><\/li>\n\n\n\n
        2. Using the Line Profiler in Excel<\/a><\/li>\n\n\n\n
        3. Use NumPy or Pandas to replace for loops<\/a><\/li>\n\n\n\n
        4. Speed up your code using Numba or Cython<\/a><\/li>\n\n\n\n
        5. Multi-threading<\/a><\/li>\n\n\n\n
        6. Asynchronous Functions<\/a><\/li>\n<\/ol>\n<\/li>\n\n\n\n
        7. Resources<\/a><\/li>\n<\/ol>\n\n\n\n

          Introduction<\/h2>\n\n\n\n

          All the code from this blog is on github<\/a><\/em><\/p>\n\n\n\n

          When creating any kind of application, there sometimes comes a point where things are not running as smoothly as you want and you need to think about the performance of your application. The same is true when developing Excel spreadsheets. Using PyXLL to add functionality to Excel is an extremely powerful way to create rich analytics and applications in Excel, but what do you do when your Python based spreadsheet is having performance problems?<\/p>\n\n\n\n

          When tackling any sort of performance problem, the first step is always to profile the code! This post covers some pointers to make it easier for you to identify performance bottlenecks (or hotspots) and how to profile a running Excel application.<\/p>\n\n\n\n

          Write code to run inside and outside<\/em> of Excel<\/h2>\n\n\n\n

          When writing Python code for Excel it’s helpful to be able to run it outside of Excel as well. Doing so means that you can apply automated testing (e.g. unit testing) to your code, and also you can examine any performance bottlenecks and debug problems without having to run your code in Excel.<\/p>\n\n\n\n

          To help you do this, PyXLL provides as stubs<\/em> package that declares all the same functions and decorators you use to expose your Python functions to Excel (e.g. @xl_func<\/em>), but that do nothing. This means you can run the same code both in Excel and from any Python prompt.<\/p>\n\n\n\n

          When you download PyXLL, the stubs package is provided as a .whl<\/em> file, or Python Wheel. It will be named pyxll-{version}-{pyversion}-none-win{32\/64}.whl<\/em>, depending on which version of Python you have downloaded PyXLL for. To install it, open a command prompt and if necessary active your Python environment and use the command pip<\/em> to install the whl file:<\/p>\n\n\n

          \n>> activate myenv  ;; only if using conda or a venv\n>> pip install .\/pyxll-3.5.4-cp36-none-win_amd64.whl [\/code]\n<\/pre><\/div>\n\n\n
          Note:<\/strong>Use the path to the file, including “.\/” if in the current directory. Without this, pip will look for a wheel with that name from the remote PyPI repository instead of installing the local file.<\/em><\/p>\n\n\n\n
          Now you will be able to import pyxll from Python even if not running in Excel via the PyXLL add-in.<\/p>\n\n\n\n
          Setting up the Example<\/h2>\n\n\n\n
          Before we can look at profiling, we need to set up some code to profile \ud83d\ude42 For this we’ll use a simple function that calculates the average of a range of numbers, with a tolerance so only numbers above that tolerance are included.<\/p>\n\n\n\n
          An example function<\/h3>\n\n\n\n
          Start your preferred Python IDE (I will be using PyCharm for this blog), or a simple text editor will do, create a new file named “average_tol.py” and enter the following:<\/p>\n\n\n
          \nfrom pyxll import xl_func\n\n@xl_func\ndef average_tol_simple(values, tolerance):\n    """Compute the mean of values where value is > tolerance\n\n    :param values: Range of values to compute average over\n    :param tolerance: Only values greater than tolerance will be considered\n    """\n    total = 0.0\n    count = 0\n    for row in values:\n        for value in row:\n            if not isinstance(value, float):\n                continue\n            if value <= tolerance:\n                continue\n            total += value\n            count += 1\n    return total \/ count\n<\/pre><\/div>\n\n\n
          This function will compute the average of a range of numbers above the specified tolerance, and will also be available to be called from Excel because it’s decorated with the @xl_func<\/em> annotation.<\/p>\n\n\n\n
          We can test this by importing the Python module and calling the function. If you are using PyCharm you can right click and select “Run File in Console”. This will run the file and give you an IPython prompt so you can test the function:<\/p>\n\n\n\n
          \nIn[2]: average_tol_simple([[1,2,3],[4,5,6],[7,8,9]], 5)\nOut[2]: 7.5\n<\/pre><\/pre>\n\n\n\n
          If you are not using PyCharm, you can import the function and run it as normal from an IPython prompt. If you don’t have IPython installed, install it now as we will be using some of its timing features later. IPython can be installed by running “pip install ipython” or “conda install ipython” if you are using Anaconda.<\/p>\n\n\n\n
          Setting up Excel<\/h3>\n\n\n\n
          Now we’ve got a basic function we can try calling it from Excel. If you’ve not already installed PyXLL, install it now. See the PyXLL Introduction<\/a> for installation instructions. Ensure you point it at the same Python you have been using so far by setting “executable” in the pyxll.cfg config file.<\/p>\n\n\n\n
          Once you’ve got PyXLL installed successfully, add the path where you’ve saved “average_tol.py” to your Python path and add “average_tol” to the list of modules for PyXLL to import:<\/p>\n\n\n\n
          \n[PYTHON]\nexecutable = {path to your Python executable}\npythonpath =\n    {path to the folder where average_tol.py is saved}\n\n[PYXLL]\nmodules =\n    average_tol\n<\/pre><\/pre>\n\n\n\n
          Now when you reload PyXLL (or restart Excel) our new “average_tol_simple” function will be available to call as a worksheet function.<\/p>\n\n\n\n
          \"Simple<\/figure>\n\n\n\n
          How long is it taking?<\/h2>\n\n\n\n
          Measuring how long a function takes<\/h3>\n\n\n\n
          Now we’ve got a basic function working in Excel, how long does it take to run? If it’s very slow, we can just time it roughly using a watch or the clock on the computer. That’s error prone and won’t really give us the precision we need. Luckily, IPython has a way to time how long it takes to run a function accurately. It does this by running the function a number of times and taking the average.<\/p>\n\n\n\n
          To time the function in IPython we use the “%timeit” command. To make the function take a little longer, we’ll use a range with 32,000 values. To get that range, we use numpy’s arange function and reshape it into a 2d array.<\/p>\n\n\n\n
          \n[In2]: values = np.arange(1, 32001, dtype=float).reshape((320, 100))\n[In3]: average_tol_simple(values, 500)\nOut[2]: 16250.5\n[In4]: %timeit average_tol_simple(values, 500)\n14.9 ms \u00b1 135 \u00b5s per loop (mean \u00b1 std. dev. of 7 runs, 100 loops each)\n<\/pre><\/pre>\n\n\n\n
          Great! So on my laptop, it takes about 15ms to run that function for 32,000 values. But, how long does the same function take in Excel? We’d expect it to be a slower since there is inevitably some overhead in constructing the Python array from the Excel values and calling the Python function, but how can we test that?<\/p>\n\n\n\n
          One way is to set up the calculation we want, and then add a menu function to trigger that calculation and time how long Excel takes to do it. There will be some cost involved in calling the Excel API to run the calculation, but if we run a long enough calculation that time will be insignificant and so we can get a reasonably accurate picture of how long it’s taking.<\/p>\n\n\n\n
          The following triggers the recalculation of the selected cell 100 times and calculates the mean time and standard deviation. This is roughly the same as what we did previously using “%timeit” in IPython. To use it, copy the following code to a new file called “profiling_tools.py” and add “profiling_tools” to the list of modules in your pyxll.cfg file. When you reload PyXLL you will see a new menu “Profiling Tools” (under the Add-Ins tab) with our new menu item “Time to Calculate.<\/p>\n\n\n
          \nfrom pyxll import xl_menu, xl_app, xlcAlert\nfrom win32com.client import constants\nimport win32clipboard\nimport math\nimport time\n\n@xl_menu(\"Time to Calculate\", menu=\"Profiling Tools\")\ndef time_calculation():\n    \"\"\"Recalculates the selected range and times how long it takes\"\"\"\n    xl = xl_app()\n\n    # switch Excel to manual calculation and disable screen updating\n    orig_calc_mode = xl.Calculation\n    try:\n        xl.Calculation = constants.xlManual\n        xl.ScreenUpdating = False\n\n        # get the current selection\n        selection = xl.Selection\n\n        # run the calculation a few times\n        timings = []\n        for i in range(100):\n            # Start the timer and calculate the range\n            start_time = time.clock()\n            selection.Calculate()\n            end_time = time.clock()\n            duration = end_time - start_time\n            timings.append(duration)\n    finally:\n        # restore the original calculation mode and enable screen updating\n        xl.ScreenUpdating = True\n        xl.Calculation = orig_calc_mode\n\n    # calculate the mean and stddev\n    mean = math.fsum(timings) \/ len(timings)\n    stddev = (math.fsum([(x - mean) ** 2 for x in timings]) \/ len(timings)) ** 0.5\n    best = min(timings)\n    worst = max(timings)\n\n    # copy the results to the clipboard\n    data = [\n        [\"mean\", mean],\n        [\"stddev\", stddev],\n        [\"best\", best],\n        [\"worst\", worst]\n    ]\n    text = \"\\n\".join([\"\\t\".join(map(str, x)) for x in data])\n    win32clipboard.OpenClipboard()\n    win32clipboard.EmptyClipboard()\n    win32clipboard.SetClipboardText(text)\n    win32clipboard.CloseClipboard()\n\n    # report the results\n    xlcAlert((\"%0.2f ms \\xb1 %d \\xb5s\\n\"\n              \"Best: %0.2f ms\\n\"\n              \"Worst: %0.2f ms\\n\"\n              \"(Copied to clipboard)\") % (mean * 1000, stddev * 1000000, best * 1000, worst * 1000))\n\n<\/pre><\/div>\n\n\n
          Note:<\/strong>If you can’t import win32clipboard you need to install the pywin32 package. You can do that with pip, “pip install pypiwin32” or with conda, “conda install pywin32”.<\/em><\/p>\n\n\n\n
          \"Time<\/figure>\n\n\n\n
          In Excel we can now use this menu item to time how long an individual cell (or range of cells) takes to calculate. Modifying the call to “average_tol_simple” to take a range with 32,000 values and using this, on my PC I get a result that it takes about 11 ms to compute. This is actually lower than it took in IPython! However, that’s because in IPython we were iterating over a numpy array which is slower than iterating over the list that we get with PyXLL. For a fair comparison we need to use a list in both cases and then we see that the time take in IPython is lower:<\/p>\n\n\n\n
          \n[In5]: values2 = values.tolist()\n[In6]: %timeit average_tol_simple(values2, 500)\n8.14 ms \u00b1 37.2 \u00b5s per loop (mean \u00b1 std. dev. of 7 runs, 100 loops each)\n<\/pre><\/pre>\n\n\n\n
          Now the time in Excel is slightly longer than in IPython, as expected due to passing 32,000 values between Excel and Python.<\/p>\n\n\n\n
          \"Comparison<\/figure>\n\n\n\n
          Profiling an Excel Application<\/h3>\n\n\n\n
          Sometimes when a workbook is running slowly it’s not obvious where that time is being spent. It’s easy to speculate and guess what function is taking a long time, but to be sure it’s always good practice to profile. Even if you think you know where the problem is, you can often be surprised after profiling!<\/p>\n\n\n\n
          Python comes with its own profiler called cProfile<\/a>.<\/p>\n\n\n\n
          To profile a Python script, cProfile can be run as follows:<\/p>\n\n\n\n
          \npython -m cProfile -s cumtime your_script.py\n<\/pre><\/pre>\n\n\n\n
          This prints out all the functions called, with the time spend in each and the number of times they have been called.<\/p>\n\n\n\n
          As mentioned earlier, if you can run your code outside of Excel and can build a script that runs your functions with representative data then that makes things nice and easy \ud83d\ude42 You can just run that script using cProfile and see where the bottlenecks are!<\/p>\n\n\n\n
          Real world cases are rarely that straightforward, and in order to get to the point where we can focus on a few functions outside of Excel we need to profile what Excel is actually doing<\/em>. Starting to optimize your code without profiling first will waste a lot of time.<\/p>\n\n\n\n
          We can call the cProfile module from inside Excel from a couple of menu functions. By starting and stopping the profiler in Excel we can get some details about what’s actually being called. The following code adds two new menu items, one for starting the profiler and another for stopping it and collecting the results.<\/p>\n\n\n\n
          \r\nfrom pyxll import xl_menu, xlcAlert\r\nfrom io import StringIO\r\nimport win32clipboard\r\nimport cProfile\r\nimport pstats\r\n\r\n_active_cprofiler = None\r\n\r\n@xl_menu("Start", menu="Profiling Tools", sub_menu="cProfile")\r\ndef start_profiling():\r\n    """Start the cProfile profiler"""\r\n    global _active_cprofiler\r\n    if _active_cprofiler is not None:\r\n        _active_cprofiler.disable()\r\n    _active_cprofiler = cProfile.Profile()\r\n\r\n    xlcAlert("cProfiler Active\\n"\r\n             "Recalcuate the workbook and then stop the profiler\\n"\r\n             "to see the results.")\r\n\r\n    _active_cprofiler.enable()\r\n\r\n@xl_menu("Stop", menu="Profiling Tools", sub_menu="cProfile")\r\ndef stop_profiling():\r\n    """Stop the cProfile profiler and print the results"""\r\n    global _active_cprofiler\r\n    if not _active_cprofiler:\r\n        xlcAlert("No active profiler")\r\n        return\r\n\r\n    _active_cprofiler.disable()\r\n\r\n    # print the profiler stats\r\n    stream = StringIO()\r\n    stats = pstats.Stats(_active_cprofiler, stream=stream).sort_stats("cumulative")\r\n    stats.print_stats()\r\n\r\n    # print the results to the log\r\n    print(stream.getvalue())\r\n\r\n    # and copy to the clipboard\r\n    win32clipboard.OpenClipboard()\r\n    win32clipboard.EmptyClipboard()\r\n    win32clipboard.SetClipboardText(stream.getvalue())\r\n    win32clipboard.CloseClipboard()\r\n\r\n    _active_cprofiler = None\r\n\r\n    xlcAlert("cProfiler Stopped\\n"\r\n             "Results have been written to the log and clipboard.")\r\n<\/pre><\/pre>\n\n\n\n
          Copy the code above into “profiling_tools.py” and add “profiling_tools” to the list of modules in your pyxll.cfg. Restart PyXLL and you’ll find the new menu items should have been added to the Add-Ins menu.<\/p>\n\n\n\n
          \"Start<\/figure>\n\n\n\n
          To profile your workbook, start the profiler using the menu item and then recalculate everything you want to be included in the profile. When finished, stop the profiler and the results are printed to the log file and copied to the clipboard.<\/p>\n\n\n\n
          \n         32024 function calls (32023 primitive calls) in 0.020 seconds\n\n   Ordered by: cumulative time\n\n   ncalls  tottime  percall  cumtime  percall filename:lineno(function)\n        1    0.009    0.009    0.012    0.012 c:\\github\\pyxll-examples\\profiling\\average_tol.py:8(average_tol_simple)\n      2\/1    0.005    0.002    0.005    0.005 c:\\github\\pyxll-examples\\profiling\\average_tol.py:49(average_tol_numba)\n        1    0.003    0.003    0.003    0.003 c:\\github\\pyxll-examples\\profiling\\average_tol.py:28(average_tol_try_except)\n    32002    0.003    0.000    0.003    0.000 {built-in method builtins.isinstance}\n<\/pre><\/pre>\n\n\n\n
          Once you’ve identified what functions are taking most time you can focus on improving them.<\/p>\n\n\n\n
          Profiling multi-threaded Python code<\/h3>\n\n\n\n
          The Python profiler cProfile<\/em> used in the section above is great for single threaded code, but it only collects profiling data for the thread it’s called on. It is common to use one or more background threads when writing RTD or async functions, and using cProfile in these cases will not include timing data for the background threads.<\/p>\n\n\n\n
          Fortunately there is another third-party profiling tool, yappi<\/a> that will profile code running in all threads. Yappi can be installed using pip:<\/p>\n\n\n
          \n>> pip install yappi\n<\/pre><\/div>\n\n\n
          Profiling using yappi<\/em> is straightforward and can be done in a similar way to what was shown above. All that is needed is to modify the start_profiling<\/em> and stop_profiling<\/em> menu functions to use yappi<\/em> instead of cPython<\/em>.<\/p>\n\n\n\n
          \r\n@xl_menu("Start", menu="Profiling Tools", sub_menu="Yappi")\r\ndef start_profiling():\r\n    yappi.start()\r\n\r\n@xl_menu("Stop", menu="Profiling Tools", sub_menu="Yappi")\r\ndef stop_profiling():\r\n    yapp.stop()\r\n    yappi.get_func_stats().print_all()\r\n    yappi.get_thread_stats().print_all()\r\n    yappi.clear_stats()\r\n<\/pre><\/pre>\n\n\n\n
          The procedure to profiling your code is the same as above. Start the profiler, recalculate everything you want to profile and then stop the profiler. The results will be written to the log file, only this time everything from any background threads will also be included.<\/p>\n\n\n\n
          How to speed up your Python code<\/h2>\n\n\n\n
          Optimizing Python code is a whole series of blog posts in itself, but since now we know how to time an individual function in both IPython and Excel it’s a good time to dig a little deeper and see how we can improve things.<\/p>\n\n\n\n
          Line Profiling<\/h3>\n\n\n\n
          If you already know what function you need to spend time optimizing then the “line_profiler” Python package is a useful tool to drill into the function and see where the time’s going. In our case, since we only have one function we know which one to optimize, but in more complex cases it’s advisable to spend some time before this stage to uncover exactly which functions are problematic.<\/p>\n\n\n\n
          Install the “line_profiler” package using “pip install line_profiler” or “conda install line_profiler” if using Anaconda.<\/p>\n\n\n\n
          The line profiler works by running a Python script and looking for functions decorated with a special ‘@profile’ decorator. The easiest way to run it for our function is to turn the module into script that will call the function we’re interested in a number of times. We do this by adding some code at the end that only runs if the module is being run as a script by checking __name__ == “__main__”<\/em>.<\/p>\n\n\n
          \nfrom pyxll import xl_func\n\n@xl_func\n@profile\ndef average_tol_simple(values, tolerance):\n    """Compute the mean of values where value is > tolerance\n\n    :param values: Range of values to compute average over\n    :param tolerance: Only values greater than tolerance will be considered\n    """\n    total = 0.0\n    count = 0\n    for row in values:\n        for value in row:\n            if not isinstance(value, (float, int)):\n                continue\n            if value <= tolerance:\n                continue\n            total += value\n            count += 1\n    return total \/ count\n\nif __name__ == "__main__":\n    import numpy as np\n    values = np.arange(1, 32001, dtype=float).reshape(100, 320).tolist()\n    for i in range(100):\n        avg = average_tol_simple(values, 500)\n<\/pre><\/div>\n\n\n
          Note that now “@profile” has been added to the average_tol_simple function.<\/p>\n\n\n\n
          To run the line profiler we do the following:<\/p>\n\n\n
          \n>> kernprof -v -l average_tol.py\n<\/pre><\/div>\n\n\n
          The “-v” option tells the profiler to print the results rather than save them to a file. On my PC, the results look like this:<\/p>\n\n\n\n
          \"Python<\/figure>\n\n\n\n
          Looking at the percentage time we can see that the biggest contributor is testing whether the value is a number of not. In Python, using a try\/except block can be faster than doing explicit checks like this so we can try removing that check and catching and TypeError exceptions that might be raised as follows:<\/p>\n\n\n
          \ndef average_tol_try_except(values, tolerance):\n    total = 0.0\n    count = 0\n    for row in values:\n        for value in row:\n            try:\n                if value <= tolerance:\n                    continue\n                total += value\n                count += 1\n            except TypeError:\n                continue\n    return total \/ count\n<\/pre><\/div>\n\n\n
          Using IPython’s %timeit again we can see that change has really improved the performance of this function.<\/p>\n\n\n\n
          \n[In8]: %timeit average_tol_try_except(values, 500)\n3.53 ms \u00b1 60.8 \u00b5s per loop (mean \u00b1 std. dev. of 7 runs, 100 loops each)\n<\/pre><\/pre>\n\n\n\n
          Running the same test as before in Excel also shows a similar speed up, though because we are passing a large range of data and the function the time taken to convert the data from Excel to Python means there is always some small overhead.<\/p>\n\n\n\n
          \"Try\/Except<\/figure>\n\n\n\n
          Using the Line Profiler in Excel<\/h3>\n\n\n\n
          As well as using the line profiler from the command line as described above, it’s also possible to use the line_profiler API to profile functions without leaving Excel.<\/p>\n\n\n\n
          The following code adds two new menu items to start and stop the line profiler. Once the line profiler is started, if any function decorated with the @enable_line_profiler decorator (also defined below) will be profiled. When the profiler is stopped the results are written to the log file and to the clipboard to be pasted into Excel.<\/p>\n\n\n\n
          \r\nfrom pyxll import xl_menu\r\nfrom functools import wraps\r\nfrom io import StringIO\r\nimport win32clipboard\r\nimport line_profiler\r\n\r\n# Current active line profiler\r\n_active_line_profiler = None\r\n\r\n@xl_menu("Start", menu="Profiling Tools", sub_menu="Line Profiler")\r\ndef start_line_profiler():\r\n    """Start the line profiler"""\r\n    global _active_line_profiler\r\n    _active_line_profiler = line_profiler.LineProfiler()\r\n\r\n    xlcAlert("Line Profiler Active\\n"\r\n             "Run the function you are interested in and then stop the profiler.\\n"\r\n             "Ensure you have decoratored the function with @enable_line_profiler.")\r\n\r\n@xl_menu("Stop", menu="Profiling Tools", sub_menu="Line Profiler")\r\ndef stop_line_profiler():\r\n    """Stops the line profiler and prints the results"""\r\n    global _active_line_profiler\r\n    if not _active_line_profiler:\r\n        return\r\n\r\n    stream = StringIO()\r\n    _active_line_profiler.print_stats(stream=stream)\r\n    _active_line_profiler = None\r\n\r\n    # print the results to the log\r\n    print(stream.getvalue())\r\n\r\n    # and copy to the clipboard\r\n    win32clipboard.OpenClipboard()\r\n    win32clipboard.EmptyClipboard()\r\n    win32clipboard.SetClipboardText(stream.getvalue())\r\n    win32clipboard.CloseClipboard()\r\n\r\n    xlcAlert("Line Profiler Stopped\\n"\r\n             "Results have been written to the log and clipboard.")\r\n\r\ndef enable_line_profiler(func):\r\n    """Decorator to switch on line profiling for a function.\r\n\r\n    If using line_profiler from the command line, use the built-in @profile\r\n    decorator instead of this one.\r\n    """\r\n    @wraps(func)\r\n    def wrapper(*args, **kwargs):\r\n        nonlocal func\r\n        if _active_line_profiler:\r\n            func = _active_line_profiler(func)\r\n        return func(*args, **kwargs)\r\n    return wrapper\r\n<\/pre><\/pre>\n\n\n\n
          Copy the code above into your profiling_tools.py file, and add the @enable_line_profiler decorator to the average_tol_simple function.<\/p>\n\n\n
          \nfrom pyxll import xl_func\nfrom profiling_tools import enable_line_profiler\n\n@xl_func\n@enable_line_profiler\ndef average_tol_simple(values, tolerance):\n    total = 0.0\n    count = 0\n    for row in values:\n        for value in row:\n            if not isinstance(value, (float, int)):\n                continue\n            if value <= tolerance:\n                continue\n            total += value\n            count += 1\n    return total \/ count\n<\/pre><\/div>\n\n\n
          Now reload PyXLL and you’ll see the two new menu items. Start the line profiler, call average_tol_simple, then stop the line profiler and copy the results to Excel or look at the log file.<\/p>\n\n\n\n
          Use NumPy or Pandas to replace for loops<\/h3>\n\n\n\n
          For operations involving large arrays of data it’s often much faster (and simpler) to use numpy or pandas rather than writing for loops to process data.<\/p>\n\n\n\n
          Our example function can easily be re-written using numpy:<\/p>\n\n\n
          \n@xl_func("numpy_array<float>, float: float")\ndef average_tol_numpy(values, tolerance):\n    return values[values > tolerance].mean()\n<\/pre><\/div>\n\n\n
          Note the use of the signature in @xl_func. That tells PyXLL what types to pass to our function, and so handles the conversion of the array of data into a numpy array for us.<\/p>\n\n\n\n
          This function is significantly faster than our original, and now the time taken is dominated by the cost of converting the Excel data into Python objects.<\/p>\n\n\n\n
          \"NumPy<\/figure>\n\n\n\n
          \n[In9]: %timeit average_tol_numpy(values, 500.0)\n61.5 \u00b5s \u00b1 451 ns per loop (mean \u00b1 std. dev. of 7 runs, 10000 loops each)\n<\/pre><\/pre>\n\n\n\n
          This version of the function however doesn’t handle non-numeric types being passed to it, whereas the previous ones would skip non-numeric values. Adding those checks back in negates the improvement gained by using numpy, which goes to show why profiling is so important!<\/p>\n\n\n\n
          Speed up your code using Numba or Cython<\/h3>\n\n\n\n
          For certain types of CPU bound functions you can get significant speedups using Numba<\/a> or Cython<\/a>.<\/p>\n\n\n\n
          Numba is a Python JIT compiler that can speed up any array and math heavy calculations with simple annotations. For example, all that’s need to speed up our average function is to do the following:<\/p>\n\n\n
          \nfrom pyxll import xl_func\nimport numba\n\n@xl_func\n@numba.jit(locals={"value": numba.double, "total": numba.double, "count": numba.int32})\ndef average_tol_numba(values, tolerance):\n    total = 0.0\n    count = 0\n    for row in values:\n        for value in row:\n            if value <= tolerance:\n                continue\n            total += value\n            count += 1\n    return total \/ count\n<\/pre><\/div>\n\n\n
          By telling Numba some of the types of the local variables it can the code faster. However, for a function like this where most of the time is spent iterating over the lists, the overhead of using the JIT compiler means there is little or no performance benefit. Real world functions are likely to have better results!<\/p>\n\n\n\n
          Cython works by converting Python code into C code, which is then compiled into a C extension. When used carefully this can speed up code dramatically. As with all optimization techniques though, constant profiling is required to understand where the time is being spent.<\/p>\n\n\n\n
          Multi-threading<\/h3>\n\n\n\n
          Excel can run calculations concurrently across multiple threads. This is only applicable if the functions can actually be run concurrently and don’t depend on each other. For sheets with many functions this can improve the overall time to calculate the sheet by making better use of a modern PCs’ multiple cores.<\/p>\n\n\n\n
          To enable a function to be run in a background calculation thread, all that’s required is to add “thread_safe=True” to the “@xl_func” decorator:<\/p>\n\n\n\n
          \nfrom pyxll import xl_func\n\n@xl_func(thread_safe=True)\ndef thread_safe_func():\n    pass\n<\/pre><\/pre>\n\n\n\n
          Although thread safe functions may be run concurrently across multiple threads, Python code can’t always take advantage of this because of something called the “GIL” or “Global Interpreter Lock”. Basically, while the Python interpreter is doing anything it always acquires the GIL, preventing any other thread from running any Python code at the same time! This means that although your Python code is now running on multiple threads, it only runs on one thread at once and so you see no benefit from this.<\/p>\n\n\n\n
          All is not lost however. Many libraries will release the GIL while running long tasks. For example, if you are reading a large file from disk, then Python will release the GIL while that IO operation takes place meaning that it’s freed up for other threads to continue running Python code. Packages like numpy and scipy also release the GIL while performing numerical computations, so if you are making heavy use of those your code may benefit from being multi-threaded too. If you are using numba or cython to speed up your code, both of those have options to release the GIL so you can make your code thread friendly.<\/p>\n\n\n\n
          If you have IO bound functions (e.g. reading from disk, a network or a database), or if you’re packages that release the GIL (like numpy or scipy), then using multiple threads may help. If your functions are purely CPU bound and are just running plain Python code then it may not.<\/p>\n\n\n\n
          For some more information about multi-threading and multi-processing in Python see this post: https:\/\/www.geeksforgeeks.org\/difference-between-multithreading-vs-multiprocessing-in-python\/<\/a>.<\/p>\n\n\n\n
          For a more detailed explanation of the GIL see https:\/\/wiki.python.org\/moin\/GlobalInterpreterLock<\/a>.<\/p>\n\n\n\n
          Asynchronous Functions<\/h3>\n\n\n\n
          For IO bound functions asynchronous functions allow Excel to continue working on other functions while the asynchronous function continues in the background. This can be useful in some situations where you don’t want to block one of the calculation threads from doing other calculations while you wait for a response from a remote server, for example.<\/p>\n\n\n\n
          For more information about asynchronous functions in PyXLL see Asynchronous Functions<\/a> in the user guide.<\/p>\n\n\n\n
          Note that asynchronous functions must still return during Excel’s calculation phase, and so they are not a way to free up the user interface while a long running calculation is being performed. For that, use an RTD (Real Time Data<\/a>) function instead.<\/p>\n\n\n\n
          Resources<\/h2>\n\n\n\n