# # 🚀 How to Run Python in an Open Workbook
# 👉 Link to Documentation: https://docs.xlwings.org/en/stable/index.html

# ### Datasets  [for demonstration purposes]
# #### 1. Stock Market Data 📈
# Import libaries
import pandas as pd
from pandas_datareader import data

# Get Stockdata (only adjusted close)
def retrieve_stock_data(ticker_symbol):
    df = data.DataReader(
        ticker_symbol, start="2020-12-1", end="2020-12-31", data_source="yahoo"
    )[["Adj Close"]]
    df = df.rename(columns={"Adj Close": f"Adj Close {ticker_symbol}"})
    return df


df = retrieve_stock_data("TSLA")

# Print out head
df.head()


# #### 2. MPG Dataset  🚗
mpg = pd.read_csv(
    "https://raw.githubusercontent.com/mwaskom/seaborn-data/master/mpg.csv"
)
mpg

# # 0. Installation & Import of xlwings
# Install xlwings into the instance of Python that launched the notebook
# xlwings comes preinstalled with Anaconda. Make sure to update your version to use the latest features
get_ipython().system("pip install xlwings --upgrade --quiet ")

# Import xlwings
import xlwings as xw


# # 1. Quick & Easy Way

# View Dataset in Excel
xw.view(mpg)

# View Dataset in Excel
xw.view(df)


# Load selected data into pandas dataframe (New in version 0.22.0.)
ticker_df = xw.load()


# Check type of ticker_df
type(ticker_df)


# # 2. Connect to Excel Workbook
# ## Three different scenarios [New Book, Unsaved Book, Saved Book]
# 👉 **`xlwings`** Documentation: https://docs.xlwings.org/en/stable/connect_to_workbook.html

# ## 2.1 Connect to new workbook
# Open up a new Excel Workbook
wb = xw.Book()

# Define sheet
sht = wb.sheets[0]

# Write Data to cell A1
sht.range("A1").value = ticker_df


# ## 2.2 Connect to an open, but unsaved workbook
# Connect to unsaved workbook
wb = xw.Book("Book2")

# Define sheet
sht = wb.sheets[0]

# Write stock data to worksheet
sht.range("A1").value = df

# Save Workbook
wb.save("stocks.xlsx")

# Close Workbook
wb.close()


# ## 2.3 Connect to an open and saved workbook
# Using pathlib (works on Mac & Windows)
from pathlib import Path  # Standardlibrary (no need to install)

filename = Path("stocks.xlsx")

# Connect & open a saved Workbook
wb = xw.Book(filename)

# Specify Worksheet
sht = wb.sheets["Sheet1"]

# # 3. Practical Examples
# ## 3.1 Basic Examples
# Add a new sheet
sht = wb.sheets.add("Basic Examples")

# Read single cell from Excel
ticker_symbol = sht.range("A1").value

# Read range from Excel
ticker_symbol = sht.range("A1:A3").value

# Insert Formula
sht.range("B1").formula = "=A1"

# Read Name Range
months = sht.range("months").value

# Create Name Range
sht.range("A1:A3").name = "ticker_list"

# Read data from excel, store values in pandas dataframe
new_df = sht.range("D2").options(pd.DataFrame, expand="table").value

# Save workbook as PDF
wb.to_pdf("MyPDF")


# ## 3.2 Plot Stockprice in Excel Workbook
# Import Matplotlib
import matplotlib.pyplot as plt

# Get Adj Close Price for given Tickersymbol and store values in a dataframe
df = retrieve_stock_data("GOOG")

# Plot Adj. Close Price
fig = df.plot(figsize=(12, 8))

# Get Figure Object
fig = fig.get_figure()

# Add a new sheet
sht = wb.sheets.add("Plot Stockprice")

# Add Picture to Excel
sht.pictures.add(fig, name="Stockprice", update=True)


# ## 3.3 Connect Workbook to API's
# Install package into the instance of Python that launched the notebook
get_ipython().system("pip install requests --quiet")

# Imports
import requests
import urllib.request

# ### 3.3.1 Useless Fact
# Get Random Fact via API
def get_useless_fact():
    URL = "https://uselessfacts.jsph.pl/random.json?language=en"
    useless_fact = requests.get(URL).json()["text"]
    return useless_fact


# Add New Worksheet
sht = wb.sheets.add("API")


# Save Random Fact in Worksheet
sht.range("A1").value = get_useless_fact()


# ### 3.3.1 Insert Image to Workbook (DOG API)
# Image location/name
filename = Path("dog.jpg")

# Retrieve & Save Dog Image
def get_dog_image(filename):
    API = "https://dog.ceo/api/breeds/image/random"  # API Link
    URL = requests.get(API).json()["message"]  # Get URL of dog image
    dog_image = urllib.request.urlretrieve(URL, filename)  # Download dog image
    return None


# Get a new dog iamge
get_dog_image(filename)

# Insert image in Excel
sht.pictures.add(
    filename.absolute(),  # Path to Image
    name="Dog",  # Name requiered to update the image
    update=True,
)  # Updates the image


# ## 3.4 Generate Automated Stock Reports
# Import quanstats library
import quantstats as qs

# Specify excel file location (works on Mac & Windows)
filename = Path("stock_report/stock_report.xlsx")

# Open Workbook
wb = xw.Book(filename)

# Select Sheet
sht = wb.sheets["Report"]


def generate_stock_report_as_pdf():

    # --- EXCEL DATA ---
    # Get the ticker symbol from Worksheet
    ticker = sht.range("B3").value

    ####################################################################

    # --- DO SOMETHING WITH THE EXCEL DATA IN PYTHON ---
    # Fetch the daily returns for a stock
    stock = qs.utils.download_returns(ticker)

    # Image location
    snapshot_path = Path("snapshot.png")
    heatmap_path = Path("return_heatmap.png")

    # Title of Snapshot Report
    title = f"{ticker} Performance"

    # Generate snapshot report of any given ticker symbol
    stock_snapshot = qs.plots.snapshot(
        stock, title=title, savefig=snapshot_path, show=False
    )

    # Generate montly return heatmap
    stock_heatmap = qs.plots.monthly_heatmap(stock, savefig=heatmap_path, show=False)

    ######################################################################

    # --- RETURN RESULT BACK TO EXCEL ---
    # Add Picutures to Excel
    sht.pictures.add(snapshot_path.absolute(), name="snapshot", update=True)
    sht.pictures.add(heatmap_path.absolute(), name="heatmap", update=True)

    # Save PDF Report
    wb.to_pdf("stockreport")

    return None


# Generate Stock Report and save as PDF
generate_stock_report_as_pdf()