Log into Trovata and navigate to the developer section in the menu. Create a new application and make sure to give it access permissions for balances and transactions at a minimum.

Copy the credentials from the application page:

Download this template : Trovata Excel Connector.xlsx

In a Windows version of Microsoft Excel open the template file and navigate to the tab named Trovata Setup if it is not already there when it opens.

Click the Data section in the ribbon and then click on Get Data>From Other Sources>Blank Query

Click on the advanced editor button in the window that opens

You should see a window open that has some code in it

Delete all of the code that is in the window that opens so that it is empty

Paste the following code into the window:

let

// Read setup table

setupTable = Excel.CurrentWorkbook(){[Name="Trovata_Setup"]}[Content],

// Lookup values from labels

appId = Text.Trim(Record.Field(Table.SelectRows(setupTable, each [Parameter] = "App ID"){0}, "Value")),

secret = Text.Trim(Record.Field(Table.SelectRows(setupTable, each [Parameter] = "Secret"){0}, "Value")),

companyId = Text.Trim(Record.Field(Table.SelectRows(setupTable, each [Parameter] = "Company ID"){0}, "Value")),

startDate = Text.Trim(Record.Field(Table.SelectRows(setupTable, each [Parameter] = "Start Date"){0}, "Value")),

endDate = Text.Trim(Record.Field(Table.SelectRows(setupTable, each [Parameter] = "End Date"){0}, "Value")),

// API config

baseUrl = "https://api.trovata.io",

relativePath = "developer/data/v1/transactions",

// Pagination loop

PageRange = List.Numbers(0, 10),

GetPage = (offset as number) =>

let

pageResponse = Web.Contents(baseUrl,

[

RelativePath = relativePath,

Query = [

start_date = startDate,

end_date = endDate,

offset = Text.From(offset),

limit = "100",

fullAccountNumbers = "true"

],

Headers = [

#"appid" = appId,

#"secret" = secret,

#"companyid" = companyId

]

]),

parsed = Json.Document(pageResponse),

tx = parsed[transactions]

in

tx,

allPages = List.Combine(List.Transform(PageRange, each GetPage(_))),

output = Table.FromRecords(allPages),

#"Expanded tags" = Table.ExpandListColumn(output, "tags"),

#"Expanded account" = Table.ExpandRecordColumn(#"Expanded tags", "account", {"institutionName", "institutionId", "accountNumber", "accountName", "accountId"}, {"account.institutionName", "account.institutionId", "account.accountNumber", "account.accountName", "account.accountId"}),

#"Expanded metadata" = Table.ExpandRecordColumn(#"Expanded account", "metadata", {"narrativeText", "creditDebitIndicator", "type", "amount", "merchantDetails"}, {"metadata.narrativeText", "metadata.creditDebitIndicator", "metadata.type", "metadata.amount", "metadata.merchantDetails"}),

#"Expanded metadata.amount" = Table.ExpandRecordColumn(#"Expanded metadata", "metadata.amount", {"amount", "currency"}, {"metadata.amount.amount", "metadata.amount.currency"}),

#"Expanded metadata.merchantDetails" = Table.ExpandRecordColumn(#"Expanded metadata.amount", "metadata.merchantDetails", {"merchantName"}, {"metadata.merchantDetails.merchantName"}),

#"Expanded metadataDisplay" = Table.ExpandListColumn(#"Expanded metadata.merchantDetails", "metadataDisplay"),

#"Expanded metadataDisplay1" = Table.ExpandRecordColumn(#"Expanded metadataDisplay", "metadataDisplay", {"blockType", "blockValues"}, {"metadataDisplay.blockType", "metadataDisplay.blockValues"}),

#"Expanded metadataDisplay.blockValues" = Table.ExpandListColumn(#"Expanded metadataDisplay1", "metadataDisplay.blockValues"),

#"Expanded metadataDisplay.blockValues1" = Table.ExpandRecordColumn(#"Expanded metadataDisplay.blockValues", "metadataDisplay.blockValues", {"displayKey", "displayValue"}, {"metadataDisplay.blockValues.displayKey", "metadataDisplay.blockValues.displayValue"})

in

#"Expanded metadataDisplay.blockValues1"

Now click Done and wait for the preview to load. Rename the query by right clicking on the name in the left column and change the name to Transactions

Click the Load and Save button at the top left ribbon and a new tab will appear that includes all of the data from the query.

Clicking on Refresh All will refresh the transactions with the latest data that you specify in the date range for up to 1000 transaction records.

