Exchange Rate Loading from OANDA to GPMA.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "05a86422",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import datetime as dt\n",
    "import smtplib, ssl\n",
    "from email.mime.text import MIMEText\n",
    "from email.mime.multipart import MIMEMultipart"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "38bf0767",
   "metadata": {},
   "outputs": [],
   "source": [
    "#def call_exchangerate(event=None,context=None):\n",
    "#Converting datetime to String for easy access\n",
    "x = dt.datetime.now().strftime(\"%m/%d/%Y\") "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "7d8b68e1",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Generate Start Date and End Date...\n",
    "start_Day = 16\n",
    "end_Day = 15\n",
    "month = int(x.split('/')[0])  # To split month     \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "77154ebb",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Start_date: 2023/8/16,End_date: 2023/9/15,Effective_Year: 2023,Effective_Month: 10\n"
     ]
    }
   ],
   "source": [
    "#Formating Start Date and End Date in DD/MM/YYYY format\n",
    "\n",
    "if month == 1:\n",
    "    #For Loading February month exchange rate in January, start month has to be start from previeous December\n",
    "    start_date = str(int(x.split('/')[2])-1)+'/'+str(month+11)+'/'+str(start_Day)\n",
    "    end_date = str(x.split('/')[2])+'/'+str(month)+'/'+str(end_Day)\n",
    "    eff_year = int(x.split('/')[2])\n",
    "    eff_month = 2\n",
    "    \n",
    "elif month == 12:\n",
    "    #For Loading January month exchange rate in December, start month has to be start from previeous November\n",
    "    start_date = str(int(x.split('/')[2])-1)+'/'+str(month-1)+'/'+str(start_Day)\n",
    "    end_date = str(int(x.split('/')[2])-1)+'/'+str(month)+'/'+str(end_Day)\n",
    "    eff_year = str(int(x.split('/')[2])+1)\n",
    "    eff_month = 1\n",
    "\n",
    "else:\n",
    "    #For Other than January & February month in calendar year below condition has to be executed\n",
    "    start_date = str(int(x.split('/')[2]))+'/'+str(month-1)+'/'+str(start_Day)\n",
    "    end_date = str(x.split('/')[2])+'/'+str(month)+'/'+str(end_Day)\n",
    "    eff_year = str(x.split('/')[2])\n",
    "    eff_month = int(month) + 1\n",
    "    print(\"Start_date: {},End_date: {},Effective_Year: {},Effective_Month: {}\".format(start_date,end_date,eff_year,eff_month))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bc85c2a3",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Defining Currency code in lists\n",
    "gpma_cur = ['HKD','TWD','PLN','JPY','CNY','KRW','EUR','THB','USD','VND']\n",
    "#['USD','JPY','CNY','HKD','KRW','TWD','PLN','EUR','THB','VND']\n",
    "y_df = pd.DataFrame()\n",
    "for x in gpma_cur:\n",
    "    #Generating Dynamic URL framing using Generated Start & End Date rang\n",
    "    url_df=\"http://www.oanda.com/rates/api/v1/rates/\"+x+\".json?api_key=gpma1203&decimal_places=9&start=\"+start_date+\"&end=\"+end_date+\"&fields=averages&quote=HKD&quote=TWD&quote=PLN&quote=JPY&quote=CNY&quote=KRW&quote=EUR&quote=THB&quote=USD&quote=VND\"\n",
    "    y_df = pd.DataFrame(y_df.append(pd.read_json(url_df)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c87c0614",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Deleting the unnecessary row from the dataframe\n",
    "y_df.drop(index=['effective_params','request_time','skipped_currencies'],inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "65ccb2c6",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Deleting the unnecessary column from the dataframe\n",
    "y_df.drop(columns='meta',inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a24f26a7",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Setting up Index as new column \"conv_to_cur_code\" \n",
    "y_df['conv_to_cur_code'] = y_df.index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bb8e0ff1",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Creating Data source column on dataframe\n",
    "y_df['data_srce_id'] = 1\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7752ba3c",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Creating new columns ask_price and bid_price on dataframe\n",
    "y_df['ask_price'] = pd.Series()\n",
    "y_df['bid_price'] = pd.Series()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d04bc517",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Creating new Columns Effective Month and  Effective Year\n",
    "y_df['Effective_Year'] = eff_year\n",
    "y_df['Effective_Month'] = eff_month"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8f87d2f7",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Realigning Columns name\n",
    "y_df = y_df[['data_srce_id','base_currency','conv_to_cur_code','ask_price','bid_price','Effective_Year','Effective_Month','quotes']]\n",
    "y_df.rename(columns={'data_srce_id':'Data_SourceID','base_currency':'Base_Currency','conv_to_cur_code':'To_Currency',\n",
    "                         'ask_price':'ASK_Price','bid_price':'BID_Price','quotes':'Quotes'},inplace=True)  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "495ee368",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Feeding existing dictionary column data from quotes columns to \"ask_price\" & \"bid_price\" on data frame\n",
    "for t in range(0,len(y_df)):\n",
    "    if (y_df['Base_Currency'][t] == 'JPY') or (y_df['To_Currency'][t] == 'JPY'):\n",
    "        a = float(y_df['Quotes'][t]['ask'])\n",
    "        b = str(int(a))\n",
    "        c = str(a - int(a))[1:9]\n",
    "        ap = b + c\n",
    "        #print(\"Base_currency:{},To_currency:{},Ask Price:{},row_count:{}\".format(y_df['Base_Currency'][t],y_df['To_Currency'][t],float(ap),t))\n",
    "        y_df['ASK_Price'][t] = float(ap)\n",
    "        d = float(y_df['Quotes'][t]['bid'])\n",
    "        e = str(int(d))\n",
    "        f = str(d - int(d))[1:9]\n",
    "        bp = e + f\n",
    "        y_df['BID_Price'][t] = float(bp)\n",
    "        \n",
    "        #Below else if condition created becuase for Base currency VND to EUR & USD conversion values is 0 so i type cast it to Integer  \n",
    "    elif (y_df['Base_Currency'][t] == 'VND' and (y_df['To_Currency'][t] == 'EUR'or y_df['To_Currency'][t] == 'USD')):\n",
    "        y_df['ASK_Price'][t] = 0.0000\n",
    "        y_df['BID_Price'][t] = 0.0000\n",
    "            \n",
    "    else:\n",
    "        a = float(y_df['Quotes'][t]['ask'])\n",
    "        b = str(int(a))\n",
    "        c = str(a - int(a))[1:6]\n",
    "        ap = b + c\n",
    "        #print(\"Base_currency:{},To_currency:{},Ask Price:{},row_count:{}\".format(y_df['Base_Currency'][t],y_df['To_Currency'][t],float(ap),t))\n",
    "        y_df['ASK_Price'][t] = float(ap)\n",
    "        d = float(y_df['Quotes'][t]['bid'])\n",
    "        e = str(int(d))\n",
    "        f = str(d - int(d))[1:6]\n",
    "        bp = e + f\n",
    "        y_df['BID_Price'][t] = float(bp)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ba54dcd5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the plain-text and HTML version of your message\n",
    "text = y_df.to_html()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d7f033b1",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Turn these into plain/html MIMEText objects\n",
    "part1 = MIMEText(text, \"html\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9de3c0c0",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "64861961",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Triggering email with the content of dataframe as HTML to receptents\n",
    "sender_email = \"mailsivacs@gmail.com\"\n",
    "receiver_email = [\"snatarajan5@mmm.com\",\"mailsivacs@gmail.com\"]\n",
    "password = input(\"Type your password and press enter:\")\n",
    "\n",
    "message = MIMEMultipart(\"alternative\")\n",
    "message[\"Subject\"] = \"OANDA Exchange Rates\"\n",
    "message[\"From\"] = sender_email\n",
    "message[\"To\"] = \",\".join(receiver_email)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "43a7b074",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create the plain-text and HTML version of your message\n",
    "text = y_df.to_html()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2a875789",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Turn these into plain/html MIMEText objects\n",
    "part1 = MIMEText(text, \"html\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1936c129",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add HTML/plain-text parts to MIMEMultipart message\n",
    "# The email client will try to render the last part first\n",
    "message.attach(part1)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "de85d37b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create secure connection with server and send email\n",
    "context = ssl.create_default_context() \n",
    "with smtplib.SMTP_SSL(\"smtp.gmail.com\", 465, context=context) as server:\n",
    "    server.login(sender_email, password)\n",
    "    server.sendmail(sender_email, receiver_email, message.as_string()) "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9e58180d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "26d375f5",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "825c18bb",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6b847c1b",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}