<jupyter_start><jupyter_text>**Assignment Ideas**1. State the problem clearly2. Describe the input and output information3. Work the problem by hand4. Develop and algorithm 5. Test slution with a variety of dataProgram, Inputs, Processing and Outputs Inputs*Input is a ticker symbol?Note: To prompt the user, I guess I will need to use the input function **Find out what a is a ticker symbol is** A stock symbol is a unique series of letters assigned to a security for trading purposes. Stocks listed on the New York Stock Exchange (NYSE) can have four or fewer letters.https://www.investopedia.com/terms/s/stocksymbol.asp Processing thoughts *scrape the yahoo website*extract the 'key executives'*Calculate the income tax**Find yahoo webstie****Source the key executives**Here is the webpage in which data is to be retrieved https://au.finance.yahoo.com/Note: There is a search bar, will use to find a companyOutputs*Save to a CSV file*this simply requires me to use a pandas command exporting a table using ```exec_df.to_csv```*Save to an SQL database*Requires a little bit more work, but the easiest solution is to create a temporary CSV file and have the SQL insert commands be created from there.https://stackoverflow.com/questions/38134503/csv-file-to-sql-insert-statementExplore the website1. Search for Telstra Best guess - TLS.AX is Telstra '.AX' indicates that the company is AustralianHere is the URL for Telstrahttps://au.finance.yahoo.com/quote/TLS.AX/profile?p=TLS.AX2. Find NABHere is the URL for NABhttps://au.finance.yahoo.com/quote/NAB.AX/profile?p=NAB.AXThe URL pattern is distinct, there are two places in which the ticker search appears.https://au.finance.yahoo.com/quote/____/profile?p=____Manual process is1. Search for the ticker2. Click on 'Profile'**TO DO: This process be shortcutted and just use the URL**The URL can be edited to replace the ticker - only the first mention of the ticker is important, but to be safe both should be replaced. Calculation of income tax```Name	Title	Pay	Exercised	Year bornMr. <NAME> CBE, B.B.S.	Group CEO, MD & Director	2.54M	N/A	1957Mr. <NAME> BEC, F.C.A., BEc (Hons), FCA	Group Chief Financial Officer	1.13M	N/A	N/AMr. <NAME>	Group Exec. of Technology & Enterprise Operations	1.72M	N/A	1967Ms. <NAME>	Group Exec. of Legal & Commercial Services	912.98k	N/A	N/AMs. Susan Ferrier	Group Exec. of People & Culture	929.27k	N/A	N/AMs. <NAME>	Group Chief Digital, Data & Analytics Officer	1.62M	N/A	N/AMr. <NAME>	Chief Risk Officer	1.05M	N/A	N/AMr. <NAME>	Chief Exec. of Bus. Private Banking Division	333.27k	N/A	N/AMr. <NAME>	Group Chief Operating Officer	N/A	N/A	1960Ms. <NAME>	Head of Investor Relations	N/A	N/A	N/A```The first column is the executive's respective namesThe second is their title/ position in the organisationThe third is their annual income/ pay**TO DO: How to do we extract the third column****RESOLUTION** 18/10/2021  ```  pay = rec['Pay'] Needs to be sourced from the previous function  last_char = pay[-1]  -1 retrieves the last character in the pay cell  num_part = pay[0:-1]```Only need the name, title and pay```Mr. <NAME> CBE, B.B.S.	Group CEO, MD & Director	2.54M	N/A	1957```Thrid column is pay; 2.54mM is assumedly "Million"; The pay being 2.5 * 1000000 Inspect another row for feasibility ```Ms. <NAME>	Group Exec. of Legal & Commercial Services	912.98k	N/A	N/A```Noticed the pay is 912.8kK assumedly meaning thousand or 1000; therefore their pay is 912.8*1000 **Resolution for symbols indicating salary** 19/10/2021```  if last_char == 'M':    income = float(num_part) * 1000000    elif last_char == 'k':    income = float(num_part) * 1000  else:    income = 0   return income``` **Input validation codes that were attempted but failed.***Led to an indefinitely repeating error message*```def get_rows(soup):   while True:    try:      table = soup.find('table')       get to the row      body = table.find('tbody')      rows = body.find_all('tr')      records = []      for row in rows:        result = row_capture(row)        records.append(result)      return rows    except AttributeError:      print("Please enter a valid ticker identifier")```*Led to a reinput, that caused a blank box*```def get_rows(soup):   while True:    try:      table = soup.find('table')       get to the row      body = table.find('tbody')      rows = body.find_all('tr')      records = []      for row in rows:        result = row_capture(row)        records.append(result)      return rows    except AttributeError:      print("Please enter a valid ticker identifier")      return generate_url()```<jupyter_code>!pip install kora -q
#Must be installed for the program to work
from bs4 import BeautifulSoup
from kora.selenium import wd
import pandas as pd
import csv
def generate_url():
  # User input ticker symbol
  ticker = input("Please input ticker of the company you wish to search ")
  
  #The URL that will be generated based on the ticker that is entered
  example_url = 'https://au.finance.yahoo.com/quote/{}/profile?p={}'
  result = example_url.format(ticker, ticker)
  return result
 def row_capture(row):
  #Specifies what in the HTML for the row to capture for the program 
  cells = row.find_all('td')
  name = cells[0].text
  title = cells[1].text
  pay = cells[2].text
  return {'Name': name, 'Title': title, 'Pay': pay}
 def get_rows(soup): 
  #specifies the table on the HTML
  table = soup.find('table')
  body = table.find('tbody')
  rows = body.find_all('tr')
  # attempted to add the input validation here (see above notes)
  return rows
