TRIEDATA | County Portfolio

Connect With Excel 2013

Excel 2013 has introduced a new feature to access a RESTful web service directly from spreadsheet. This new function named "webservice()" allows the user to call a web service directly and populate the spreadsheet fields with the resulting XML output. This section shows you how to connect to our web services using Excel 2013.

Excel Webservice function allows you to invoke TRIEBRARY services directly from your excel. You need to have Microsoft office Excel 2013 or later versions. Click here to Learn more about Excel WEBSERVICE

A service request is made from the client browser and the service response is generated by TRIEBRARY.

EXCEL function: WEBSERVICE(url)

Excel WEBSERVICE
Example: WEBSERVICE("https://smartwebgateway.cloudapp.net/SmartWeb.svc/ssl/GetORBasicInfoByLegalDescription/subscribercode/TOKEN/99999/TD%20#1028/91TR 1883 UT 1 FL")
Server URL	https://secure.triedata.com/SmartWeb.svc/ssl/
Web Service	GetORBasicInfoByLegalDescription
Parameter 1	Subscriber Code (This is the code assigned to your organization)
Parameter 2	TOKEN (Unique subscriber token)
Parameter 3	99999 (Test FIPS code)
Parameter 4	Legal description

Step 1	Verify your subscriber account Use your login name and password to log into your account in TRIEBRARY. Click on your user name (top right corner) to manage your account. Your user account page will look similar to the window below. Click on "My Account" to see details about your account. Your account will look similar to the window below. Note down or copy Subscriber code and Token for your account as you will need this information for all transactions with TRIEBRARY. Make sure that you have subscribed to transactions and agreed to publisher terms. Window below shows subscribed transactions and your acceptance of the publisher terms. You will need to agree to publisher transactions and subscribe to individual transactions before you can access the set. Note: Price may vary between transactions and publishers.
Step 2	Open Excel and create a new blank workbook We will use a new blank workbook for our testing purposes. In this example, we will invoke Excel2013 function named "WEBSERVICE" and "FILTERXML" to request and process service result. We will be using TRIEBRARY service GetTaxInfo for our test purposes. This service returns the tax information for a given parcel id.
Step 3	Invoke web service call Next step is to setup the fields for invoking the web service. First, we are going to create a set of input fields and then create the formulae's for retrieving the data through webservice Open a new blank workbook Type "Enter URL" in cell A1 Type "Enter service name" in cell A2 Type "Enter Subscriber ID" in cell A3 Type "Enter Token" in cell A4 Type "Enter Publisher code" in cell A5 Type "Enter Parcel ID" in cell A6 Type "RESTful URL" in cell A7. Populate the field B7 with the formulae "=B1&"/"&B2&"/"&B3&"/"&B4&"/"&B5&"/"&B6". This will form the RESTful API URL for retrieving tax information. Type "Data" in cell A8. Populate the field B8 with the formulae "=WEBSERVICE(B7)". This will invoke Excel 2013 function webservice and fetch data from our services Now we are all set to call the web service and retrieve the XML output. Before that make sure to populate the fields in column B with your account details as shown in image below. Note: You can use test FIPS code as 99999 and parcel id as 0000100000
Step 4	View the results Let us create a title for our output columns first. We will be using Excel function FILTERXML to parse the data returned by our service. You can learn about this function by visiting FILTERXML function Since the fields contain common headers, go ahead and create a new row with the data header in row 10. Enter the header as "//GetTaxInfoResponse/GetTaxInfoResult/a:taxroll/a:TaxRoll/a:" in column B10. We will be using this information while parsing the data Type the following text in cell A12 through J12: Parcel_Id, Publisher_Code, Street_Name, Street_No, Tax_Balance, Tax_Year, Total_Paid, Total_tax, Total_tax, Escrow_Code Copy and paste the following formulae to its correspnding cell in A13 through J13 ==FILTERXML($B$8,$B$10&A12) Thats it. If everything goes right, you will see the results of the XML web service parsed into individual column in an excel sheet Download sample file Note: EXCEL 2013 has a timer which returns "#VALUE" error if the WEBSERVICE function did not return any value within a period of three seconds. You may also see a "#VALUE" error if the returned XML is larger than the cell limit (32767 characters).

Learn About SmartWeb

Connect With Excel 2013

Links

Contact Us