Tutorial: Create custom functions in Excel

Custom functions enable you to add new functions to Excel by defining those functions in JavaScript as part of an add-in. Users within Excel can access custom functions as they would any native function in Excel, such as SUM(). You can create custom functions that perform simple tasks like calculations or more complex tasks such as streaming real-time data from the web into a worksheet.

In this tutorial, you will:

  • Create a custom function add-in using the Yeoman generator for Office Add-ins.
  • Use a prebuilt custom function to perform a simple calculation.
  • Create a custom function that gets data from the web.
  • Create a custom function that streams real-time data from the web.

Prerequisites

  • Node.js (version 8.0.0 or later)

  • The latest version of Yeoman and the Yeoman generator for Office Add-ins. To install these tools globally, run the following command via the command prompt:

    npm install -g yo generator-office
    

    Note

    Even if you've previously installed the Yeoman generator, we recommend you update your package to the latest version from npm.

  • Excel on Windows (version 1904 or later, connected to Office 365 subscription) or on the web

Create a custom functions project

To start, you'll create the code project to build your custom function add-in. The Yeoman generator for Office Add-ins will set up your project with some prebuilt custom functions that you can try out. If you have already run the custom functions quick start and generated a project, continue to use that project and skip to this step instead.

  1. Run the following command and then answer the prompts as follows.

    yo office
    
    • Choose a project type: Excel Custom Functions Add-in project
    • Choose a script type: JavaScript
    • What do you want to name your add-in? stock-ticker

    Yeoman generator for Office Add-ins prompts for custom functions

    The Yeoman generator will create the project files and install supporting Node components.

  2. Navigate to the root folder of the project.

    cd stock-ticker
    
  3. Build the project.

    npm run build
    

    Note

    Office Add-ins should use HTTPS, not HTTP, even when you are developing. If you are prompted to install a certificate after you run npm run build, accept the prompt to install the certificate that the Yeoman generator provides.

  4. Start the local web server, which runs in Node.js. You can try out the custom function add-in in Excel on the web or Windows.

To test your add-in in Excel on Windows, run the following command. When you run this command, the local web server will start and Excel will open with your add-in loaded.

npm run start:desktop

Try out a prebuilt custom function

The custom functions project that you created contains some prebuilt custom functions, defined within the ./src/functions/functions.js file. The ./manifest.xml file specifies that all custom functions belong to the CONTOSO namespace. You'll use the CONTOSO namespace to access the custom functions in Excel.

Next you'll try out the ADD custom function by completing the following steps:

  1. In Excel, go to any cell and enter =CONTOSO. Notice that the autocomplete menu shows the list of all functions in the CONTOSO namespace.

  2. Run the CONTOSO.ADD function, with numbers 10 and 200 as input parameters, by typing the value =CONTOSO.ADD(10,200) in the cell and pressing enter.

The ADD custom function computes the sum of the two numbers that you provided and returns the result of 210.

Create a custom function that requests data from the web

Integrating data from the Web is a great way to extend Excel through custom functions. Next you’ll create a custom function named stockPrice that gets a stock quote from a Web API and returns the result to the cell of a worksheet.

Note