def get_salary(pay):
  #Captures the last character of the cell which is a symbol 
  pay = rec['Pay']
  last_char = pay[-1]
  num_part = pay[0:-1]
  #Gives the symbols numerical value so that the tax may be calculated
  if last_char == 'M':
    inc = float(num_part) * 1000000
  elif last_char == 'k':
    inc = float(num_part) * 1000
  else:
    inc = 0
  
  return inc
def calculate_tax(income):
  if income <= 18200:
    tax_payable = 0
  elif 18200 < income <= 45000:
    tax_payable = 0.19 * (income - 18200)
  elif 45000 < income < 120000:
    tax_payable = 5092 + 0.32 * (income - 45000)
  elif 120001 < income < 180000:
    tax_payable = 29467 + 0.37 * (income - 120000)
  elif income > 180000:
    tax_payable = 51667 + 0.45 * (income - 180000)
  
  return tax_payable
def export_table():
  while True:
    query = input("How would you like to save your table? ")
    #CSV allows the file to be named and saved
    if query == 'csv':
      csv_save = input("Please name the csv file you could like to save: ")
      exec_df.to_csv(csv_save + '.csv', encoding ='utf-8', index=False)
      print("Success! Please find your file attached in the folder on the left shortly!")
      break
    #SQL saves a temporary file and reads off it providing INSERT commands
    elif query == 'sql':
      exec_df.to_csv('table_as_sql.csv', encoding ='utf-8', index=False)
      openFile = open('table_as_sql.csv', 'r')
      csvFile = csv.reader(openFile)
      header = next(csvFile)
      headers = map((lambda x: '`'+x+'`'), header)
      insert = 'INSERT INTO Table (' + ", ".join(headers) + ") VALUES "
      for row in csvFile:
          values = map((lambda x: '"'+x+'"'), row)
          print (insert +"("+ ", ".join(values) +");" )
      openFile.close()
      break
    else:
      print("Please type either 'csv' or 'sql'")
def tables():
  while True:
    tabulisation  = input("Would you like for your data to be exported? ")
    if tabulisation == "yes":
      export_table()
      break
    #The following elif statement attempts to display the table on colab
    elif tabulisation == "no":
      exec_df = pd.DataFrame.from_records(records)
      exec_df
      break
    else:
      ("Please input either 'yes' or 'no'")
url = generate_url()
wd.get(url)
soup = BeautifulSoup(wd.page_source,'html.parser')
rows = get_rows(soup)
records = []
for row in rows:
  result = row_capture(row)
  records.append(result)
# attempted to add a tax column as it's own function however errors refering to "rec" not being defined permeated through the code
for rec in records:
  sal = get_salary(rec['Pay'])
  rec['Tax'] = calculate_tax(sal)
#This calls the table function and determines how the retrieved data is presented
tables()
exec_df = pd.DataFrame.from_records(records)
exec_df<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.10410170304629407, 'fraction_numerical': 0.029383545214679778, 'mean_word_length': 3.6327777777777777, 'pattern_counts': {'":': 2, '<': 23, '<?xml version=': 0, '>': 17, 'https://': 7, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '280324', 'license': 'MIT', 'n_tokens_mistral': 2787, 'n_tokens_neox': 2546, 'n_words': 1021, 'path': 'ISYS5002 - Assignment_Final_Version_20484040.ipynb', 'repo_name': 'shimossa/ISYS5002_Assignment_One', 'size': '8447'}