Anaconda Code is an Excel add-in that enables users to execute Python or R code directly within Excel. It allows you to create custom Python functions for use in Excel workbooks. In addition, its package management support makes it easy to add or remove packages, significantly extending Excel’s capabilities. With these features, tasks such as querying external APIs for data become possible right inside Excel.

In this tutorial, we will guide you through using Anaconda Code to query the IP2Location.io API and display the results in your workbook. The IP2Location.io service provides a fast and accurate IP geolocation API that allows users to determine a visitor’s geographical location and apply geolocation data in various use cases.

Before we begin, please ensure that Anaconda Code is installed in your Excel application. It can be installed by adding the Anaconda Toolbox Excel add-in. You may refer to their installation guide for detailed steps.

In addition, to make a CORS request to the IP2Location.io API, you will need an active subscription. CORS support is available only for the Starter plan and above. You may view and purchase a plan here: https://www.ip2location.io/pricing

Steps to Use the IP2Location.io API in Anaconda Code

1. In Excel, go to the Formulas tab and click the Code icon.

2. Click the Sign In button, and click it again to proceed.

3. By default, Anaconda Code requires you to sign in before use. If you have not registered before, you may sign up here. You may log in using your Anaconda account or sign in with Google, Microsoft, or GitHub.

4. If this is your first time using it, you will need to create an Anaconda Code cell. For this tutorial, we will use the default settings. Click Create Code Cell to continue.

5. Click the Imports and Definitions icon to import the necessary package. Enter the following to import the required library:

from pyodide.http import pyfetch

6. After that, scroll to the end of the Code tab and paste the provided code snippet:

async def iplio_query(ip: str, column: str | None = None):
    base_api = 'https://api.ip2location.io/?'
    api_key = 'YOUR_API_KEY'
    full_url = f'{base_api}key={api_key}&ip={ip}'
    resp = await pyfetch(full_url)
    data = await resp.json()
    print(type(data))
    if column is not None:
        print(f'column is {column}')
        response = data[column]
        return response
    return data

Note to the API

You must upgrade to any paid plan to be able to use our API to bypass the CORS limitation.

7. Click Apply when you are done.
8. You can now use the custom function to query the IP2Location.io API for geolocation results. For example, if you have an IP address stored in cell A14, simply enter the following formula in any cell you prefer:

=ANACONDA.IPLIO_QUERY(A14)

Conclusion

In summary, Anaconda Code enhances Excel by enabling access to external data through Python or R. It is especially useful when you have a list of IP addresses stored in Excel and want to populate accurate geolocation information using a reliable API. In this tutorial, we have demonstrated how to query IP2Location.io, but you can use the same approach with any other API to achieve similar results.

---