The following code requests a stock quote using the IEX Trading API. Before you can run the code, you'll need to create a free account with IEX Cloud so that you can get the API token that's required in the API request.

  1. In the stock-ticker project, find the file ./src/functions/functions.js and open it in your code editor.

  2. In functions.js, locate the increment function and add the following code after that function.

    /**
    * Fetches current stock price
    * @customfunction 
    * @param {string} ticker Stock symbol
    * @returns {number} The current stock price.
    */
    function stockPrice(ticker) {
        //Note: In the following line, replace <YOUR_TOKEN_HERE> with the API token that you've obtained through your IEX Cloud account.
        var url = "https://cloud.iexapis.com/stable/stock/" + ticker + "/quote/latestPrice?token=<YOUR_TOKEN_HERE>"
        return fetch(url)
            .then(function(response) {
                return response.text();
            })
            .then(function(text) {
                return parseFloat(text);
            });
    
        // Note: in case of an error, the returned rejected Promise
        //    will be bubbled up to Excel to indicate an error.
    }
    CustomFunctions.associate("STOCKPRICE", stockPrice);
    

    The CustomFunctions.associate code associates the id of the function with the function address of stockPrice in JavaScript so that Excel can call your function.

  3. Run the following command to rebuild the project.

    npm run build
    
  4. Complete the following steps (for either Excel on the web or Windows) to re-register the add-in in Excel. You must complete these steps before the new function will be available.

  1. Close Excel and then reopen Excel.

  2. In Excel, choose the Insert tab and then choose the down-arrow located to the right of My Add-ins. Insert ribbon in Excel on Windows with the My Add-ins arrow highlighted

  3. In the list of available add-ins, find the Developer Add-ins section and select the stock-ticker add-in to register it. Insert ribbon in Excel on Windows with the Excel Custom Functions add-in highlighted in the My Add-ins list

  1. Try out the new function. In cell B1, type the text =CONTOSO.STOCKPRICE("MSFT") and press enter. You should see that the result in cell B1 is the current stock price for one share of Microsoft stock.

Create a streaming asynchronous custom function

The stockPrice function returns the price of a stock at a specific moment in time, but stock prices are always changing. Next you’ll create a custom function named stockPriceStream that gets the price of a stock every 1000 milliseconds.

  1. In the stock-ticker project, add the following code to ./src/functions/functions.js and save the file.

    /**
    * Streams real time stock price
    * @customfunction 
    * @param {string} ticker Stock symbol
    * @param {CustomFunctions.StreamingInvocation<number>} invocation
    */
    function stockPriceStream(ticker, invocation) {
        var updateFrequency = 1000 /* milliseconds*/;
        var isPending = false;
    
        var timer = setInterval(function() {
            // If there is already a pending request, skip this iteration:
            if (isPending) {
                return;
            }
    
            //Note: In the following line, replace <YOUR_TOKEN_HERE> with the API token that you've obtained through your IEX Cloud account.
            var url = "https://cloud.iexapis.com/stable/stock/" + ticker + "/quote/latestPrice?token=<YOUR_TOKEN_HERE>"
            isPending = true;
    
            fetch(url)
                .then(function(response) {
                    return response.text();
                })
                .then(function(text) {
                    invocation.setResult(parseFloat(text));
                })
                .catch(function(error) {
                    invocation.setResult(error);
                })
                .then(function() {
                    isPending = false;
                });
        }, updateFrequency);
    
        invocation.onCanceled = () => {
            clearInterval(timer);
        };
    }
    CustomFunctions.associate("STOCKPRICESTREAM", stockPriceStream);
    

    The CustomFunctions.associate code associates the id of the function with the function address of stockPriceStream in JavaScript so that Excel can call your function.

  2. Run the following command to rebuild the project.

    npm run build
    
  3. Complete the following steps (for either Excel on the web or Windows) to re-register the add-in in Excel. You must complete these steps before the new function will be available.

  1. Close Excel and then reopen Excel.

  2. In Excel, choose the Insert tab and then choose the down-arrow located to the right of My Add-ins. Insert ribbon in Excel on Windows with the My Add-ins arrow highlighted

  3. In the list of available add-ins, find the Developer Add-ins section and select the stock-ticker add-in to register it. Insert ribbon in Excel on Windows with the Excel Custom Functions add-in highlighted in the My Add-ins list

  1. Try out the new function. In cell C1, type the text =CONTOSO.STOCKPRICESTREAM("MSFT") and press enter. Provided that the stock market is open, you should see that the result in cell C1 is constantly updated to reflect the real-time price for one share of Microsoft stock.

Next steps

Congratulations! You've created a new custom functions project, tried out a prebuilt function, created a custom function that requests data from the web, and created a custom function that streams real-time data from the web. You can also try out debugging this function using the custom function debugging instructions. To learn more about custom functions in Excel, continue to the following article:

Data provided free by IEX. View IEX's Terms of Use. Microsoft's use of the IEX API in this tutorial is for educational purposes only.