-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathstreamlit.py
193 lines (126 loc) · 6.96 KB
/
streamlit.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
import streamlit as st
import plotly.express as px
import pandas as pd
import mysql.connector
class Banks:
def __init__(self):
# For transactions and cash rate
self.conn = mysql.connector.connect(
host = st.secrets['Host'],
user=st.secrets['Database_user'],
password= st.secrets['Database_password'],
database=st.secrets['Database_name'],
port=st.secrets['Port_number']
)
self.cur = self.conn.cursor()
# The two functions below retrieve the name of the banks
def banks_name(self):
self.cur.execute('select distinct bank from rates')
banks = self.cur.fetchall()
banks = [bank[0] for bank in banks]
return banks
def banks_name_full(self):
self.cur.execute('select distinct bank from full_rates')
banks = self.cur.fetchall()
banks = [bank[0] for bank in banks]
return banks
# This function return the date, CurrencyCode, Buying and selling rates from database for respective bank
def rate(self, bank: str) -> str:
self.cur.execute('SELECT * FROM rates WHERE bank = %s ORDER BY date DESC', (bank,))
result = self.cur.fetchall()
most_recent_date = result[0][1]
recent_rates = [rate for rate in result if rate[1] == most_recent_date]
return recent_rates
# This function return the date, CurrencyCode, Cash Buying and cash selling, Transactional buying, Transactional selling rates from database for respective bank
def full_rate(self, bank: str) -> str:
self.cur.execute('SELECT * FROM full_rates WHERE bank = %s ORDER BY date DESC', (bank,))
result = self.cur.fetchall()
most_recent_date = result[0][1]
recent_rates = [rate for rate in result if rate[1] == most_recent_date]
return recent_rates
# The two functions below return the dataframe and date that is retieved from the database using the functions: full_rate() and rate()
def bank_rate_table(self, bank: str):
rates = self.rate(bank)
data = [[rate[2], rate[3], rate[4]] for rate in rates if rate[3] is not None]
return rates[0][1].strftime('%d-%m-%Y'), pd.DataFrame(data= data, columns = ['Currency code', 'Buying', 'Selling']).set_index('Currency code')
def bank_full_rate_table(self, bank: str):
rates = self.full_rate(bank)
data = [[rate[2], rate[3], rate[4], rate[5], rate[6]] for rate in rates if rate[3] is not None]
return rates[0][1].strftime('%d-%m-%Y'), pd.DataFrame(data= data, columns = ['Currency code', 'Cash Buying', 'Cash Selling', 'Transaction Buying', 'Transaction Selling']).set_index('Currency code')
def best_rates(self):
# Only shows the recent date
self.cur.execute('select Date from full_rates order by Date Desc limit 1;')
date = self.cur.fetchone()[0]
self.cur.execute('select * from rates where CurrencyCode = %s and Date = %s order by Buying DESC limit 1;', ('USD', date))
result = self.cur.fetchall()[0]
# Best USD rates
self.cur.execute('select * from full_rates where CurrencyCode = %s and Date = %s order by TransactionalBuying DESC limit 1;', ('USD',date))
result_full = self.cur.fetchall()[0]
self.cur.execute('select * from rates where CurrencyCode = %s and Date = %s order by Buying DESC limit 1;', ('USD', date))
result = self.cur.fetchall()[0]
best_rate = result[3]
best_full_rate = result_full[5]
best_USD = result if best_rate > best_full_rate else result_full
# Best EUR rates
self.cur.execute('select * from full_rates where CurrencyCode = %s and Date = %s order by TransactionalBuying DESC limit 1;', ('EUR',date))
result_full = self.cur.fetchall()[0]
self.cur.execute('select * from rates where CurrencyCode = %s and Date = %s order by Buying DESC limit 1;', ('EUR', date))
result = self.cur.fetchall()[0]
best_rate = result[3]
best_full_rate = result_full[5]
best_EUR = result if best_rate > best_full_rate else result_full
# Best GBP rates
self.cur.execute('select * from full_rates where CurrencyCode = %s and Date = %s order by TransactionalBuying DESC limit 1;', ('GBP',date))
result_full = self.cur.fetchall()[0]
self.cur.execute('select * from rates where CurrencyCode = %s and Date = %s order by Buying DESC limit 1;', ('GBP',date))
result = self.cur.fetchall()[0]
best_rate = result[3]
best_full_rate = result_full[5]
best_GBP = result if best_rate > best_full_rate else result_full
best_rates = [list(best_USD), list(best_EUR), list(best_GBP)]
for best in best_rates:
best[1] = best[1].strftime('%d-%m-%Y')
if len(best) == 7:
best[3] = best[5]
best[4] = best[6]
best.pop()
best.pop()
best[0], best[1] = best[1], best[0]
return pd.DataFrame(data =best_rates, columns = ['Date', 'Bank', 'Currency Code', 'Buying', 'Selling']).set_index('Date')
def visualize_historical_data(self):
self.cur.execute('select Date, TransactionalSelling from full_rates where bank = %s AND CurrencyCode = %s order by date desc limit 30', ('CBE', 'USD'))
data = self.cur.fetchall()
data= [[pd.to_datetime(price[0], format='%d-%m-%Y'), float(price[1])] for price in data]
USD_df = pd.DataFrame(data, columns=['Date', 'Currency selling']).set_index('Date')
USD_df.index = pd.to_datetime(USD_df.index)
USD_df.sort_index(inplace=True)
fig = px.line(USD_df, y='Currency selling')
return fig
class Ui:
def __init__(self):
st.set_page_config(layout="wide")
st.title('Exchange Rate')
st.header('Best Currency rates in Ethiopia')
banks = Banks()
banks_name = banks.banks_name()
banks_name_full = banks.banks_name_full()
Banks_name = banks_name + banks_name_full
Banks_name.sort()
st.write('These are the best currency rates in Ethiopia right now')
st.dataframe(banks.best_rates(), width = 1000)
st.header('Commercial Bank of Ethiopia USD selling rate')
st.plotly_chart(banks.visualize_historical_data())
st.header('List of Currency rates')
for name in Banks_name:
with st.expander(f'{name} bank exchange rate'):
if name in banks_name_full:
date, rates = banks.bank_full_rate_table(name)
else:
date, rates = banks.bank_rate_table(name)
st.write('Last updated', date)
st.dataframe(rates, width = 1000)
# Footer
st.markdown("---")
st.markdown("Developed by Yosef. 2024 V1.0")
if __name__ == '__main__':
ui = Ui()