Combine Forecast with Excel for no limit data reporting to share with team members and key stakeholders across various apps, internal systems, services or third party apps. Enabling the Excel integration allows users to analyze data outside of the Forecast platform where users can customize, extract and collect data with one click.
This article contains:
Overview of the Microsoft Excel Integration
This integration is useful as you have to go through setting it up only once. From then after you just ask Excel to refresh the data based on your current setup using the Refresh All button in the Data tab. You can export data by obtaining an API-key from Forecast, and connecting it to Excel.
Setting up the integration in Microsoft Excel
- Open Microsoft Excel, and create a blank workbook.
Go to the Data tab in the menu at the top of Excel. Click New Query, and select From Other Sources → From Web.
Click Advanced in the new window
Go to the Admin Settings in Forecast using your personal menu at the top right.
- Go to the API-keys tab and click Generate new API-key.
Note: Your new API key will be generated automatically.
- Copy the new API-key, and go back to Excel.
- Add the information as showcased in the screenshot above, i.e.URL and the GET name of the data you wish to export from Forecast. As an example, set the URL and GET name to the following:
GET name: e.g. "projects", "persons", or "time_registrations"
- Remember about the Parameters at the bottom of the window, or find the information using the GitHub repository with our API guidelines. Add the parameters together with your unique API-key from Forecast in the second field:
HTTP request header parameters: "X-FORECAST-API-KEY" and API-key "X-FORECAST-API-KEY"
- Click OK.
- The data has now been downloaded. Click To Table...
- Click OK.
- Click the small button at the top right of the first column as shown in the screenshot below. Check or uncheck data as you see fit, and click OK.
- The data will now show up in your spreadsheet.
- Right-click on the new item in the right sidebar, and go to Properties to rename the data set and enable/disable data points if necessary. A good practice is also to rename the tab/sheet at the bottom if you plan to work with several data sets.
Note: Remember to name the tabs properly to distinguish later on.
Click Close & Load when you are done.
You can add more data sets to the same file which makes you able to link data sets, e.g. Person ID with that particular person's time registrations using the "persons" and "time_registrations" API endpoints. Additionally, you can get a better visual view by using the Pivot Table feature.