May 07, 2022

In this article:

Getting started

Preqin for Excel enables you to seamlessly access our comprehensive data directly in Excel, eliminating the need to navigate between your spreadsheets and Preqin Pro. Instantly sync your Preqin Saved Searches, Target Lists, and Custom Benchmarks, or update individual cells with specific Preqin data points using our powerful Formula Builder.

Once installed, a Preqin for Excel button will appear in your Home Excel menu. From this pane you can access all the Preqin features that are available in Excel.

Open the Formula Builder and other related options
Open the Template Library
Open the Preqin Data Loader
Open the Preqin ID search pop-up

By clicking on the three dots in the top right corner of the plugin, you can:

Open Preqin Pro in your default web browser
Log out of Preqin for Excel

Login/logout

1. Open Excel, click on ‘Preqin for Excel’ in the Home tab, and this will open the login page.

2. When the login page opens, click ‘Sign In’

3. A pop-up will appear. Type in your Preqin Pro login details and click ‘Sign in’.

4. Once you’re logged in, the selected Preqin for Excel function will open. You will remain logged in as a user for all features unless you log out.

When opening the Excel sheet again, the plugin will re-verify your credentials. This may take 2-3 seconds.

1. By clicking on the three dots in the top right corner of the plugin, you can log out of Preqin for Excel.

Preqin ID lookup

All formulas must have a single Preqin ID as parameter 1. This ID can be retrieved using the Preqin ID search tool. Enter a firm, asset, or fund name into the search bar. A pop-up will then display any potential matches.

Formula Builder

Preqin for Excel now includes a Formula Builder which gives you access to Preqin data points directly in your Excel workflow. At the moment, Preqin for Excel includes the following datasets:

Most up-to-date data only:

Investors
Fund managers
Funds
Investment consultants
Company Intelligence (Company search)

Historical data:

Fund performance
Company Intelligence (Company financials)

For a full list of available data points, simply request a copy of the data dictionary.

To access the Formula Builder, click on the Preqin tab in the Excel ribbon, select the Formula Builder dropdown, and click on ‘Formula Builder’. Alternatively, you can manually enter arguments directly into a cell.

When using the Formula Builder, the first step is to select the ‘Data Type’ you wish to return. This is always step one when building a formula.

Formulas/prefix

There are multiple formulas on Preqin for Excel, each based on the different ‘Data Types’ available.

This is automatically populated when selecting ‘Data Type’ in the Formula Builder.

Building formulas for the most up-to-date data (investors, fund managers, funds, investment consultants, company search), Preqin for Excel supports 2-way and 3-way arguments:

2-way arguments return basic data and some simple financial data in a single format type (e.g., firm name)
3-way arguments return financial data typically where the format must be specified (e.g., currency or percentage)

The parameters in bold are required for all formulas; non-bold parameters can be left blank e.g. (“”) to return default data. The requirements for argument 3, ‘Format’, will vary depending on the data that you wish to return.

Parameter 1: ID
Parameter 2: Field
Parameter 3: Format

Examples

Investors

=PREQIN.LP(12345,”FIRM_NAME”,””)
=PREQIN.LP(12345,”AUM”,”USD”)

Fund managers

=PREQIN.GP(12345,”FIRM_NAME”,””)
=PREQIN.GP(12345,”ALTERNATIVES_ASSETS_UNDER_MANAGEMENT”,”USD”)

Funds

=PREQIN.FUND(12345,”FUND_NAME”,””,””,””)
=PREQIN.FUND(12345,”FUND_SIZE”,””,””,”USD”)

Investment consultants

=PREQIN.IC(12345,”FIRM_NAME”,””)
=PREQIN.IC(12345,”TOTAL_AUA”,”USD”)

Company Intelligence

=PREQIN.CI(12345,”COMPANY_NAME”,””,””,””)
=PREQIN.CI(12345,”TOTAL_RAISED”,””,””,”USD”)

When building formulas to surface historical data (fund performance, company financials, etc.), Preqin for Excel requires 4-way and 5-way arguments.

The parameters in bold are required for all formula;, non-bold parameters can be left blank e.g. (“”) to return default data. The requirements for argument 3, 4, and 5 will vary depending on the data that you wish to return.

Parameter 1: ID
Parameter 2: Field
Parameter 3: Period
Parameter 4: Date
Parameter 5: Format

Cell referencing

Traditional Excel processes such as cell referencing allow you to identify data in a cell on a worksheet. This function also works with Preqin data. For example, if a cell includes a Preqin ID such as B1 = 12345, the formula will return the same results:

