Connect to L3 Workbench

https://developer.precisionlender.com/l3-workbench-api-v1/docs/workbench/auth

Create a Service User in PrecisionLender Administration tab

import http.client
import getpass
import json
import urllib.parse
import pandas as pd
from io import StringIO
import os

clientid = "a9cdadd4-1324-4a7a-842e-fe8fc9fe37f1"
username = "L3_Workbench_Service_User"
password = getpass.getpass()

········

Connect to L3 Workbench as Service User

conn = http.client.HTTPSConnection("api.precisionlender.com")

headers = {
    'clientid': clientid,
    'username': username,
    'password': password
    }

conn.request("POST", "/L3/v1/workbench/auth", headers=headers)

res = conn.getresponse()
data = res.read()

print(data.decode("utf-8"))

{"token_type":"Bearer","expires_in":3599,"ext_expires_in":3599,"access_token":"eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsIng1dCI6ImtnMkxZczJUMENUaklmajRydDZKSXluZW4zOCIsImtpZCI6ImtnMkxZczJUMENUaklmajRydDZKSXluZW4zOCJ9.eyJhdWQiOiJodHRwczovL3N0b3JhZ2UuYXp1cmUuY29tIiwiaXNzIjoiaHR0cHM6Ly9zdHMud2luZG93cy5uZXQvZjA2ZDQ1OWItZDkzNS00YWQ3LWE5ZDMtYTgyMzQzYzRjOWRhLyIsImlhdCI6MTYwNjkyNTEzMSwibmJmIjoxNjA2OTI1MTMxLCJleHAiOjE2MDY5MjkwMzEsImFpbyI6IkUyUmdZRENzWC9VcjFrVnhPNzlXclhINkNiWUxBQT09IiwiYXBwaWQiOiIwN2JlNDJlYS0xY2Y2LTQ5YTYtYmIzYy1lMTVmOGU2Y2U2MTciLCJhcHBpZGFjciI6IjEiLCJpZHAiOiJodHRwczovL3N0cy53aW5kb3dzLm5ldC9mMDZkNDU5Yi1kOTM1LTRhZDctYTlkMy1hODIzNDNjNGM5ZGEvIiwib2lkIjoiNDhlYzMyMzgtYTZjZi00NTYwLWE4MjAtYzBiNjgzOGE1NGI5IiwicmgiOiIwLkFBQUFtMFZ0OERYWjEwcXAwNmdqUThUSjJ1cEN2Z2YySEtaSnV6emhYNDVzNWhkYkFBQS4iLCJzdWIiOiI0OGVjMzIzOC1hNmNmLTQ1NjAtYTgyMC1jMGI2ODM4YTU0YjkiLCJ0aWQiOiJmMDZkNDU5Yi1kOTM1LTRhZDctYTlkMy1hODIzNDNjNGM5ZGEiLCJ1dGkiOiJYMVZnWGpIZ3pVQ1dzNTQ5UURBVkFBIiwidmVyIjoiMS4wIn0.TvcQU-_V0H-r2HVhXg2ck-lZqMPA3aeN8yvgLz4DnVp4gDTrGjKNcVCz1VpMylFBlWNjVNzsm4YSbm6POOwNB9MFygODGu9blLBRPyTD_cxXjGsplKU9oFykdC4arNaUJyILQZrvIA0g8T_8S1OJ95305VR87OHuPgfcZxSW5WYBn-ep7UO8BsrlrOgsn_Due2i_U-2k60Iw1VO8mX_Zmq9UbKBDlVCPESRFV0YjRLL8ZzbTEO5f8r7A0xOGR4nqGFkKivuHDUI-AFtTrPTsH85JpYHwGe3Fw8c5LzkPBBGzsi4ibPL7l1oqE-sYnEWnvkc-x_3pkpGj2y76wRqPOw"}

access_token = eval(data.decode("utf-8"))["access_token"]

print(access_token)

eyJ0eXAiOiJKV1QiLCJhbGciOiJSUzI1NiIsIng1dCI6ImtnMkxZczJUMENUaklmajRydDZKSXluZW4zOCIsImtpZCI6ImtnMkxZczJUMENUaklmajRydDZKSXluZW4zOCJ9.eyJhdWQiOiJodHRwczovL3N0b3JhZ2UuYXp1cmUuY29tIiwiaXNzIjoiaHR0cHM6Ly9zdHMud2luZG93cy5uZXQvZjA2ZDQ1OWItZDkzNS00YWQ3LWE5ZDMtYTgyMzQzYzRjOWRhLyIsImlhdCI6MTYwNjkyNTEzMSwibmJmIjoxNjA2OTI1MTMxLCJleHAiOjE2MDY5MjkwMzEsImFpbyI6IkUyUmdZRENzWC9VcjFrVnhPNzlXclhINkNiWUxBQT09IiwiYXBwaWQiOiIwN2JlNDJlYS0xY2Y2LTQ5YTYtYmIzYy1lMTVmOGU2Y2U2MTciLCJhcHBpZGFjciI6IjEiLCJpZHAiOiJodHRwczovL3N0cy53aW5kb3dzLm5ldC9mMDZkNDU5Yi1kOTM1LTRhZDctYTlkMy1hODIzNDNjNGM5ZGEvIiwib2lkIjoiNDhlYzMyMzgtYTZjZi00NTYwLWE4MjAtYzBiNjgzOGE1NGI5IiwicmgiOiIwLkFBQUFtMFZ0OERYWjEwcXAwNmdqUThUSjJ1cEN2Z2YySEtaSnV6emhYNDVzNWhkYkFBQS4iLCJzdWIiOiI0OGVjMzIzOC1hNmNmLTQ1NjAtYTgyMC1jMGI2ODM4YTU0YjkiLCJ0aWQiOiJmMDZkNDU5Yi1kOTM1LTRhZDctYTlkMy1hODIzNDNjNGM5ZGEiLCJ1dGkiOiJYMVZnWGpIZ3pVQ1dzNTQ5UURBVkFBIiwidmVyIjoiMS4wIn0.TvcQU-_V0H-r2HVhXg2ck-lZqMPA3aeN8yvgLz4DnVp4gDTrGjKNcVCz1VpMylFBlWNjVNzsm4YSbm6POOwNB9MFygODGu9blLBRPyTD_cxXjGsplKU9oFykdC4arNaUJyILQZrvIA0g8T_8S1OJ95305VR87OHuPgfcZxSW5WYBn-ep7UO8BsrlrOgsn_Due2i_U-2k60Iw1VO8mX_Zmq9UbKBDlVCPESRFV0YjRLL8ZzbTEO5f8r7A0xOGR4nqGFkKivuHDUI-AFtTrPTsH85JpYHwGe3Fw8c5LzkPBBGzsi4ibPL7l1oqE-sYnEWnvkc-x_3pkpGj2y76wRqPOw

