Personalized Financial advise using AzureML PromptFlow, AzureOpenAI & banking customer data on PostgreSQL/Cosmosdb
Customer data stored in operational databases will be useful to enrich context for LLM generations. In this post we will generate personalized financial advise to banking customers using their data stored in a database. We will use PromptFlow for LLM app development and postgresql / cosmosdb for storing customer data.
Promptflow, a new service within Azure ML suite of services, tries to address challanges with LLM App development. Main benefit is that PromptFlow brings together LLM’s, 3rd party API’s, OS models, tools for prompt engineering and to evaluate prompt/model variants. (Please refer to my earlier posts on PromptFlow for introduction.)
- Unveiling Azure Machine Learning PromptFlow [link]
- Create an LLM app to query your Data in Azure PromptFlow [link]
In our imaginary scenario we will integrate customer data stored in Cosmosdb / PostgreSQL with GPT models to generate financial advise for a customer intended task e.g. loan application, debt repayment.
After creating a cosmosdb instance on Azure we will create a customer banking db under postgresql / cosmosdb and upload banking data to it. GPT4 proves to be very useful in generating synthetic data…
I simply used ChatGPT to create synthetic data for a banking customer database which will include data that can be used for financial advise such as “average_monthly_deposit”, “average_monthly_withdrawal”, “risk_tolerance”, “financial_goal” etc. I then copy ChatGPT generated synthetic data to a .csv file and uploaded it to Cosmosdb/PostgreSQL database running on AzureI created earlier with a simple df.to_sql statement.
import pandas as pd
df = pd.read_csv('data.csv')
df.columns
Index(['id', 'account_number', 'account_type', 'balance',
'account_holder_name', 'date_of_birth', 'employment_status',
'creation_date', 'interest_rate', 'branch_id', 'overdraft_limit',
'currency', 'last_transaction_date', 'average_monthly_deposit',
'average_monthly_withdrawal', 'financial_goal', 'goal_amount',
'risk_tolerance'],
dtype='object')
Sample data
id,account_number,account_type,balance,account_holder_name,date_of_birth,employment_status,creation_date,interest_rate,branch_id,overdraft_limit,currency,last_transaction_date,average_monthly_deposit,average_monthly_withdrawal,financial_goal,goal_amount,risk_tolerance
1,1234567890,Checking,5000.00,John Doe,1980-06-01,Employed,2023-01-01,1.00,101,500.00,USD,2023-07-30,2500.00,2000.00,Retirement,1000000.00,Medium
2,2345678901,Savings,7000.00,Jane Doe,1985-12-12,Self-employed,2023-02-01,2.00,102,1000.00,USD,2023-07-25,3000.00,1500.00,Buy a House,300000.00,Low
...import pandas as pd
df = pd.read_csv('data.csv')
import ssl
ssl_context = ssl.create_default_context()
!pip install sqlalchemy
!pip install psycopg2-binary
from sqlalchemy import create_engine
#you can find your connection string under cosmosdb "connection strings"
#XXXX is your db password, YYYY is your db hostname
engine = create_engine(
'postgresql+pg8000://citus:XXXX@YYYY.[db-instance-name].postgres.cosmos.azure.com:5432/citus',
connect_args={'ssl_context': ssl_context}
)
df.to_sql('bank_accounts', engine, if_exists='replace', index=False)Copy the psql connection string to your terminal and connect to the db…
psql -h c-db-ozguler.XXXXX.postgres.cosmos.azure.com -d citus -U citus -p 5432
SELECT * FROM bank_accounts;
Next, create a PromptFlow custom connection to connect to cosmosdb PostgreSQL database…
Creating a custom PromptFlow runtime for connectivity to PostgreSQL / cosmosdb with pyodbc
Create a new AzureML “environment” based on your existing default promptflow runtime. For the SQL API for cosmos the environment will require azure-cosmos pip install’ed so that you can use the cosmos db custom promptflow connection within your python code.
However since we are using the postgresql citus API we will need to install the required odbc drivers to the image…Below is the Docker file to do that…
# Use the PromptFlow runtime as a parent image
FROM mcr.microsoft.com/azureml/promptflow/promptflow-runtime:20230831.v1
# Upgrade pip
RUN python -m pip install --upgrade pip
# Install Azure Cosmos and Azure Search Documents
RUN pip install \
azure-cosmos \
azure-search-documents==11.4.0b8
# Install system packages required for pyodbc
RUN apt-get update \
&& apt-get install -y --no-install-recommends \
gcc \
g++ \
unixodbc-dev \
&& apt-get clean \
&& rm -rf /var/lib/apt/lists/*
# Install PostgreSQL ODBC Driver
RUN apt-get update && apt-get install -y \
curl \
gnupg \
&& curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | apt-key add - \
&& echo "deb http://apt.postgresql.org/pub/repos/apt/ buster-pgdg main" > /etc/apt/sources.list.d/pgdg.list \
&& apt-get update \
&& apt-get install -y odbc-postgresql \
&& apt-get clean \
&& rm -rf /var/lib/apt/lists/*
# Install pyodbc Python package
RUN pip install pyodbc$schema: https://azuremlschemas.azureedge.net/latest/environment.schema.json
name: promptflow acs-cosmos
build:
path: .
dockerfile_path: dockerfile
inference_config:
liveness_route:
port: 8080
path: /health
readiness_route:
port: 8080
path: /health
scoring_route:
port: 8080
path: /score Environments / Custom Environments / Create / Upload Existing Docker Context / (Upload your DockerFile and set as a DockerFile). Your new “environment” will be built with the above dockerfile.
Check the build logs to confirm the new environment was created correctly.
In AzureML PromptFlow context, a container runtime is called an “Environment”. The compute that will run the new “Environment” is called a runtime. When your new “Environment” that includes cosmos-db is built correctly you will need to build a new “runtime” (the compute) that will run the new environment.
Create a new compute and put your environment on it. This becomes your PromptFlow runtime…
PromptFlow Flow Standard Flow
Python code to connect to cosmos-db
from promptflow import tool
from azure.cosmos import CosmosClient, PartitionKey, exceptions
from promptflow.connections import CustomConnection
# The inputs section will change based on the arguments of the tool function, after you save the code
# Adding type to arguments and return value will help the system show the types properly
# Please update the function name/signature per need
@tool
def my_python_tool(query: str, connection: CustomConnection) -> str:
#return 'hello ' + query
# Initialize the Cosmos client
endpoint = connection.endpoint
key = connection.key
client = CosmosClient(endpoint, key)
# Connect to a database
database_name = connection.database
database = client.get_database_client(database_name)
# Connect to a container
container_name = "<your_container_name>"
container = database.get_container_client(container_name)
# Issue a query
query = "SELECT * FROM c WHERE c.id='some_id'"
items = list(container.query_items(
query=query,
enable_cross_partition_query=True
))
for item in items:
print(item)
A sample generation…
"output":"Subject: Comprehensive Financial Plan for John Doe's Loan Application Dear John Doe, Thank you for reaching out for assistance with your financial goals and loan application. Based on the detailed financial profile you provided, I have prepared a comprehensive financial plan that aligns with your long-term goals,
risk tolerance, and current financial standing. Please find the plan outlined
below. 1. Personalized Advice for Different Age Groups or Life Situations:
- Considering your current goal of retirement, it is essential to start saving
and investing early to take advantage of compounding returns. However,
regardless of age, the principles of diversification and risk management remain
crucial. I will factor in your age to determine an appropriate asset allocation
strategy. 2. Account Type - Checking Account: - While a checking account
provides liquidity and flexibility for day-to-day transactions, it may not
align with your long-term financial objectives. I recommend opening additional
accounts, such as a savings account or investment account, to allocate funds
for specific purposes and potential growth. 3. Strategies for Maximizing the
Value of Current Balance: - With a current balance of $5000, it is advisable
to allocate a portion of this balance towards emergency funds as a safety net.
I recommend setting aside 3-6 months' worth of living expenses in a high-yield
savings account. This ensures you have access to funds in case of unexpected
expenses or job loss. 4. Employment Status - Employed: - Your employment status
provides a stable income source, allowing you to allocate a portion of your
monthly earnings towards savings and investments. I will incorporate your
mployment status into the financial plan to determine an appropriate savings
rate and investment strategy. 5. Allocation of Average Monthly Deposit - $2500:
- As a general guideline, I recommend allocating a certain percentage of your
monthly deposit towards investments, savings, and emergency funds. Considering your medium risk tolerance, I suggest allocating 70% ($1750) towards investments, 20% ($500) towards savings, and 10% ($250) towards emergency funds. 6. Methods to Minimize Unnecessary Withdrawals: - To minimize unnecessary withdrawals, it is crucial to create a budget and stick to it.
By tracking your expenses and prioritizing needs over wants, you can reduce the likelihood of making unnecessary withdrawals.
Additionally, establishing an emergency fund will help cover unexpected expenses without tapping into your investment portfolio.
7. Steps for Achieving Financial Goal - Retirement: - Short-term steps: -
Review your current retirement savings and assess if they are on track to meet
your desired retirement income. - Maximize contributions to tax-advantaged
retirement accounts, such as a 401(k) or IRA, to take advantage of potential
employer matches and tax benefits. - Consider working with a financial advisor
to develop a retirement savings plan tailored to your specific goals and risk
tolerance. - Long-term steps: - Regularly review and adjust your investment
portfolio to ensure it aligns with your changing financial circumstances and
risk tolerance. - Continuously increase your retirement savings contributions
as your income allows. - Explore additional retirement savings vehicles, such
as annuities or real estate investments, to diversify your retirement income sources. 8. Investment Opportunities based on Medium Risk Tolerance: - Given your medium risk tolerance, a balanced investment approach is recommended. This may include a combination of stocks, bonds, and other asset classes to achieve a diversified portfolio. - Investment options to explore include low-cost index funds, mutual funds, or exchange-traded funds (ETFs) that offer exposure to broad market indices. - Avoid high-risk investments, such as individual stocks or speculative ventures, that may not align with your risk tolerance. Please note that this plan serves as a general guideline and should be customized to your specific financial situation. It is advisable to consult with a financial advisor who can provide personalized recommendations tailored to your needs. I hope this comprehensive financial plan provides you with a solid foundation for achieving your financial goals and supports your loan application. Should you have any further questions or require additional assistance, please feel free to reach out. Best regards, [Your Name] [Your Title] [Contact Information]"
}Ozgur Guler
I am a Solutions Architect at MS where I work with Startups & Digital Natives focusing on app development with AzureOpenAI.
Subscribe to my AzureOpenAI Builders Newsletter where we cover the lates on building with #AzureOpenAI on LinkedIn here.
