Choose the Information You Want to View

Open the Open Rerporting API Documentation to see what information you can retrieve from Care Control.

Browse the available reporting options and find the information you would like to use in Excel. You'll select and copy the relevant option later in this guide.

Create a New Excel Workbook

Open Microsoft Excel and select Blank workbook to create a new spreadsheet.

Open the Data Tab

At the top of Excel, select the Data tab. This contains the tools needed to connect to external data sources.

Select From Web

Within the Data tab, select From Web.

This allows Excel to retrieve information from a web address, including an API endpoint.

Copy the API Base URL

Open the Open Reporting API and copy the following Base URL:

https://auth.carecontrolsystems.co.uk/CareControl.ClientReporting.API

You'll need this address to connect Microsoft Excel to Care Control. In the next steps, you'll add your chosen API endpoint to the end of this URL.

Paste the Base URL into Excel

Return to Excel and paste the copied address into the URL field in the From Web window.

Leave this window open while you find the specific API endpoint you want to use.

Copy an API Endpoint

Return to the Open Reporting API documentation and find the information you want to retrieve.

Select the Copy icon beside the relevant endpoint to copy its address.

Complete the API URL

In Excel, paste the copied endpoint onto the end of the Base URL.

Make sure the two parts form one complete web address, then select OK.

Select Basic Authentication

Excel will display a window asking how you want to connect to the data source.

Select Basic from the available authentication options.

Copy Your API Key

Return to Care Control and open the user page containing your Open Reporting API credentials.

Locate your API Key and copy it.

Enter Your API Key

Return to Excel and paste your API Key into the User name field in the Basic authentication window.

Copy Your API Secret

Return to your Care Control user page and locate the API Secret.

Copy the secret, making sure you do not accidentally include any extra spaces.

Enter Your API Secret

Paste your API Secret into the Password field in Excel.

Your API Key and API Secret are used to authenticate your connection to Care Control.

Connect to the Open Reporting API

Once both credentials have been entered, select Connect.

Excel will attempt to connect to the Open Reporting API and retrieve the information from your selected endpoint.

Open the Power Query Editor

Once Excel retrieves the information, a preview window will appear.

Select Transform Data to open the Power Query Editor, where you can prepare the information for your spreadsheet.

Convert the Data into a Table

In the Power Query Editor, select To Table.

This converts the retrieved information into a table format that Excel can work with.

Confirm the Table Conversion

A window will appear with options for converting the data.

Leave the settings as shown and select OK to continue.

Expand the Table Columns

Locate the Expand icon in the column heading and select it.

This allows you to display the individual fields from the API response as separate columns.

Remove the Column Name Prefix

In the window that appears, untick Use original column name as prefix.

This helps keep your column headings shorter and easier to read. Select OK to apply the changes.

Load the Data into Excel

Once your information is arranged correctly, select File and then Close & Load.

Excel will load the information into your workbook as a table, ready for you to review and use.

Refresh Your Data

Whenever you want to retrieve updated information from Care Control, open the Data tab and select Refresh All.

Excel will run the saved connection again and update your table with the latest available information.