Go to Blog

Integrating with Excel

Suggest Edits

The example here shows how you can pull BIN data into an Excel sheet for the purpose of conducting a deeper analysis of your payment transactions or customer records. You don’t have to write any code for this example.

Much like integrating with Google Sheets, integrating with Excel allows you to pull customer or transactional data from Parrot by Pagos and analyze it in a familiar environment. But with Excel, you won't have to worry about triggering Google quotas!

To allow Parrot and Excel to talk to one another, we've generate a VBA-Parrot library. VBA (Visual Basic for Application) is a programming language used to customize Excel and integrate with third party products—like Parrot! VBA-Parrot builds on the multi-platform VBA library, VBA-tools by Tim Hall. It supports both Windows and Mac versions of Excel, but please note the stability and performance note below.

🚧

Note about Stability and Performance

The VBA-Parrot library can make a large number of queries to the Parrot service. This can be a slow process and can cause Excel to be unstable, especially on Macs where Excel doesn’t really support making URL calls natively. As such, we recommend using this integration at your own risk and saving your work frequently. We're always open to feedback and bug fixes!

📘

Adding VBA-Tools and VBA-Parrot to Excel Manually

If you want to integrate the VBA-tools and Parrot libraries into an existing Excel sheet manually, contact us and we can walk you through it, but we thought we’d spend the time on this page showing an example of using the service in Excel.

Before You Begin

Before you can integrate Parrot with Excel, you'll need the following:

Step 1: Download VBA-Parrot and open Excel

To integrate Parrot with Excel, start in your browser where you have VBA-Parrot – Blank.xlsm open:

  1. On the right side of the page, click Download.
  2. Open the downloaded XLSM file.
  3. In the pop-up window, click Enable Macros.

This should automatically open a blank Excel spreadsheet; rest assured, the code needed to access Parrot is already there!

📘

For the code curious...

You can view the full VBA-Parrot code on Github.

Step 2: Query Parrot

After you download the VBA-Parrot library and open the Excel sheet, you can start querying the service.

To make your first Parrot BIN query:

  1. In cell B1, paste your API key.
  2. Go to cell A1 and type in an example BIN, such as 535316.
  3. Go to cell B2 and type in the function to call Parrot—note you need to replace the blue text (including brackets) with your API key:
=ParrotBINGet("https://parrotv2.prod.pagosapi.com/cards", $B$1,A2,,FALSE)

Your Excel Sheet1 should now show the attributes of the cards on that BIN.

In Excel, the column headers are not printed to make integrating into existing work easier. As a reminder of our JSON response, the columns of the resulting data set are currently mapped 1:1 with the JSON object, and include card attributes:

  • Number Length
  • Number Luhn
  • Scheme
  • Type
  • Brand
  • Prepaid
  • Country Numeric
  • Country Alpha2
  • Country Name
  • Country Currency
  • Country Latitude
  • Country Longitude
  • Bank Name
  • Bank Url
  • Bank Phone
  • Bank City

ParrotBINGet function parameters:

  1. URL
  2. Your API Key
  3. BIN number to search
  4. The XMLPath string defining the elements you want (see our API reference)
  5. Use Caching, True or False (Please see the note about caching in the final section below)
  6. Use Enhanced Data, True or False

You can customize the call to request specific parts of the data set. For example

=ParrotBINGet("https://api.pagos.ai/cards", $B$1,A2,"/card/product,/card/network,/card/type,/card/country/alpha2,/card/bank/name",FALSE)

Finally, you can also query enhanced data, but you will need to set the parameter enhanced to true and define the XMLPATH for those attributes. You can use BIN: 423312 to test.

Big Queries: How Long Do I Wait?

If you are pulling down a lot of data via the API in Excel, you’ll be able to monitor the status of all those queries by looking at the Calculating information in the bottom right of the Excel window — this can help manage expectations. It’ll look like this if you are doing 100s of rows at a time.

VBA-Parrot Caching

Given that VBA can be a bit slow and heavy, we’ve built in a simple caching mechanism. While your Excel sheet is open and running, we'll build up a local cache of BIN data so you don’t hit the Pagos service every time Excel re-calculates. You may control this behavior using the 5th function parameter—useCache. The cache is not saved, so when you re-open an Excel sheet, the BIN data will be refreshed with the latest from the server. If you're working with a lot of BIN data in a single sheet, we recommend you set Excel Calculate to Manual.

Updated about 1 year ago

What’s Next