notebooks/itr-schedule-fa-automated.ipynb

{
 "cells": [
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# ITR Schedule FA Automated Data Entry\n",
    "Automatically enter data from an excel file to the ITR Schedule FA in the ITR-2 form.\n",
    "\n",
    "---"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pre-requisites\n",
    "- Create conda env using the following command from the root of this repo: `conda env create -f ./env/itr-schedule-fa-automated.yaml`. \n",
    "- Download the webdriver for your browser and place it in some directory and keep a note of it. The notebook is currently configured to use the Chrome webdriver and will work for any Chromium based browser. Download the Chrome webdriver from [here](https://chromedriver.chromium.org/downloads) or [**here**](https://googlechromelabs.github.io/chrome-for-testing/#stable).\n",
    "- Make sure that you have both browser and driver for the **same version**.\n",
    "- Following variables are **required to be set** before running the script:\n",
    "\n",
    "| Variable | Description | Example |\n",
    "| --- | --- | --- |\n",
    "| `chrome_driver_path` | Local path to the downloaded webdriver **chromedriver.exe** file | r\"C:\\Files\\chromedriver_win32\\chromedriver.exe\" |\n",
    "| `browser_path` | Local path to the **chrome.exe** file | r\"C:\\Files\\Google\\Chrome\\Application\\chrome.exe\" |\n",
    "| `excel_file_path` | Path to the excel file containing Schedule FA details | r\"C:\\Files\\itr-schedule-fa.xlsx\" |\n",
    "| `country` | Value for country that will be used in the Schedule FA form at UI  | Already defined below |"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 73,
   "metadata": {},
   "outputs": [],
   "source": [
    "chrome_driver_path = r\"<PATH_TO_CHROME_DRIVER>\"\n",
    "browser_path = r\"<PATH_TO_BROWSER>\"\n",
    "excel_file_path = r\"<PATH_TO_SCHEDULE_FA_REPORT>\"\n",
    "\n",
    "country = \"2 - UNITED STATES\""
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Read Excel File\n",
    "The following cell reads in the data from the excel file and stores it in a dataframe named `df`."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "\n",
    "month_map = {\n",
    "    \"01\": \"Jan\",\n",
    "    \"02\": \"Feb\",\n",
    "    \"03\": \"Mar\",\n",
    "    \"04\": \"Apr\",\n",
    "    \"05\": \"May\",\n",
    "    \"06\": \"Jun\",\n",
    "    \"07\": \"Jul\",\n",
    "    \"08\": \"Aug\",\n",
    "    \"09\": \"Sep\",\n",
    "    \"10\": \"Oct\",\n",
    "    \"11\": \"Nov\",\n",
    "    \"12\": \"Dec\",\n",
    "}\n",
    "df = pd.read_excel(excel_file_path)\n",
    "df.head()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Preprocess Data\n",
    "Make sure that the data in the excel file does not contain any null values. If there are any null values, replace them with appropriate values. Also, go through the data and do your own sanity checks to ensure that the data is correct and good to go."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 75,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Country Name                                                                       4\n",
       "Country Code                                                                      16\n",
       "Name of entity                                                                    16\n",
       "Address of entity                                                                 16\n",
       "zip code                                                                          16\n",
       "Nature of entity                                                                  16\n",
       "Date of acquiring interest/ stake                                                 16\n",
       "Initial value of investment                                                       16\n",
       "Peak value of investment                                                          16\n",
       "Closing Balance                                                                   16\n",
       "Total gross amount paid/credited with respect to the holding during the period    16\n",
       "Total gross proceeds from sale or redemption of investment during the period      16\n",
       "dtype: int64"
      ]
     },
     "execution_count": 75,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check null values in df\n",
    "df.isnull().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "print(f\"Length of df before dropping rows with null values: {len(df)}\")\n",
    "\n",
    "# remove rows where \"Country Name\" is null\n",
    "df = df.dropna(subset=[\"Country Name\"])\n",
    "\n",
    "# remove rows where \"Country Code\" is null\n",
    "df = df.dropna(subset=[\"Country Code\"])\n",
    "\n",
    "# convert \"Date of acquiring interest/ stake\" column to date\n",
    "df[\"Date of acquiring interest/ stake\"] = pd.to_datetime(df[\"Date of acquiring interest/ stake\"], errors=\"coerce\")\n",
    "\n",
    "# replace value \"-\" by 0 in columns \"Total gross amount paid/credited with respect to the holding during the period\" and \"Total gross proceeds from sale or redemption of investment during the period\"\n",
    "df[\"Total gross amount paid/credited with respect to the holding during the period\"] = df[\"Total gross amount paid/credited with respect to the holding during the period\"].replace(\"-\", 0)\n",
    "df[\"Total gross proceeds from sale or redemption of investment during the period\"] = df[\"Total gross proceeds from sale or redemption of investment during the period\"].replace(\"-\", 0)\n",
    "\n",
    "# convert columns \"Initial value of investment\", \"Peak value of investment\", \"Closing Balance\", \"Total gross amount paid/credited with respect to the holding during the period\" and \"Total gross proceeds from sale or redemption of investment during the period\" to int\n",
    "df[\"Initial value of investment\"] = df[\"Initial value of investment\"].astype(int)\n",
    "df[\"Peak value of investment\"] = df[\"Peak value of investment\"].astype(int)\n",
    "df[\"Closing Balance\"] = df[\"Closing Balance\"].astype(int)\n",
    "df[\"Total gross amount paid/credited with respect to the holding during the period\"] = df[\"Total gross amount paid/credited with respect to the holding during the period\"].astype(int)\n",
    "df[\"Total gross proceeds from sale or redemption of investment during the period\"] = df[\"Total gross proceeds from sale or redemption of investment during the period\"].astype(int)\n",
    "\n",
    "print(f\"Length of df after dropping rows with null values: {len(df)}\")\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 79,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Country Name                                                                      0\n",
       "Country Code                                                                      0\n",
       "Name of entity                                                                    0\n",
       "Address of entity                                                                 0\n",
       "zip code                                                                          0\n",
       "Nature of entity                                                                  0\n",
       "Date of acquiring interest/ stake                                                 0\n",
       "Initial value of investment                                                       0\n",
       "Peak value of investment                                                          0\n",
       "Closing Balance                                                                   0\n",
       "Total gross amount paid/credited with respect to the holding during the period    0\n",
       "Total gross proceeds from sale or redemption of investment during the period      0\n",
       "dtype: int64"
      ]
     },
     "execution_count": 79,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# check null values in df\n",
    "df.isnull().sum()"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Connect selenium with existing browser session"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Open chrome browser in debugging mode: \n",
    "    - Open terminal and navigate to the directory where you have the chrome browser file (chrome.exe in case of windows).\n",
    "    - Execute the following command: `chrome --remote-debugging-port=9222`. This will open the chrome browser in debugging mode.\n",
    "2. Login to the ITR portal in the opened window:\n",
    "    - Login to the ITR portal.\n",
    "    - Complete the steps and open the **Schedule FA** page in the ITR-2 form.\n",
    "    - Please delete if there is any existing entry.\n",
    "    - Make sure to do the first entry manually. The script will start entering data from the second row onwards.\n",
    "    - Make sure your page looks like this before running the next cell:<br/>\n",
    "    ![Schedule FA](../images/schedule-fa-pre-requisite.png)\n",
    "\n",
    "> Note: Notice that the **A3** section in the above image is collapsed. Make sure you have the same state in your web browser window. This is important because the section gets auto collpased when you succesfuly add a row to this section."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 80,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "ITR2 Schedule Foreign Assets\n"
     ]
    }
   ],
   "source": [
    "from selenium import webdriver\n",
    "from selenium.webdriver.common.by import By\n",
    "from selenium.webdriver.support.ui import Select\n",
    "from selenium.webdriver.chrome.options import Options\n",
    "from selenium.webdriver.chrome.service import Service\n",
    "from time import sleep\n",
    "   \n",
    "chrome_options = Options()\n",
    "chrome_options.add_experimental_option(\"debuggerAddress\", \"127.0.0.1:9222\")\n",
    "chrome_options.binary_location = browser_path\n",
    "\n",
    "service = Service(executable_path=chrome_driver_path)\n",
    "\n",
    "driver = webdriver.Chrome(service=service, options=chrome_options)\n",
    "print(driver.title)"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Copy data from dataframe to the UI\n",
    "The following cell iterates over the dataframe `df` row by row and does the following for each row:\n",
    "- fetches values from the columns (you will need to **modify** the column names based on what you have in the excel file)\n",
    "- gets the UI elements for the corresponding columns\n",
    "- fills the fetched values in the correspoding fetched UI elements\n",
    "- prints the row number after the row is successfully copied (this is helpful in case you want to run the cell again after some error, set `idx_start` to the next row number that was printed in the output)\n",
    "\n",
    "The cell will take time dependiong on the number of rows in your excel file. Please be patient.\n",
    "\n",
    "> Note: The variable `idx_start` is used to specify the row number from which the script should start entering data. This is useful in case you want to run the script again after some error. In that case, you can set the value of `idx_start` to the row number from which you want to start entering data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "idx_start = 1\n",
    "\n",
    "for idx in range(idx_start, len(df)):\n",
    "\n",
    "    # get values for all fields from df\n",
    "    row = df.iloc[idx]\n",
    "    name_of_entity = row[\"Name of entity\"]\n",
    "    address_of_entity = row[\"Address of entity\"]\n",
    "    zip_code = row[\"zip code\"]\n",
    "    nature_of_entity = row[\"Nature of entity\"]\n",
    "    date = str(row[\"Date of acquiring interest/ stake\"])\n",
    "    init_value = str(row[\"Initial value of investment\"])\n",
    "    peak_value = str(row[\"Peak value of investment\"])\n",
    "    closing_value = str(row[\"Closing Balance\"])\n",
    "    gross_amount = str(\n",
    "        row[\n",
    "            \"Total gross amount paid/credited with respect to the holding during the period\"\n",
    "        ]\n",
    "    )\n",
    "    gross_proceeds = str(\n",
    "        row[\n",
    "            \"Total gross proceeds from sale or redemption of investment during the period\"\n",
    "        ]\n",
    "    )\n",
    "\n",
    "    # click on 'A3' section\n",
    "    a3_section = driver.find_element(\n",
    "        By.XPATH, '//*[@id=\"uniservenxtcmp_hyper_490\"]/div/div[1]/div/div/span'\n",
    "    )\n",
    "    a3_section.click()\n",
    "\n",
    "    # sleep(2)\n",
    "\n",
    "    # click on 'Add Another' button\n",
    "    add_another_elem = driver.find_element(\n",
    "        By.XPATH, '//*[@id=\"uniservenxtcmp_button_330\"]'\n",
    "    )\n",
    "    sleep(1)\n",
    "    add_another_elem.click()\n",
    "\n",
    "    # get all elements from UI\n",
    "    country_elem = driver.find_element(\n",
    "        By.XPATH, '//*[@id=\"uniservenxtcmp_dropdown_164\"]'\n",
    "    )\n",
    "    name_of_entity_elem = driver.find_element(\n",
    "        By.XPATH, '//*[@id=\"uniservenxtcmp_textbox_203\"]'\n",
    "    )\n",
    "    address_of_entity_elem = driver.find_element(\n",
    "        By.XPATH, '//*[@id=\"uniservenxtcmp_textbox_204\"]'\n",
    "    )\n",
    "    zip_code_elem = driver.find_element(\n",
    "        By.XPATH, '//*[@id=\"uniservenxtcmp_textbox_205\"]'\n",
    "    )\n",
    "    nature_of_entity_elem = driver.find_element(\n",
    "        By.XPATH, '//*[@id=\"uniservenxtcmp_textbox_206\"]'\n",
    "    )\n",
    "    date_elem = driver.find_element(By.XPATH, '//*[@id=\"uniservenxtcmp_textbox_471\"]')\n",
    "    init_value_elem = driver.find_element(\n",
    "        By.XPATH, '//*[@id=\"uniservenxtcmp_textbox_207\"]'\n",
    "    )\n",
    "    peak_value_elem = driver.find_element(\n",
    "        By.XPATH, '//*[@id=\"uniservenxtcmp_textbox_208\"]'\n",
    "    )\n",
    "    closing_value_elem = driver.find_element(\n",
    "        By.XPATH, '//*[@id=\"uniservenxtcmp_textbox_209\"]'\n",
    "    )\n",
    "    gross_amount_elem = driver.find_element(\n",
    "        By.XPATH, '//*[@id=\"uniservenxtcmp_textbox_210\"]'\n",
    "    )\n",
    "    gross_proceeds_elem = driver.find_element(\n",
    "        By.XPATH, '//*[@id=\"uniservenxtcmp_textbox_211\"]'\n",
    "    )\n",
    "    add_elem = driver.find_element(By.XPATH, '//*[@id=\"uniservenxtcmp_button_279\"]')\n",
    "\n",
    "    # Put value into country dropdown at UI\n",
    "    country_select = Select(country_elem)\n",
    "    # sleep(2)\n",
    "    country_select.select_by_visible_text(country)\n",
    "\n",
    "    # Put value into all other elements at UI\n",
    "    name_of_entity_elem.send_keys(name_of_entity)\n",
    "    address_of_entity_elem.send_keys(address_of_entity)\n",
    "    zip_code_elem.send_keys(zip_code)\n",
    "    nature_of_entity_elem.send_keys(nature_of_entity)\n",
    "    init_value_elem.send_keys(init_value)\n",
    "    peak_value_elem.send_keys(peak_value)\n",
    "    closing_value_elem.send_keys(closing_value)\n",
    "    gross_amount_elem.send_keys(gross_amount)\n",
    "    gross_proceeds_elem.send_keys(gross_proceeds)\n",
    "\n",
    "    # Put value into date element at UI\n",
    "    date_arr = date.split(\"-\")\n",
    "    year, month, day = date_arr[0], month_map[date_arr[1]], date_arr[2]\n",
    "    date_elem.click()\n",
    "    month_year_elem = driver.find_element(\n",
    "        By.XPATH, \"/html/body/div[4]/div[1]/table/thead/tr[2]/th[1]\"\n",
    "    )\n",
    "    month_year_elem.click()\n",
    "    year_elem = driver.find_element(\n",
    "        By.XPATH, \"/html/body/div[4]/div[2]/table/thead/tr[2]/th[1]\"\n",
    "    )\n",
    "    year_elem.click()\n",
    "    year_box = driver.find_element(By.XPATH, f\"//*[text()='{year}']\")\n",
    "    year_box.click()\n",
    "    month_box = driver.find_element(By.XPATH, f\"//*[text()='{month}']\")\n",
    "    month_box.click()\n",
    "    day_box = driver.find_element(\n",
    "        By.XPATH, f\"//td[@class='day' and text()='{int(day.split(' ')[0])}']\"\n",
    "    )\n",
    "    # sleep(1)\n",
    "    day_box.click()\n",
    "\n",
    "    # click on 'Add' button to add all the filled details\n",
    "    add_elem.click()\n",
    "\n",
    "    print(f\"idx: {idx} copied successfully.\")\n",
    "    # sleep(1)\n",
    "\n",
    "print(\"-------------Task Completed-------------\")"
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Troubleshooting\n",
    "Whenever the above cell fails, firstly you **must make sure that the page is back to the same state** as [mentioned earlier](../images/schedule-fa-pre-requisite.png) and then you should do the following in order until the issue is resolved:\n",
    "- The cell will print the last row number that was successful. You can set the value of `idx_start` to the last succesful row number + 1 and run the cell again. This will start entering data from the row where it failed.\n",
    "- If you see the same error again, identify the line where it fails. Put a `time.sleep(2)` before the failed line if it's not already there or try increasing the value of `time.sleep()` and run the cell again.\n",
    "\n",
    "> Note: If you can't resolve the error, feel free to create an issue on the [GitHub repository](https://github.com/iamrk04/TaskAutomationKit/issues). Make sure to include the error message with the entire stacktrace."
   ]
  },
  {
   "attachments": {},
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Disclaimer\n",
    "- This is a personal project and is not affiliated with the Income Tax Department of India in any way.\n",
    "- Make sure to **verify the data entered** by the script before submitting the form.\n",
    "- The author does not make any warranty, express or implied, or assume any legal/consequential liability, or responsibility for the authenticity, accuracy and completeness of the data filled at website.\n",
    "- The author is not responsible for any loss or damage caused by the use of this software."
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "pytorch",
   "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}