=PREQIN.LP(12345,”AUM”,”USD”)

=PREQIN(B2,”AUM”,”USD”)

This cell referencing works with any step of the formula, and you can build fully dynamic Preqin formulas. For example, if B2 = 12345, C2 = AUM, D2 = USD, the formula will return the same results: =PREQIN.LP(12345,”AUM”,”USD”) =PREQIN.LP(B2,C2,D2)

Range formulas

Preqin for Excel enables users to pull in a range of data points directly with one formula, via the range formulas option. Just like other data points, the formula will vary according to the data type.

To differentiate, the word ‘Range’ can be found in the field name along with a (i) reference directly in the Formula Builder interface

Formula builder troubleshooting

Error codes
In addition to existing Excel error codes, you may return a Preqin-specific error code when using our formulas.

If you run into any issues or questions that are not covered in this document, please contact us via datadelivery@preqin.com.

Data Loader

Preqin for Excel’s Data Loader allows you to access predefined Saved Searches, Target Lists, and Custom Benchmarks directly in Excel, without needing to navigate to Preqin Pro to export.

Saved Search

Creating a Saved Search on Pro

To learn how to create and manage a Saved Search in Preqin Pro, please read this Pro Tip article from the Preqin Help Center.

Please note: If the dataset is large, it will load rows in batches while the loading circle continues to spin until the process is complete. Loading speeds are dependent on data size and internet connection.

You will be notified once the data load is complete
The data will be loaded to a separate sheet using the same name as your Saved Search title

Note: Editing the name of the sheet will impact your ability to refresh the data.

Saved Searches – scope of data

The following datasets for Saved Searches can be loaded:

Investor
Fund
Fund performance
Fund manager
Service provider
Investment consultant

Target List

Creating a Target List on Pro

To learn how to create and manage a Target List in Preqin Pro, please read this Pro Tip article from the Preqin Help Center.

Note: Target Lists have a predefined set of columns in Preqin Pro and when pulling it into Excel via the plugin. If you manually add columns to a Target List via the ‘Managed Columns’ feature in Preqin Pro, this change will not be reflected when pulling the Target List via the Excel plugin. See the FAQ below for a list of Preqin Pro’s default columns across all types of Target Lists.

Note: Editing the name of the sheet will impact your ability to refresh the data.

Target List – scope of data

The following Target List datasets can be loaded:

Investor
Fund
Fund performance
Fund manager
Service provider
Investment consultant

Custom Benchmark

Creating a Custom Benchmark on Pro

To learn how to create and manage a Saved Search in Preqin Pro, please read this Pro Tip article from the Preqin Help Center.

Note: If the dataset is large, it will load rows in batches while the loading circle continues to spin until the process is complete. Loading speeds are dependent on data size and internet connection.

You will be notified once the data load is complete
The data will be loaded to a separate sheet using the same name as your Custom Benchmark title

Note: Editing the name of the sheet will impact your ability to refresh the data.

Refreshing data

Refreshing data is simple and can be done in one of two ways. You can either:

Refresh/overwrite data in a previously exported sheet
Reload a new sheet with updated data

It is important to remember NOT to change any of the names of the sheets. By doing so, the logic for refreshing the data will no longer work.

Refresh data in an existing sheet

If you wish to overwrite a previously exported sheet with updated data points:

Note: If the dataset is large, it will load rows in batches and the status of the refresh will be displayed. Loading speeds are dependent on data size and internet connection. Once completed, the below ‘Refresh Complete’ screen will appear.

Refresh data as a new sheet

If you wish to refresh data while maintaining a previous version, repeat the steps in ‘Loading a Saved Search’ and load the data. A new sheet will appear with the same name, but with a number appended to the end. For your first ‘refresh’ of data it will append a 1, for your second a 2, and so on.

Error messaging

When trying to load a Saved Search, you may receive an error message. Please click the ‘Load Again’ button to reload the data. If the error continues, please email datadelivery@preqin.com and we will investigate the issue further.

Templates

With Preqin for Excel you can access flexible, prebuilt templates. These downloadable templates are fully customizable and preloaded with Preqin formulas.

Opening the Template Library

To access the templates, simply select the templates icon in the home window of the plugin, where you will see a list of all available templates. You can then narrow down your results using the search bar.

By hovering your cursor over specific templates, you can also see more information regarding each template.

Once you have selected a template click ‘Open’, and it will appear in a new workbook.