#!/usr/bin/env python
# coding: utf-8

# Web Automation and Information Search with Python
#
# #### Challenge:
#
# We work in an importing company and the price of our products is linked to the quotation of:
# – Dollar
# – Euro
# – Gold
#
# We need to automatically get on the internet the quotation of these 3 items and know how much we should charge for our products, considering a contribution margin that we have in our database.
#
# Database: https://drive.google.com/drive/folders/1KmAdo593nD8J9QBaZxPOG1yxHZua4Rtv?usp=sharing
#
# To do this, we will create a web automation:
#
# – We will use selenium
# – Important: download webdriver

# In[30]:

# chromedriver -> Chrome
# geckodrive -> Firefox

from selenium import webdriver # create the browser
from selenium.webdriver.common.by import By # Find elements – the items of a site
from selenium.webdriver.common.keys import Keys # allow clicking keys on keyboard

browser = webdriver.Chrome()

# Step 1 – Enter google
browser.get(“https://www.google.com/”)

# Step 2 – Research the Dollar Exchange Rate
browser.find_element(By.XPATH, ‘/html/body/div[1]/div[3]/form/div[1]/div[1]/div[1]/div/div[2]/input’).send_keys(“dollar quote”)
browser.find_element(By.XPATH, ‘/html/body/div[1]/div[3]/form/div[1]/div[1]/div[1]/div/div[2]/input’).send_keys(Keys.ENTER)

# Step 3 – Get the Dollar Quotation
dollar_quote = browser.find_element(By.XPATH, ‘//*[@id=”knowledge-currency__updatable-data-column”]/div[1]/div[2]/span[1]’).get_attribute(‘data-value’)
print(dollar_quote)

# Step 4 – Get the Euro rate
browser.get(“https://www.google.com/”)

browser.find_element(By.XPATH, ‘/html/body/div[1]/div[3]/form/div[1]/div[1]/div[1]/div/div[2]/input’).send_keys(“euro quote”)

browser.find_element(By.XPATH, ‘/html/body/div[1]/div[3]/form/div[1]/div[1]/div[1]/div/div[2]/input’).send_keys(Keys.ENTER)

quote_euro = browser.find_element(By.XPATH, ‘//*[@id=”knowledge-currency__updatable-data-column”]/div[1]/div[2]/span[1]’).get_attribute(‘data-value’)

print(quote_euro)

# Step 5 – Get the gold quote
browser.get(“https://www.melhorcambio.com/ouro-hoje”)

gold_quote = browser.find_element(By.XPATH, ‘//*[@id=”commercial”]’).get_attribute(‘value’)
quote_gold = quote_gold.replace(“,”,”.”)

print(gold_quote)

# In[31]:

# !pip install selenium

### Now we will update our price base with the new quotes

# – Importing the database

# In[32]:

# Step 6 – Update my database with the new quotes
import pandas as pd

table = pd.read_excel(“Products.xlsx”)
display(table)

# – Updating Prices and Calculating the Final Price

# In[33]:

# update the quote according to the corresponding currency
# dollar
# the rows where the “Currency” column = “Dollar
table.loc[tabela[‘Moeda’] == “Dollar”, “Quote”] = float(quote_dollar)

# euro
table.loc[tabela[‘Moeda’] == “Euro”, “Rate”] = float(rate_euro)

# gold
table.loc[tabela[‘Moeda’] == “Gold”, “Quote”] = float(quote_gold)

# maintain purchase price = original price * quotation
table[“Purchase Price”] = table[“Original Price”] * table[“Quote”]

# maintain sales price = purchase price * margin
table[“Sales Price”] = table[“Purchase Price”] * table[“Margin”]
display(table)

# Now let’s export the new updated price base

# In[34]:

table.to_excel(“NewProducts.xlsx”, index=False)
browser.quit()

# In[ ]: