precompute.py

import json
import psycopg2
import streamlit as st
import openai
from decimal import Decimal

PINECONE_API_KEY = st.secrets["PINECONE_API_KEY"]
OPENAI_API_KEY = st.secrets["OPENAI_API_KEY"]
INDEX_NAME = 'realvest-data-v2'
EMBEDDING_MODEL = "text-embedding-ada-002"  # OpenAI's best embeddings as of Apr 2023
MAX_LENGTH_DESC = 200
MATCH_SCORE_THR = 0.0
TOP_K = 20

def net_operating(rent, tax_rate, price):
    
    #Takes input as monthly mortgage amount and monthly rental amount
    #Uses managment expense, amount for repairs, vacancy ratio
    #Example input: net_operating(1000,1,400,200)
    #879.33
    #1000 - 16.67 (tax) - 100 (managment) - 4 (repairs)
    
    mortgage_amt = mortgage_monthly(price,20,3)
    prop_managment = rent * 0.10
    prop_tax = (price * (tax_rate/100)/12)
    prop_repairs = (price * 0.02)/12
    vacancy = (rent*0.02)
    #These sections are a list of all the expenses used and formulas for each
    
    net_income = rent - prop_managment - prop_tax - prop_repairs - vacancy - mortgage_amt
    #Summing up expenses
    output = [prop_managment, prop_tax, prop_repairs, vacancy, net_income]
  
    
    return output

def down_payment(price,percent):
    #This function takes the price and the downpayment rate and returns the downpayment amount 
    #Ie down_payment(100,20) returns 20
    amt_down = price*(percent/100)
    return(amt_down)

def mortgage_monthly(price,years,percent):
    
    
    #This implements an approach to finding a monthly mortgage amount from the purchase price,
    #years and percent. 
    #Sample input: (300000,20,4) = 2422
    #
    
    
    percent = percent /100
    down = down_payment(price,20)
    loan = price - down
    months = years*12
    interest_monthly = percent/12
    interest_plus = interest_monthly + 1
    exponent = (interest_plus)**(-1*months)
    subtract = 1 - exponent
    division = interest_monthly / subtract
    payment = division * loan
    
    
    return(payment)

#to do
def price_mine(pid):
    #Currently this function takes an input of a URL and returns the listing prices 
    #The site it mines is remax
    #The input must be a string input, we can reformat the input to force this to work
    #Next we use regex to remove space and commas and dollar signs
    #need to get from a product id to a price
    prices = 0
    prices = float(prices)
  
    return prices
    
    
def cap_rate(monthly_income, price):
    #This function takes net income, and price and calculates the cap rate
    #
    cap_rate = ((monthly_income*12) / price)*100
    
    return cap_rate


def cash_on_cash(monthly_income, down_payment):
    cash_return = ((monthly_income*12)/down_payment)*100
    return cash_return



def query_postgresql(
    query: str, 
    database: str, 
    user: str, 
    password: str, 
    host: str,
    port: str,
    named_columns: bool=True
    ):
    
    conn = psycopg2.connect(
            database=database,
            user=user,
            password=password,
            host=host,
            port=port
        )
    
    cur = conn.cursor()
    cur.execute(query)
    rows = cur.fetchall()

    if named_columns:
        column_names = [desc[0] for desc in cur.description]
        return [ dict(zip(column_names, r)) for r in rows ]

    return rows

def query_postgresql_realvest(query: str, named_columns: bool=True):
    import streamlit as st
    POSTGRESQL_REALVEST_USER = st.secrets["POSTGRESQL_REALVEST_USER"]
    POSTGRESQL_REALVEST_PSWD = st.secrets["POSTGRESQL_REALVEST_PSWD"]

    return query_postgresql(
        query, 
        database="realvest", 
        user=POSTGRESQL_REALVEST_USER, 
        password=POSTGRESQL_REALVEST_PSWD, 
        host="realvest.cdb5lmqrlgu5.us-east-2.rds.amazonaws.com",
        port="5432",
        named_columns=named_columns
        )

def summarize_products(products: str) -> str:
    """
    Input:
    products = [
        {text information of product#1},
        {text information of product#2},
        {text information of product#3},
    ]

    Output:
    summary = "{summary of all products}"
    """
    NEW_LINE = '\n'
    prompt = f"""
    You are a highly experienced commercial asset investor. You are writing an investement analysis report based on the data provided. You tends to be critical as your goal is to sift through and to identify promising oppurtunities.  Try to use the following instructions to write it. 
    For Executive Summary: 
    Brief overview of the property, location, asking price, and the potential investment opportunity.
    Property Description: Detailed description of the property, including number of units, age of the building, renovations, amenities, and other notable features. 
    
    for Demographics Analysis: based on the location, come up with basic population, income, age, crime, tax, traffic information. 
    
    for Market Analysis: Detailed analysis of the local market, including demographics, competition, growth projections, and other relevant factors. Make sure to call out the specific category of the business and its typical range of multipliers. 
    
    for Financial Analysis: Detailed review of the property's financials, look into the finance_info and description, including the key metrics like CAP, COC, GRM, NIM, DCR, ER per Unit, Price per Unit, GOI, NOI, Total Debt Service, Cash Flow, ROI, IRR, Equity Buildup Rate, BER, and LTV. Each metric should be calculated and explained. Given the specific business category, provide the key metrics specific to this category.  Export in a format that is in a table. Most important information: cash flow, cap rate and EBITDA

    for Risk Assessment: Detailed analysis of potential risks, including economic, industry, and location-specific risks. Call out specific risks that are relevant to the property.

    for Follow up questions: come up with questions and to do list with contact info. 
    
    for Conclusion and Recommendation:  Final thoughts on the investment opportunity, including whether it meets your investment criteria and objectives.
    
    Here is the input data in JSON format, try to parse out the metrics and location information, if the data is not include, try to use your own knoweldge and expertise to fill in.
    
    
    {products}
    
    
    Please write a insightful summary (display as Markup) and try your best to fill in data and what you know with the following format:
    
    #Investment Sample Report: 

    ##Executive Summary: 
      
    ##Demographics Analysis: 
    
    ##Market Analysis: 
    
    ##Financial Analysis: 
    
    ##Risk Assessment: 

    ##Follow up questions: 
      
    ##Conclusion and Recommendation: 
    
    """
    #print(f"prompt: {prompt}")
    
    openai.api_key = OPENAI_API_KEY
    completion = openai.ChatCompletion.create(
        model="gpt-4",
        messages=[
            {"role": "system", "content": "You are a helpful assistant."},
            {"role": "user", "content": prompt}
        ]
    )

    summary = completion.choices[0].message
    return summary




if __name__ == "__main__":
    query = """
    select  productid, category, established_year name, alternatename, 
    offers,asking_price, cash_flow, finance_info, rent, listing_profile, description 
    from main_products 
    limit 1
    """
  
    results = query_postgresql_realvest(query)
    #loop through results and cancatenate each row into a json string
    
    
    # Assuming results are a list of tuples

    for row in results:
        print(row['productid'])
        str_data = ""
        str_data += json.dumps({k: str(v) if isinstance(v, Decimal) else v for k, v in row.items()})

        summary = ""
        summary = summarize_products(str_data)
        print(summary['content'])