|
| 1 | +#!/usr/bin/env python3 |
| 2 | + |
| 3 | +import urllib.request as url |
| 4 | +import openpyxl as xl |
| 5 | +import json |
| 6 | + |
| 7 | +from datetime import date, timedelta |
| 8 | + |
| 9 | +# Using Frankfurter API - free, no API key required |
| 10 | +API_ENDPOINT = "https://api.frankfurter.app/" |
| 11 | + |
| 12 | +def main(): |
| 13 | + wb = xl.load_workbook("excel/financial status_2016_test.xlsx", read_only=False) |
| 14 | + sheet_names = wb.sheetnames |
| 15 | + if "Currencies" in sheet_names: |
| 16 | + wb_currencies = wb["Currencies"] |
| 17 | + |
| 18 | + # print type(wb_currencies), " ", wb_currencies.title |
| 19 | + print_previous_rates(wb_currencies) |
| 20 | + data = get_quotes_json() |
| 21 | + update_monthly_quotes(wb_currencies) |
| 22 | + |
| 23 | + # write data |
| 24 | + write_current_quotes_excel(data["rates"], wb_currencies) |
| 25 | + print("Writing file...") |
| 26 | + wb.save("excel/financial_status_2016_out_py.xlsx") |
| 27 | + print("Done.") |
| 28 | + else: |
| 29 | + print("Cannot find sheet Currencies") |
| 30 | + exit() |
| 31 | + |
| 32 | + |
| 33 | +def get_quotes_json(period="live", req_date=None): |
| 34 | + """ |
| 35 | + Fetch exchange rates from Frankfurter API |
| 36 | + Base currency: USD |
| 37 | + Target currencies: RUB, EUR, GBP, CHF |
| 38 | + """ |
| 39 | + # Frankfurter uses EUR as base by default, but we can convert |
| 40 | + # We'll fetch rates from USD to other currencies |
| 41 | + currencies = "RUB,EUR,GBP,CHF" |
| 42 | + |
| 43 | + try: |
| 44 | + if period == "live" and req_date == None: |
| 45 | + # Get latest rates |
| 46 | + req_link = f"{API_ENDPOINT}latest?from=USD&to={currencies}" |
| 47 | + elif req_date != None: |
| 48 | + # Get historical rates |
| 49 | + req_link = f"{API_ENDPOINT}{req_date}?from=USD&to={currencies}" |
| 50 | + else: |
| 51 | + print("Parameters missing for request.") |
| 52 | + return None |
| 53 | + |
| 54 | + # print(f"Fetching: {req_link}") |
| 55 | + |
| 56 | + connection = url.urlopen(req_link) |
| 57 | + response = connection.read() |
| 58 | + result = json.loads(response) |
| 59 | + |
| 60 | + if result and "rates" in result: |
| 61 | + # Transform to match old API format for compatibility |
| 62 | + formatted_result = { |
| 63 | + "success": True, |
| 64 | + "rates": result["rates"], |
| 65 | + "date": result["date"] |
| 66 | + } |
| 67 | + return formatted_result |
| 68 | + else: |
| 69 | + print("Could not fetch currency data.") |
| 70 | + return None |
| 71 | + |
| 72 | + except Exception as e: |
| 73 | + print(f"Error fetching data: {e}") |
| 74 | + return None |
| 75 | + |
| 76 | + |
| 77 | +def update_monthly_quotes(sheet): |
| 78 | + print("Updating historical quotes...") |
| 79 | + i = 8 |
| 80 | + curr_cell = "B" + str(i) |
| 81 | + |
| 82 | + def get_date(curr_cell): |
| 83 | + cell_value = sheet[curr_cell].value |
| 84 | + if cell_value is None: |
| 85 | + return None |
| 86 | + year = cell_value.year |
| 87 | + month = cell_value.month |
| 88 | + day = cell_value.day |
| 89 | + cell_date = date(year, month, day) |
| 90 | + return cell_date |
| 91 | + |
| 92 | + def write_hist_quotes(row, rates): |
| 93 | + if "RUB" in rates: |
| 94 | + sheet["C" + str(i)].value = round(rates["RUB"], 6) |
| 95 | + if "EUR" in rates: |
| 96 | + sheet["D" + str(i)].value = round(1 / rates["EUR"], 6) |
| 97 | + if "CHF" in rates: |
| 98 | + sheet["E" + str(i)].value = round(1 / rates["CHF"], 6) |
| 99 | + if "GBP" in rates: |
| 100 | + sheet["F" + str(i)].value = round(1 / rates["GBP"], 6) |
| 101 | + |
| 102 | + cell_date = get_date(curr_cell) |
| 103 | + while cell_date is not None and (cell_date < date.today()): |
| 104 | + hist_date = cell_date |
| 105 | + rates_data = get_quotes_json("historical", hist_date.isoformat()) |
| 106 | + |
| 107 | + if rates_data and "rates" in rates_data: |
| 108 | + hist_rates = rates_data["rates"] |
| 109 | + write_hist_quotes(i, hist_rates) |
| 110 | + if "RUB" in hist_rates: |
| 111 | + print(f"USDRUB on {hist_date.isoformat()} @ {hist_rates['RUB']}") |
| 112 | + else: |
| 113 | + print(f"Could not fetch rates for {hist_date.isoformat()}") |
| 114 | + |
| 115 | + i += 1 |
| 116 | + curr_cell = "B" + str(i) |
| 117 | + cell_date = get_date(curr_cell) |
| 118 | + |
| 119 | + print("Historical quotes updated.") |
| 120 | + |
| 121 | + |
| 122 | +def write_current_quotes_excel(data, sheet): |
| 123 | + print("Updating current quotes...") |
| 124 | + if "RUB" in data: |
| 125 | + sheet["C4"].value = round(data["RUB"], 6) |
| 126 | + print("=> updated USDRUB: ", sheet["C4"].value) |
| 127 | + if "EUR" in data: |
| 128 | + sheet["D4"].value = round(1 / data["EUR"], 6) |
| 129 | + print("=> updated EURUSD: ", sheet["D4"].value) |
| 130 | + if "CHF" in data: |
| 131 | + sheet["E4"].value = round(1 / data["CHF"], 6) |
| 132 | + print("=> updated CHFUSD: ", sheet["E4"].value) |
| 133 | + if "GBP" in data: |
| 134 | + sheet["F4"].value = round(1 / data["GBP"], 6) |
| 135 | + print("=> updated GBPUSD: ", sheet["F4"].value) |
| 136 | + |
| 137 | + |
| 138 | +def print_previous_rates(sheet): |
| 139 | + print("=> previous USDRUB: ", sheet["C4"].value) |
| 140 | + print("=> previous EURUSD: ", sheet["D4"].value) |
| 141 | + print("=> previous CHFUSD: ", sheet["E4"].value) |
| 142 | + print("=> previous GBPUSD: ", sheet["F4"].value) |
| 143 | + |
| 144 | + |
| 145 | +if __name__ == "__main__": |
| 146 | + main() |
0 commit comments