Once again, in the Data Section of the ribbon click on the Get Data button on the left and select the Blank Query Option

Once again click Advanced Editor and clear out the code in the window that opens

Enter the following code into the Query:

let

// Read values from Trovata_Setup table

setupTable = Excel.CurrentWorkbook(){[Name="Trovata_Setup"]}[Content],

appId = Text.Trim(Record.Field(Table.SelectRows(setupTable, each [Parameter] = "App ID"){0}, "Value")),

secret = Text.Trim(Record.Field(Table.SelectRows(setupTable, each [Parameter] = "Secret"){0}, "Value")),

companyId = Text.Trim(Record.Field(Table.SelectRows(setupTable, each [Parameter] = "Company ID"){0}, "Value")),

startDate = Text.Trim(Record.Field(Table.SelectRows(setupTable, each [Parameter] = "Start Date"){0}, "Value")),

endDate = Text.Trim(Record.Field(Table.SelectRows(setupTable, each [Parameter] = "End Date"){0}, "Value")),

// API configuration

baseUrl = "https://api.trovata.io",

relativePath = "developer/data/v1/accounts/balances/historical",

// Call the endpoint

response = Web.Contents(baseUrl,

[

RelativePath = relativePath,

Query = [

start_date = startDate,

end_date = endDate,

fullAccountNumbers = "true"

],

Headers = [

#"appid" = appId,

#"secret" = secret,

#"companyid" = companyId

]

]

),

json = Json.Document(response),

records = json[accountBalances],

output = Table.FromRecords(records),

#"Expanded balances" = Table.ExpandListColumn(output, "balances"),

#"Expanded balances1" = Table.ExpandRecordColumn(#"Expanded balances", "balances", {"date", "bankClosingAvailable", "bankOpeningLedger", "bankClosingLedger", "bankCurrentAvailable", "bankCurrentAvailableConverted", "bankOpeningAvailable", "bankClosingAvailableConverted", "bankClosingLedgerConverted", "bankOpeningAvailableConverted", "bankOpeningLedgerConverted", "bankSynthetic", "bankSyntheticConverted", "compositeBalance", "compositeBalanceConverted", "compositeField", "compositeFieldConverted", "trovataOpeningBalanceType", "trovataClosingBalanceType", "trovataOpeningBalance", "trovataOpeningBalanceConverted", "trovataClosingBalance", "trovataClosingBalanceConverted", "conversionRate", "conversionRateTimestamp", "isFilled", "isCalculated"}, {"balances.date", "balances.bankClosingAvailable", "balances.bankOpeningLedger", "balances.bankClosingLedger", "balances.bankCurrentAvailable", "balances.bankCurrentAvailableConverted", "balances.bankOpeningAvailable", "balances.bankClosingAvailableConverted", "balances.bankClosingLedgerConverted", "balances.bankOpeningAvailableConverted", "balances.bankOpeningLedgerConverted", "balances.bankSynthetic", "balances.bankSyntheticConverted", "balances.compositeBalance", "balances.compositeBalanceConverted", "balances.compositeField", "balances.compositeFieldConverted", "balances.trovataOpeningBalanceType", "balances.trovataClosingBalanceType", "balances.trovataOpeningBalance", "balances.trovataOpeningBalanceConverted", "balances.trovataClosingBalance", "balances.trovataClosingBalanceConverted", "balances.conversionRate", "balances.conversionRateTimestamp", "balances.isFilled", "balances.isCalculated"})

in

#"Expanded balances1"

Click Done, and wait for the balances to preview. Right Click on the Query1 name in the column on the left and rename it to Balances. Click Close and Load and the new tab should appear in your workbook named Balances.

Every day that you want to use this tool all you have to do is change the dates in the Trovata Setup with the dates you would like to query. MAKE SURE TO CONVERT THE CELL TO A STRING LITERAL by putting a ‘ in front of the date you are entering. Like this: ‘2023-09-27 THEN hit enter.

After you have changed the date click on the Refresh All Button in the ribbon and you will get your new data!

If there are any issues when connecting to the Trovata API from within Excel it may be an issue related to public access of the web service. To adjust this open the Query Tab in the ribbon and click on "Edit"

Click on "Data Source Settings" and in the dialogue that opens click on https://api.trovata.io in the list and click on "edit permissions"

Ensure that Privacy Level is set to none and that credentials access is set to anonymous. This is due to the fact that the Trovata API utilizes a custom three layer security credential to secure your access to the Trovata Platform.