Determine Pipeline Quality Path!

Do not forgot to URL encode the directory path!

# Path parameters
filesystem = 'datamart'

# Query Parameters
recursive = False

# Headers
headers = {
    'clientid': clientid,
    'authorization': "Bearer " + access_token
    }

# Unencoded directory
directory = "Workspace/Derived/InstancePartition=" + clientid + "/Pipeline-Quality"
encoded_directory = urllib.parse.quote_plus(directory)

conn.request("GET", "/L3/v1/workbench/" + filesystem + "?directory=" + encoded_directory + "&recursive=" + str(recursive) , headers=headers)

res = conn.getresponse()
data = res.read()
l3_paths = json.loads(data.decode("utf-8"))
l3_paths["paths"]

[{'contentLength': '14648811',
  'etag': '0x8D7BC7D2B711F9E',
  'lastModified': 'Fri, 28 Feb 2020 18:36:48 GMT',
  'name': 'Workspace/Derived/InstancePartition=a9cdadd4-1324-4a7a-842e-fe8fc9fe37f1/Pipeline-Quality/pipeline_quality.csv'}]

Read Pipeline Quality file into Pandas DataFrame

filesystem = "datamart"
path = "/Workspace/Derived/InstancePartition=" + clientid + "/Pipeline-Quality/pipeline_quality.csv"
encoded_path = urllib.parse.quote_plus(path)

conn.request("GET", "/L3/v1/workbench/" + filesystem + path, headers=headers)

res = conn.getresponse()
data = res.read()

data_string = StringIO(data.decode("utf-8"))
pipeline_quality_df = pd.read_csv(data_string)

pipeline_quality_df.head(n=5)

	ReportDate	DatePartition	InstancePartition	LastModified	RelationshipId	RelationshipIdentifierId	OpportunityId	OpportunityName	OpportunityUrl	PricingDate	...	DaysSinceCreatedBin	DaysSinceModifiedBin	DaysToCloseBin	LoanCommitment	LoanBalance	DepositAverageBalance	OtherIncome	AverageAssets	AverageEquity	NetIncome
0	20200228	20191129	99b9412b-70fd-4221-8431-d1b3934889ec	2019-11-29T02:01:04.0827583	NaN	NaN	025ea80d-42e8-4c14-b07d-caa34d352cf4	New Opportunity	NaN	2019-11-29T02:01:01.771	...	60 to 90 Days	60 to 90 Days	60 to 90 Days	1000000.0	0.0	0.0	0.0	0.0	50000.0	-2941.828333
1	20200228	20191129	99b9412b-70fd-4221-8431-d1b3934889ec	2019-11-29T02:01:04.0827583	NaN	NaN	025ea80d-42e8-4c14-b07d-caa34d352cf4	New Opportunity	NaN	2019-11-29T02:01:01.771	...	60 to 90 Days	60 to 90 Days	60 to 90 Days	1000000.0	0.0	0.0	0.0	0.0	50000.0	-2941.828333
2	20200228	20191129	99b9412b-70fd-4221-8431-d1b3934889ec	2019-11-29T02:01:04.0827583	NaN	NaN	025ea80d-42e8-4c14-b07d-caa34d352cf4	New Opportunity	NaN	2019-11-29T02:01:01.771	...	60 to 90 Days	60 to 90 Days	60 to 90 Days	1000000.0	0.0	0.0	0.0	0.0	50000.0	-2941.828333
3	20200228	20191129	99b9412b-70fd-4221-8431-d1b3934889ec	2019-11-29T02:01:04.0827583	NaN	NaN	025ea80d-42e8-4c14-b07d-caa34d352cf4	New Opportunity	NaN	2019-11-29T02:01:01.771	...	60 to 90 Days	60 to 90 Days	60 to 90 Days	1000000.0	0.0	0.0	0.0	0.0	50000.0	-2941.828333
4	20200228	20191129	99b9412b-70fd-4221-8431-d1b3934889ec	2019-11-29T02:01:04.0827583	NaN	NaN	025ea80d-42e8-4c14-b07d-caa34d352cf4	New Opportunity	NaN	2019-11-29T02:01:01.771	...	60 to 90 Days	60 to 90 Days	60 to 90 Days	1000000.0	0.0	0.0	0.0	0.0	50000.0	-2941.828333

5 rows × 42 columns

Download Pipeline Quality file to Downloads folder

Write as CSV into Downloads folder

os.chdir('C:\\Users\\THepner\\Downloads\\')

with open("pipeline-quality.csv", 'wb') as outfile:
    outfile.write(data)

data[0:100]

b'ReportDate,DatePartition,InstancePartition,LastModified,RelationshipId,RelationshipIdentifierId,Oppo'