FinFolio | Portfolio Management Software Blog

I just blogged about our REST API, an easily accessible way to generate web queries that securely share your FinFolio data with other web sites and applications. This posted generated many questions along the lines of "what specifically can I do with this data".

One way to use the data is with Microsoft Excel. Excel lets you extend FinFolio by using its powerful suite of tools; once the data is in Excel, you can build on it by using formulas, pivot tables, and other standard Excel functionality.

The first time you use the API you will generate a secure API key in FinFolio:

1. Open FinFolio and click File, Options. This opens the Options screen.
2. On the Options screen, click API. This opens the "View and create API keys..." tab.
3. On the "View and create API keys..." tab click Insert new item.... This creates a new row in the API list with an auto-generated random key.
   
4. Enter a Username next to the new API key. This should be the same name as a user who already been granted access to FinFolio data. This API key will be able to see the same data as this user. Note the user does not need to be set up in Active Directory or SQL.
5. Click OK to save the API key and close the screen.

Create your query using the FinFolio API website:

1. In a web browser, navigate to your API site (usually api.yoursite.finfolio.com).
2. Enter the API key into the api_key field at the top of the page.
3. Navigate the API documentation to find the correct query to generate the data you need. For example, if you are trying to show a list of accounts with market values, you would:
   1. Click on Account. This expands the account api section.
   2. In the account api section, click on GET /api/account. This expands the parameters for the account api call.
   3. In the /api/account fields list, enter id, sortName, SimpleValue. This specifies the two fields you want to see plus the account id field.
   4. Click Try it out! and your list of accounts will appear in the Response Body section.
      
   5. Copy the text in the Request URL section. This is your REST API call that can now be reused in other applications. It will look similar to this: 

      https://api.finfolio.com:443/api/account?fields=id%2C%20sortName%2C%20SimpleValue&api_key=A0C11111-6666-4B4B-ACAC-F32F32F32722

4. Now you have a REST API call that generates data in any web browser, Excel or other applications that can make web queries.

Move to Excel and insert the API query data into a spreadsheet:

1. Open Excel 2013, click File New and select Blank Workbook.
2. On the Data ribbon, click From Web. This opens the New Web Query window.
3. In the New Web Query window enter your API string in the Address field and click Go. This will show the data results from the query.
4. Click Import. This processes the data results and shows the Import Data screen.
5. In the Import Data screen, select XML table in existing worksheet and enter $A$1 for the field.
6. Click OK to insert the data into your document. You will now be looking at a document with your list of accounts and values.
   
7. To refresh your queries after FinFolio data has changed, go to the Data ribbon and click the Refresh All button. This will update all queries in your spreadsheet.

Now you can use the data just like you all Excel data--modify it using formulas, link it to other queries, reformat and print it, insert into other Office documents, etc. You can follow a similar process in many other 3rd party applications to reuse FinFolio data and extend our functionality. Please let us know if you discover a unique way of repurposing our data!