What is the ALIS API Export?

This data export tool gives the freedom to access hundreds of data points captured in ALIS for your own discrete needs. ALIS provides a list of different mergeable data exports for you to pull into your Excel worksheets.

If you are new to APIs, here are a couple of good reference guides for background: 

• https://a16z.com/2018/03/09/api-world-summit/
• https://www.freecodecamp.org/news/what-is-an-api-in-english-please-b880a3214a82/

1 - Set up your access to the ALIS API Exports

1. It is necessary for your onboarding manager to create and enable this API access. We will provide you with a unique and secure username and password for your company. We store these securely on our end.
2. Once you have the credentials, chose a data export you want to view in Excel. Here is the index of the data exports: https://api.alisonline.com/index.html. Click the rows to see the values included in each export group.
   
   
   

2 - Import information from ALIS to Excel

1. In Excel, open the Data menu. In the Get & Transform Data section on the left, select "From Web".
   • Select Advanced, and in the URL parts, paste https://api.alisonline.com and the URI stem from the data export you chose above. For example:  https://api.alisonline.com/v1/export/communities
   • Next, skip down to "HTTP request header parameters (optional)". Here we want to choose Accept from the first menu and type "text/csv" in next text field.
   • Click OK.
     
2. In the next window to Access Web content, click Basic on the left.
   • Paste your User name and Password given to you in section 1 above!
   • The menu "Select which level to apply these settings to" should say https://api.alisonline.com/
   • Click Connect. 
     
     
3. Next, you have a preview of the data that will load in to your worksheet. Here you have options to Transform your data, Load it into the current sheet, or Load it somewhere else. 
   
   
4. Once the data is loaded, all values of the data export group will display as columns in a new tab of your workbook. 

3 - Steps for merging multiple data exports in Excel

1. Open the Query menu toward the right. In the Combine section, click Merge.
   • First, use the first two menus to select the two worksheets (Excel tabs) you want to merge.
   • For each worksheet, click on the reference columns. These columns should have the same column header and contain same strings of data.
   • For Join Kind, select Left Outer.
   • When the Privacy levels window pops up, set to Private and click Save.
   • Click OK. 
2. The opens for us the Power Query Editor. Here we will create our own merged workbook.
   • Find the highlighted column you joined in the previous step, and click the button at the top left of the column showing two arrows pointing away from each other.
   • Select from the list which columns you want from the secondary table. By default all are selected!
   • Click Close & Load on the top left of the window. 

FAQ

I'm running into issues importing the data to Excel. How can I change credentials and start over?

• Open the Data menu. In the Get & Transform Data section, click Get Data > Data source settings...
  • Click the Global Permissions radio button.
  • Select https://api.alisonline.com/
  • Either click "Edit Permissions..." to open Edit Permissions. Here you can click "Edit..." to review and update your credentials for your company's API.
• You can also click Clear Permissions so that Excel will forget the credentials.

How do I change the status and date filters of an existing query?

Some queries have filters on by default. One example is occupancy queries only show you the current month and year's worth of data. Here's how to update that:

1. Find the query in the API Index page (https://api.alisonline.com/index.html)
2. Click the Try it out button
3. Now the parameters are editable. Type the month and year you wish to query, and then click Execute.
4. This will return an error on the index page, but you now have a URL containing your parameters. Scroll down to find this URL under Responses. All we need to do is run the query from this appended URL.
5. In your Excel workbook, go to the Data toolbar and click Get Data. Click the option Data Source Settings.
   • In the pop-up, click the "Change Source.." button and paste the new appended URL that contains your parameters.

How do I fix a "Column not found" error?

If you have been using an ALIS API query in your workbook for awhile and suddenly an error stops you to say "Column not found", this is possibly due to ALIS having made an update. Our engineers have likely added a new column, and your installation of Excel has cached the older version of the query.

To fix this error, all you need to do is clear the cache in Excel! 

• In the Data toolbar, click Get Data and select Query Options.
• Then select the Data Load menu and scroll down to Data Management Options. Click the Clear Cache button.