Video Demo: <URL Budgetary>
Github: <URL Project Code>
Budgetary is a web application for managing personal finances across multiple currencies. Users can track their daily budgets in categories like incomes, expenses, investments, and debts, with real-time insights on net worth, financial trends, and detailed logs.
-
HTML, CSS, Bootstrap, JavaScript
-
Chart.js
-
Mark.js
-
AJAX
-
Flask framework
-
SQLite
-
Jinja
-
AJAX
-
Frankfurter API to get the foreign currency exchange rate in real time.
-
Binance API to get the cryptocurrency quotes in real time.
-
Yahoo Finance API to get the stock quotes in real time.
-
User Registration
-
Login
-
Customized Currency Selection
-
Daily Budget Entry
-
Multi-currency Budget Monitoring
-
Budget Analysis and Visualization
-
Budget History Review
-
Changing Password
-
Logout
-
Route:
@app.route("/register", methods=["GET", "POST"])
inapp.py
-
Templates:
register.html
andlayout.html
intemplates
-
Database:
users
table inbudgetary.db
-
GET Request:
-
Renders the
register.html
template. When rendering, any flash message from the previous session is stored in a variable before clearing session data to ensure the flash message is displayed. -
As the
layout.html
uses the Jinja condition{% if session["user_id"] %}
, only thelogin
andregister
tabs are displayed in thenav bar
since theuser_id
has not been established yet in the logged-out state.
-
-
POST Request:
-
Server-Side Validation
-
Checks if the
username
,password
, andpassword confirmation
fields are submitted. -
Checks if the
password
andpassword confirmation
matches.
-
-
If Validation Passes
-
The
username
andhash value of password
(obtained usinggenerate_password_hash
from thewerkzeug.security
module) are inserted into theusers
table, completing the registration process. -
Then, the user is redirected to the
/login
route (login.html
).
-
-
If Validation Fails
- The user is redirected to the
/register
route (register.html
) and a flash message is displayed.
- The user is redirected to the
-
-
Route:
@app.route("/login", methods=["GET", "POST"])
inapp.py
-
Templates:
login.html
andlayout.html
intemplates
-
Database:
users
anduser_currencies
tables inbudgetary.db
-
GET Request:
-
Renders the
login.html
template. When rendering, any flash message from the previous session is stored in a variable before clearing session data to ensure the flash message is displayed. -
As the
layout.html
uses the Jinja condition{% if session["user_id"] %}
, only thelogin
andregister
tabs are displayed in thenav bar
since theuser_id
has not been established yet in the logged-out state.
-
-
POST Request:
-
Server-side Validation
-
Checks if the
username
andpassword
fields are submitted. -
Checks if the
username
andpassword
matches with the values stored in theusers
table using thecheck_password_hash
function from thewerkzeus.security
module.
-
-
If Validation Passes
-
user_id
is stored insession
. Then,user_currencies
table is checked to know if the user has selected anypreferred currencies
previously. -
If the user has not selected any
preferred currencies
, the user is redirected to/currency
route (currency.html
). -
If the user has already selected
preferred currencies
, then the user will be redirected to/
route (index.html
). -
Since
session["user_id"]
is established in the logged-in state, all the nav bar items except thelogin
andregister
tabs are displayed by the Jinja condition{% if session["user_id"] %}
inlayout.html
.
-
-
If Validation Fails
- The user is redirected to
/login
route (login.html
) and a flash message is displayed.
- The user is redirected to
-
-
Route:
@app.route("/currency", methods=["GET", "POST"])
inapp.py
-
Templates:
currency.html
andlayout.html
intemplates
-
Server-Side Login Validation:
login_required
decorator fromhelpers.py
-
Database:
currencies
anduser_currencies
tables inbudgetary.db
-
Author's Note: The
available currencies
from thecurrencies
table are obtained from theFrankfurter API
. Additionally,MMK (Myanmar Kyat)
was manually added to the list to include my home currency. -
GET Request: Gets the
available currencies
from thecurrencies
table and the previously-selectedpreferred currencies
from theuser_currencies
table. Renders thecurrency.html
template, showingavailable currencies
in aoption select
format, with the previously selectedpreferred currencies
in the selected state. -
POST Request: Gets the newly-selected currencies via
request.form.getlist('currency')
. Delete the previously-selected currencies from theuser_currencies
table and insert the newly-selected currencies to theuser_currencies
table for thatuser_id
.
-
Route:
@app.route("/budgetary", methods=["GET", "POST"])
-
Templates:
budgetary.html
andlayout.html
intemplates
-
Server-Side Login Validation:
login_required
decorator fromhelpers.py
-
Database:
user_currencies
,transactions
,income
,spending
,investment
, anddebt
tables -
budgetary.html
Template:-
Form Fields Visibility
Initially, form fields related to the budget categories
income
,expense
,investment
, anddebt & receivable
are hidden using the CSS propertystyle="display: none;"
. When the user selects a budget category via the selection-menu id#budgetary-type
, the JavaScript functiononchange="displayForm(this.value)"
is triggered, revealing the hidden blocks of the selected category in the process using thedocument.getElementById(type).style.display = "block";
property. -
Date Input Field
The date of the computer's operating system
is obtained using the JavaScript functionsgetDate()
,getMonth()
, andgetFullYear()
to automatically fill thedate
input field. -
Client-Side Validations
-
Debt & Receivable Category
The JavaScript function
enableInterestRate()
disables theinterest rate
field when the user selectsrepayment
indebt & receivable
category to prevent conflict in the database which could occur when theborrow / lend interest rate
and therepayment interest rate
are not equal. -
Investment Category
The JavaScript function
setInvestmentQuantity()
automatically sets thequantity
field to1
and disables user input if the investment typesreal-estate
orother-investment
are chosen. This is necessary to keep the investment data monitoring correct in the homepage.
-
-
-
GET Request: Renders the
budgetary.html
template. During rendering, theuser-selected currencies
are extracted from theuser_currencies
table and they are rendered in thecurrency selection menu
on this page. -
POST Request:
-
Server-Side Validations
-
General Validations
Check if the
date
field is submitted. Check if the submitted values of the dropdown lists are among the valid options.If the
other-income
,other-spending
, andother-investment
values are selected,comment
field must be submitted. Otherwise, all relevant input fields for the chosen budget category except thecomment
field must be submitted.For validations which expect numerical values such as
quantity
,interest rate
, andamount
fields, theisinstance()
function is used to ensure if the user actually inputs a numerical value. Moreover, they must also benon-zero positive values
. -
Investment Category
If the user inputs the
stock symbol
orcryptocurrency symbol
, the server checks thestock symbol
with theYahoo Finance API
and thecryptocurrency symbol
with theBinance API
to ensure the symbol actually exists using thestock_lookup(symbol)
andcrypto_lookup(symbol)
functions fromhelpers.py
. -
Debt & Receivable Category
Only one unique string is accepted as
debtor_or_creditor
for each borrow or lend process. This is necessary to keep track of eachdebt
orreceivable
without any further complication. Ifrepayment
is chosen, thedebtor_or_creditor
must already exists in the database.For
repayment
, thepayment amount
must be equal to or lower than thetotal debt amount
, which is equal tooriginal amount + interest rate
.
-
-
Updating Database
If the validation passes, the submitted data in each
budget category
is inserted into its respective tables in thebudgetary.db
database. Generally, acategory
table (income, spending, investment, or debt) and thetransactions
table are updated one time each for one POST request.However, for
repayment
indebt & receivable
category, there are two times thedebt
andtransactions
tables are required to be updated each for one POST request. Thefirst time
is for inserting therepaid amount
into the database and thesecond time
is for inserting theremaining amount
into the database. This step is necessary to ensure therepayment
process works forless than 100% repayment amounts
.
-
-
Routes in
app.py
:-
@app.route("/", methods=["GET", "POST"])
-
@app.route("/convert_currency")
-
@app.route("/convert_profit_currency")
-
@app.route("/delete_investment", methods=["POST"])
-
@app.route("/delete_debt", methods=["POST"])
-
-
Templates:
index.html
andlayout.html
intemplates
-
Server-Side Login Validation:
login_required
decorator fromhelpers.py
-
Database:
user_currencies
,transactions
,income
,spending
,investment
, anddebt
tables -
index.html
Template:-
Layout
-
There are
4 information cards
inhomepage
, which showstotal net worth
,cash & bank deposits
,investments
, anddebts & receivables
. -
The
investments
anddebts & receivables
cards are only shown if there is any relevant information for these sessions using the Jinja code{% if investment_rows %}
and{% if debt_rows %}
.
-
-
Convert Currency Functions (AJAX request using GET)
-
There are
4 Asynchronous JavaScript (AJAX) functions
for converting currency, which areconvertCashCurrency()
,convertInvestmentCurrency()
,convertDebtCurrency()
, andconvertAssetCurrency()
. -
Each function sends the
currency
andamount
to server side usingAJAX fetch request using GET
through the routes@app.route("/convert_currency")
and@app.route("/convert_profit_currency")
. -
Both routes convert the currencies using the
forex_rate()
function from helpers.py and then returns the formatted string values inJSON
format back to the client side, which then successfully converts the currency without having to refresh the page.
-
-
Deleting Investment Rows (AJAX request using POST)
-
It is beneficial in having a delete button for investment records with
0
quantity, which have less value in existing than to be deleted. -
For investments, if the
quantity
of an investment is already0
, adelete button
appears on the specific row of theinvestments card
using Jinja condition{% if row['quantity'] == 0 %}
. If thedelete button
is clicked, the JavaScript function is triggered usingonclick=deleteInvestment(event)
property. -
Then, the
deleteInvestment()
function sends thesymbol
(ifstock
andcryptocurrency
types) andcomment
(ifreal-estate
andother-investment
types) to the server side usingAJAX fetch request using POST
throught the route@app.route("/delete_investment", methods=["POST"])
. -
Note: POST method is used here because the request includes data deletion from the server, which is not displayed in the front end of the web app.
-
At the server side, the
delete_investment()
function then stores the relevant rows from theinvestment
table in a variable. -
The relevant rows in the
investment
table are deleted. If the deleted row is aboutselling investment
, then the replacement row is inserted to theincome
table, and if it is aboutbuying investment
, then replacement row is inserted to thespending
table, re-assigning thetransaction_ids
of the deleted rows. -
Upon successful deletion and re-assignment, a
JSON
file with thestatus: success
is returned to the client side. Upon receiving thestatus: success
, the JavaScript function reloads the page using the codewindow.location.reload();
.
-
-
Deleting Debt Rows (AJAX request using POST)
-
It is beneficial in having a delete button for fully-repaid debt records, which have less value in existing than to be deleted.
-
For debts & receivables, if the specific debt is
fully repaid
, adelete button
appears on the specific row of thedebts & receivables card
using Jinja condition{% if row['amount'] == 0 %}
. If thedelete button
is clicked, the JavaScript function is triggered usingonclick=deleteDebt(event)
property. -
Then, the
deleteDebt()
function sends the string information of thedebtor_or_creditor
to the server side usingAJAX fetch request using POST
through the route@app.route("/delete_debt", methods=["POST"])
. -
Note 1: POST method is used here because the request includes data deletion from the server, which is not displayed in the front end of the web app.
-
At the server side, the
delete_debt()
function then stores the relevant rows from thedebt
table in a variable. Also, using therepay_check(debtor_or_creditor)
function ofhelpers.py
, thevery first debt_category
and thevery first transaction_id
are obtained and stored inside a variable each. -
Note 2: Remember that the database was updated two times for
repayment
in/budgetary
route, one time for thepaid amount
and one time for theremaining amount
. If thevery first debt_category
isborrow
and there is apartial repayment
, theremaining amount
will be inserted to the table with the samedebt_category: borrow
. Therefore, if we were to re-assign alltransaction_ids
from thedeleted rows
into theincome
table, the data will be inflated because of thisrepaid amount
. Therefore, for alldebt_categories
other thanrepayment
, only thevery first transaction_id
will be re-assigned to theincome
orspending
table. -
The relevant rows in the
debt
table are deleted. If the deleted row'sdebt_category
isborrow
orlend
and thetransaction_id
is not equal to thevery first transaction_id
, the row fromtransactions
table with the sametransactions.id
is deleted. If the deleted row'sdebt_category
isborrow
and thetransaction_id
is equal to thevery first transaction_id
, the replacement row is inserted to theincome
table, and else if is the case forlend
, the replacement row is inserted to thespending
table. If the deleted row'sdebt_category
isrepayment
and thevery first debt_category
islend
, the replacement row is inserted to theincome
table, and else if thevery first debt_category
isborrow
, then the replacement row is inserted to thespending
table. -
Upon successful deletion and re-assignment, a
JSON
file with thestatus: success
is returned to the client side. Upon receiving thestatus: success
, the JavaScript function reloads the page using the codewindow.location.reload();
.
-
-
-
GET Request:
-
Getting User Currencies
-
Note: When calculating the budgets, we cannot just use the
user-selected currencies
in theuser_currencies
table. If the user has used another currency before and then they change theiruser_currencies
, the data for that another currency will be sitting around with the sameuser_id
, waiting for a potential error to emerge when calculating a large sum of data. Therefore, we cannot ignore thepreviously-used currencies
for thatuser_id
. -
Get the
user-selected currencies
from theuser_currencies
table in a variable, and also get thepreviously-used currencies
from thetransactions
table for the sameuser_id
in another variable. Then, add thepreviously-used currencies
to theuser-selected currencies
so that we can start the calculation with no accuracy error. -
Later in the
/
route, if eachpreviously-used currency
has an existing amount ofcash
orbank deposit
, then this currency is added to theuser_currencies
table.
-
-
Cash & Bank Deposits Calculation
-
Loop through each currency in
user-selected currencies
. -
For each currency, get the total amount for
cash
andbank transfer
categories separately fromincome
,spending
, andtransactions
tables. -
Also get the total amount for
cash
andbank transfer
categories separately forinvestment: buy
andinvestment: sell
types frominvestment
andtransactions
tables. -
Finally, the
debt_rows
database query extracts rows from thedebt
table with thefirst row
and allrepayment rows
ofeach debtor_or_creditor
for accurate data calculation as explained in theNote 2
of the sub-title4. Deleting Debt Rows
. Then the total amount forcash
andbank transfer
categories are obtained separately forborrow
,lend
,borrow repay
, andlend repay
types fromdebt
andtransactions
tables. -
Then the existing
cash
andbank deposits
for thelooping currency
are obtained by addingincome
,investment: sell
,borrow
, andlend repay
categories, and subtractingspending
,investment: buy
,lend
, andborrow repay
categories. -
After that, the
cash
andbank deposits
values for thelooping currency
is converted toUSD
for total amount calculation. -
When all
user-selected currencies
are looped through, the totalcash
andbank deposits
of the user is stored accurately in theUSD
format. -
The
cash
andbank deposits
information ofeach currency
and thetotal amount in USD
are rendered in thecash & bank deposits
information card of the homepage.
-
-
Investments Calculation
-
Note: We calculated
cash & bank deposits
previously. If we want to calculate thetotal net worth
, however, the existing amount ofcash & bank deposits
is not the correct answer because theinvestments
that you possess also worth a value and it should be added in order to get thetotal net worth
. -
Firstly, we get the
investment_rows
of theuser_id
with their quantities and amounts already summed up for each investment item. Then we loop through each row (each investment item). -
If the
investment_type
isstock
, theoriginal value
of the stock that the user bought is obtained from theamount_in_usd
column of thelooping row
. Then, we getcurrent stock price
of that specific stock symbol from theYahoo API
using the functionstock_lookup(symbol)
. Subsequently, we can get themarket value
of the user's stock by multiplyingcurrent stock price
with thequantity
. For profit / loss, the value is calculated by subtractingamount_in_usd
from themarket value
. -
If the
investment_type
iscryptocurrency
, theoriginal value
of the cryptocurrency that the user bought is obtained from theamount_in_usd
column of thelooping row
. Then, we getcurrent cryptocurrency price
of that specific crypto symbol from theBinance API
using the functioncrypto_lookup(symbol)
. Subsequently, we can get themarket value
of the user's cryptocurrency by multiplyingcurrent cryptocurrency price
with thequantity
. Forprofit / loss
, the value is calculated by subtractingamount_in_usd
from themarket value
. -
If the
investment_type
isreal-estate
orother-investment
, we cannot know themarket value
of each investment item. Therefore, if the investment item is not sold yet (quantity: 1
), then themarket value
is set as theoriginal value
and theprofit / loss
is0
. If the investment item is sold, (quantity: 0
), then themarket value
is set as0
and theprofit / loss
issold value - buy value
, which is already calculated in the database query (investment_rows
). -
Then, each
investment_row
withmarket value
andprofit / loss
values are rendered in theinvestments
information card of the homepage. Note that the investment values that we work with in this session are all inUSD
so we can just add them directly fortotal_investment
calculation.
-
-
Debts & Receivables Calculation
-
Note 1: So far, we have already calculated
cash & bank deposits
andinvestments
. However, in order to get the user'stotal net worth
accurately, we need to addreceivables
and subtractdebts
into our equations. -
Firstly,
debt_rows
are extracted from thedebt
table. Thesedebt_rows
only contain thelast-inserted rows
for eachdebtor_or_creditor
. -
Note 2: If there are no
repayment rows
for a uniquedebtor_or_creditor
, thenthe very first row
is used for calculatinginterest
so the result is correct. Even if there arerepayment rows
, since we only extract thelast-inserted row
, theinterest
calculation would still be correct because thelast-inserted row
is the row with the information ofremaining debt amount
(Remember we have to insert twice forrepayment
category - seeNote 2
of the sub-title4. Deleting Debt Rows
). -
To work with the extracted data, we start by looping each of the
debt_rows
. For each row, we can get theoriginal debt amount
and themonthly interest rate
from the relevant columns. Then, we calculate how many days have passed betweentoday
and thetransaction date
using the functiondays_difference(past_date_str)
fromhelper.py
. Then, theoriginal debt amount
,monthly interest rate
and the number ofmonths: days_diff / 30
is used to calculate theinterest
in itsoriginal currency
. Fortotal_interest
calculation, theinterest
is also converted toUSD
. Finally, thefinal debt amount
is calculated by the equationfinal debt = original debt amount + interest
. Thefinal_debt
is also converted toUSD
fortotal_debt
calculation. -
Then, each of the
debt_rows
with theinterest
andfinal debt
are rendered in thedebts & receivables
information card along with the values oftotal_interest
andtotal_debt
.
-
-
-
Routes in
app.py
:-
@app.route("/analysis", methods=["GET", "POST"])
-
@app.route("/analysis_filter")
-
-
Templates:
analysis.html
andlayout.html
intemplates
-
Server-Side Login Validation:
login_required
decorator fromhelpers.py
-
Database:
user_currencies
,transactions
,income
,spending
,investment
, anddebt
tables -
analysis.html
Template:-
Layout
- There are
4
cards, namely,total net worth breakdown
,inflows vs outflows analysis
,inflows breakdown
, andoutflows breakdown
, each containing a canvas to dynamically draw a chart upon.
- There are
-
Creating Charts Using Chart.js Library
-
The source file link for the Chart.js library
<script src="https://cdn.jsdelivr.net/npm/chart.js"></script>
is included at the start of the JavaScript. -
The required
datasets
for the chart are acquired from theHTML
page and are changed fromstring
tonumeric
values using the scriptNumber(document.getElementById('#id-name').innerText)
. Thelabels
and thebackgroundColor
are then hard-coded for each chart. -
To specify the type of chart, we can use the syntax
new Chart(#canvas-id, { type: 'doughnut', ...
fordoughnut chart
, andtype: 'bar'
forbar chart
. In order to create astacked bar chart
, we can use the syntaxx: { stacked: true, ... }, y: { stacked: true, ... }
. For ahorizontal bar chart
, the syntax isoptions: { indexAxis: 'y', ...
.
-
-
Hiding Data Labels with
0
Value-
There are numerous
data labels
that can be0
value in the chartsinflows vs outflows analysis
,inflows breakdown
, andoutflows breakdown
. Therefore, we need to hide thedata labels
with the0
value so that the user can view the chart with minimal nuances. -
In order to do that, the JavaScript function
hideElementByClass('checkZero')
is used. In each of the<div>
tag oflabel
items, there is a class ofparentElement
, and in eachdata value
, there is a class ofcheckZero
. -
The
hideElementByClass('checkZero')
function effectively loops through thecheckZero
classes if each have any0
value by using the script functionparseFloat(element.textContent.trim());
. If a0
value is found, then the closestparentElement
is set to be hidden by the script functionparentElement.style.display = 'none';
.
-
-
Updating Charts by Filtering with Date and Currency Values (AJAX request using GET)
-
In the
inflows vs outflows analysis
card, there is a filter input, that accepts the values ofstart date
,end date
, andcurrency
. When thefilter
button is clicked, the AJAX functionanalysisFilter()
is called. -
Then, the function sends the filter inputs (
start date
,end date
,currency
), and thedata
from thetotal net worth breakdown
table to the server via the route/analysis_filter
using GET request. -
The server then responds with a
JSON
file containing thedata
for all charts in the page. TheanalysisFilter()
function then parse theJSON
file and replace thelabel data values
in theHTML
page with the updated values. -
After updating the
label data values
, all the charts are updated by replacing theexisting chart datasets
with the updated ones and calling the functionchartName.update();
. -
After updating the charts, the
labels
with0
values are set to be hidden by calling the functionhideElementByClass('checkZero');
. -
Then the
CSS
class of the id#formatted-net-balance
is updated based on its value to show agreenish
background if positive, areddish
background if negative, and agreyish
background if equals to0
by call the script functionupdateNetBalanceDisplay(data.net_balance, selectedCurrency);
. -
Finally, all
data
values are formatted to show2
decimal places in a comma-separated string with thecurrency
the user selected using the script functionupdateCurrencyDisplay(selectedCurrency);
.
-
-
-
GET Request:
-
/analysis
Route-
Note: This route is used to calculate necessary values to be used in the charts of the analysis page.
-
Investments Calculation
Investment values are calculated using the same codes as explained at
5. Multi-Currency Budget Monitoring > GET Request > 3. Investments Calculation
in this document. -
Debts & Receivables Calculation
Debts & Receivables are calculated using the same codes as explained at
5. Multi-Currency Budget Monitoring > GET Request > 4. Debts & Receivables Calculation
in this document. -
Inflows & Outflows Calculation
The calculation procedure used here is a bit different from the one used in the
/
route (index.html
). The reason is that in the/
route, we want to focus oncash
andbank deposits
information of eachcurrency
separately for accurate budget monitoring.However, for the
/analysis
route, we focus on thetype
of eachbudget category
in ourSQLite
queries since thetype - amount
relations are crucial for budget analysis.First, we loop through each currency using the method explained in the
5. Multi-Currency Budget Monitoring > GET Request > 1. Getting User Currencies
in this document.Then, we query the
amount
in thelooping currency
associated with eachtype
of theincome
,spending
,investment
, anddebt
tables. For difficulty understanding thedatabase queries
, it is advised to read through the session5. Multi-Currency Budget Monitoring > GET Request > ..
.After that, all the calculated values are assigned inside a newly-created
dictionary
variable calledcurrency_var
. This variable is specially created with the sole purpose ofreducing the number of API calls
in the/analysis
route. In the next line of code, we can see the new variablecurrency_var_sum = sum(currency_var.values())
that calculates the sum of all calculated values for thelooping currency
. If thatcurrency_var_sum
is equal to0
, we will not call API to know the foreign exchange rate of thislooping currency
, effectively reducing the loading time if the user selected so many currencies with0
used values.Next, all the calculated values are converted into
USD
since we require the same unit of currency to be displayed together in one chart. If thelooping currency
isMMK
, then the manual foreign exchange rate ofMMK_EXCHANGE_RATE
is used. If it is a currency other thanUSD
orMMK
, then the functionforex_rate(currency_v)["rate"]
that usesYahoo API
is used to know the exchange rate.Then, all the necessary components of financial
inflows
andoutflows
are calculated, thereby, allowing to calculate thenet_balance = inflows - outflows
and thetotal_assets
akatotal net worth
as well.Finally, the page is rendered by providing all necessary data of components to be used in creating the
charts
andlabel data
.
-
-
/analysis_filter
route-
The route is used to process the fetch request from the
AJAX
functionanalysisFilter()
when the user enters thestart date
,end date
, andcurrency
information and then click thefilter
button. We can get the information sent from the client side using the functionrequest.args.get("key")
. -
Calculations
All the calculation steps are almost the same as the ones used in the
/analysis
route. However, we will not re-calculate thetotal net worth
as it has already been calculated with theGET request
. For updating thetotal net worth breakdown
chart, we will reuse the data for this chart sent from the client side, skipping the calculations steps of6. Budget Analysis and Visualization > GET Request > 1. Analysis Route > Investments Calculations
and... > 1. Analysis Route > Debts & Receivables Calculations
.The other difference is that since we are filtering data by
date
, an additionalWHERE clause
is added to each database query.If there is user input for
start date
andend date
, theWHERE clause
is updated totransactions.transaction_date BETWEEN ? AND ?
, effectively filtering data in the requested period. Else, theWHERE clause
is just set as anempty string
so all the existing data for thatuser_id
will be extracted.Since the
filter
also includescurrency
as an input, at the end of the calculations, all calculated values inUSD
will required to be converted to theuser-selected currency
using the exchange rateMMK_EXCHANGE_RATE
forMMK
, and theforex_rate(selected_currency)["rate"]
function that usesYahoo API
for currencies other thanUSD
.Finally, all the filtered and currency-converted values are returned as a
JSON
file to the client side, where the browser effectively renders the updatedcharts
andlabel data
for the user's specific request.
-
-
-
Route:
@app.route("/history", methods=["GET", "POST"])
inapp.py
-
Templates:
history.html
andlayout.html
intemplates
-
Server-Side Login Validation:
login_required
decorator fromhelpers.py
-
Database:
transactions
,income
,spending
,investment
, anddebt
tables -
history.html
Template:-
Layout
-
There are
3 information cards
inhistory.html
, which shows all the historical logs ofincomes & expenses
,investments
, anddebts & receivables
. -
The
incomes & expenses
,investments
anddebts & receivables
cards are only shown if there is any relevant information for these sessions using the Jinja code{% if income_spending_rows %}
,{% if investment_rows %}
and{% if debt_rows %}
.
-
-
Searching and Highlighting Keywords Using Mark.js Library
-
The source file link for the Mark.js library
<script src="https://cdn.jsdelivr.net/npm/mark.js/dist/mark.min.js"></script>
is included at the head session oflayout.html
. -
Each individual input to the id
searchKeyword
is listened using the script syntaxdocument.getElementById('searchKeyword').addEventListener('input', function() { ...
. Then, the input letters are converted to lowercase in order to search without case sensitivity. -
Each table row is looped through and the text content of each row is also converted to lowercase using the script function
row.textContent.toLowerCase();
. Then, amark instance
is added to thelooping row
to get ready for highlighting using theMark.js
syntaxnew Mark(row);
. -
Note: It is important to
unmark
any previous highlight beforehighlighting
new letters. -
If the row contains the
Keyword
, the row is displayed using the syntaxrow.style.display = '';
. The letters in the table row that matches with theKeyword
are also highlighted using the syntaxinstance.mark(filter, ...
. -
If the row does not contain the
Keyword
, then the row is hidden using the syntaxrow.style.display = 'none';
.
-
-
-
GET Request:
-
The data from the
income
andspending
tables are both extracted using the sameSQLite
query namedincome_spending_query
. Then, the data from theinvestment
anddebt
tables are extracted separately using theinvestment_query
anddebt_query
. All information of the user is extracted from the database except for theinvestment_query
, which left out thecomment
category forstock
andcryptocurrency
types, for which thesymbol
column is extracted instead ofcomment
column. -
In the
debt_query
rows, if the debt category isrepay
, then the category is processed to explicitly mention if it isborrow repay
orlend repay
using the functionrepay_check(debtor_or_creditor)
fromhelpers.py
. The idea behind this clarification is change the color of the text content togreen
if it islend repay
and tored
if it isborrow repay
when being displayed inHTML
page. -
All the extracted rows are fed to the
history.html
, where all the rows are rendered in3
separate information cards.
-
-
POST Request:
-
This request is processed when the user wants to filter
history
data by date by inputtingstart date
andend date
information and submitting to server. -
The data query process is the same as that of
GET Request
with the exception that theWHERE clause
of the queries contains the date filtering partWHERE transactions.transaction_date BETWEEN ? AND ?
. However, if the user does not fully providestart date
andend date
, theWHERE clause
will not contain date filtering part, causing the query toreturn unfiltered information
of that user. -
Then the debt category of
repay
is further processed as mentioned in theGET Request
. -
All the extracted rows are fed to the
history.html
, rendering all information in3
separate information cards.
-
-
Route:
@app.route("/change-password", methods=["GET", "POST"])
inapp.py
-
Templates:
change-password.html
andlayout.html
intemplates
-
Server-Side Login Validation:
login_required
decorator fromhelpers.py
-
Database:
users
table inbudgetary.db
-
GET Request:
- Renders the
change-password.html
template.
- Renders the
-
POST Request:
-
Server-side Validation
-
Checks if the
old password
,new password
, andconfirmation
fields are submitted. -
Checks if the
new password
andconfirmation
inputs are the same. -
Checks if the hash value of the
old password
using thecheck_password_hash
function from thewerkzeus.security
module matches with the hash stored in theusers
table.
-
-
If Validation Passes
-
The
hash
value inusers
table for thatuser_id
is changed to thehash
value of thenew password
using thegenerate_password_hash
function from thewerkzeus.security
module. -
Then, the user is redirected to the
/change-password
route and a flash message is displayed to notify the user of the successful password change.
-
-
If Validation Fails
- The user is redirected to the
/change-password
route and a flash message mentioning the error message is displayed.
- The user is redirected to the
-
-
Route:
@app.route("/logout")
inapp.py
-
Templates:
layout.html
intemplates
-
GET Request:
- Logs the user out by clearing the session and redirecting the user to the
/login
page.
- Logs the user out by clearing the session and redirecting the user to the
-
Web Application Name: BUDGETARY suggested by ChatGPT.
-
Background Image created by
588ku - pngtree
. -
Budget Icon created by
Freepik - Flaticon
.