Loan calculator - GRID API

More details on our Python examples can be found in our GitHub repository.

You will need to download the simple loan calculator and amortization table from Microsoft, upload it to your GRID account and replace REPLACE_WITH_YOUR_SPREADSHEET_ID with its workbook id. See our quick start guide for more details.

# /// script
# requires-python = ">=3.9"
# dependencies = [
#     "fastapi[standard]",
#     "grid-api",
#     "uvicorn",
# ]
# ///
import uvicorn
from fastapi import FastAPI

from grid_api import AsyncGrid, APIStatusError, RateLimitError, APIConnectionError

app = FastAPI()


@app.get("/")
async def get_loan_calculations(loan_amount: float = 100000.0, years: int = 25, interest_rate: float = 2.5):
    client = AsyncGrid()

    loan_calculator_spreadsheet_id = "REPLACE_WITH_YOUR_SPREADSHEET_ID"
    first_row_to_read = 14
    end_row_to_read = years * 12 + first_row_to_read - 1

    try:
        response = await client.workbooks.query(
            id=loan_calculator_spreadsheet_id,
            apply=[
                {"target": "'Loan calculator'!D3", "value": loan_amount},
                {"target": "'Loan calculator'!D4", "value": interest_rate / 100},
                {"target": "'Loan calculator'!D5", "value": years},
            ],
            read=[
                "'Loan calculator'!D8",
                f"'Loan calculator'!B{first_row_to_read}:'Loan calculator'!H{end_row_to_read}",
            ],
        )
    except APIConnectionError:
        return {"error": "The server could not be reached."}
    except RateLimitError:
        return {"error": "A 429 status code was received; we should back off a bit."}
    except APIStatusError as e:
        return {"error": e.message}

    monthly_payments = response.read[0].data[0][0].v
    rows = [[cell.v for cell in row] for row in response.read[1].data]

    return {"monthly_payments": monthly_payments, "payment_schedule": rows}


if __name__ == "__main__":
    uvicorn.run(app, host="0.0.0.0", port=8000)

Make sure your API key is set in the environment variable GRID_API_TOKEN, or passed as an argument to the Grid class.

client = Grid(api_key="REPLACE_WITH_YOUR_API_KEY")

To start the loan calculator server, run the following:

uv run loan_calculator.py

Open this URL in your browser:

http://localhost:8000/?loan_amount=1000&years=1&interest_rate=2

You should get a response like:

{
  "monthly_payments": 84.2388672841109,
  "payment_schedule": [
    [
      1.0,
      45758.0,
      1000.0,
      84.2388672841109,
      82.5722006174442,
      1.66666666666667,
      917.427799382554
    ],
    [
      2.0,
      45788.0,
      917.427799382554,
      84.2388672841109,
      82.7098209518066,
      1.52904633230426,
      834.717978430745
    ],
    [
      3.0,
      45819.0,
      834.717978430745,
      84.2388672841109,
      82.847670653393,
      1.39119663071791,
      751.870307777351
    ],
    ...
  ]
}

Examples