<jupyter_start><jupyter_text>Web Application for an ETF AnalyzerIn this Project, we build a financial database and web application using SQL, Python, and the Voilà library to analyze the performance of a hypothetical fintech ETF. This notebook is used for the analysis of a fintech ETF that consists of four stocks: GOST, GS, PYPL, and SQ. Each stock has its own table in the `etf.db` database.We analyze the daily returns of the ETF stocks both individually and as a whole. Then deploy the visualizations to a web application by using the Voilà library. The detailed instructions are divided into the following parts:* Analyze a single asset in the ETF* Optimize data access with Advanced SQL queries* Analyze the ETF portfolio* Deploy the notebook as a web application  Imports of the required libraries, initiation of the SQLite database, population of the database with records from the `etf.db` seed file that is included in the repository, creates the database engine, and confirms that data tables that it now contains.<jupyter_code># Importing the required libraries and dependencies
import numpy as np
import pandas as pd
import hvplot.pandas
import sqlalchemy
import datetime
from datetime import date, datetime
from sqlalchemy import inspect

# Create a temporary SQLite database and populate the database with content from the etf.db seed file
database_connection_string = 'sqlite:///etf.db'

# Create an engine to interact with the SQLite database
engine = sqlalchemy.create_engine(database_connection_string)

# Confirm that table names contained in the SQLite database.
print("Tables names for data from stocks Green Dot Inc, Goldman Scahs Group Inc, Paypal Inc, and Square Inc.")
inspect(engine).get_table_names()<jupyter_output>Tables names for data from stocks Green Dot Inc, Goldman Scahs Group Inc, Paypal Inc, and Square Inc.<jupyter_text>Analyze a single asset in the FinTech ETF  Step 1: Write a SQL `SELECT` statement by using an f-string that reads all the PYPL data from the database. Using the SQL `SELECT` statement, we execute a query that reads the PYPL data from the database into a Pandas DataFrame.<jupyter_code># Write a SQL query to SELECT all of the data from the PYPL table
query = """
SELECT * from PYPL
"""

# Use the query to read the PYPL data into a Pandas DataFrame and set index to "time"
fmt='%Y%m%d %H:%M:%S'
pypl_dataframe = pd.read_sql_query(query, con=engine, parse_dates={'time':fmt})
pypl_dataframe=pypl_dataframe.set_index('time')

print("\033[1m  Table with Paypal Inc. prices, volume and daily returns")
pypl_dataframe<jupyter_output>[1m  Table with Paypal Inc. prices, volume and daily returns<jupyter_text>Step 2: Use the `head` and `tail` functions to review the first five and the last five rows of the DataFrame. We save the beginning and end dates that are available from this dataset, since we’ll use this information to complete the analysis.<jupyter_code># Print Beggining and End date of the Period.
print(f"Beggining of period: {pypl_dataframe.index[0]}")
print(f"End of period      : {pypl_dataframe.index[-1]}")

#Calculate lenght of period to calculate actual annualized return later
period=(pypl_dataframe.index[-1] - pypl_dataframe.index[0])
period_in_years=period.days/365.25
print(f"Period in days is: {period.days}, which are {period_in_years:,.2f} years \n\n")

# View the first 5 rows of the DataFrame.
print("\033[1m  Firsts and lasts columns of the pypl_dataframe, with data of Paypal stock. \n")
display(pypl_dataframe.head())
display(pypl_dataframe.tail())
pypl_dataframe["Daily Returns %"]=pypl_dataframe['daily_returns']*100<jupyter_output><empty_output><jupyter_text>Step 3: Interactive visualization for the PYPL daily returns using hvPlot.<jupyter_code># Create an interactive visualization with hvplot to plot the daily returns for PYPL.
pypl_dataframe.hvplot(
    title="PYPL Daily Returns (%)"
    ,y='Daily Returns %'
    ,xlabel= 'Date'
    ,ylabel='Returns (%)'
    ,width=800
).opts(
    color='blue')<jupyter_output><empty_output><jupyter_text>Step 4: Interactive visualization for the PYPL cumulative returns.<jupyter_code># Representing the cummulative investment
growth_of_1usd_investment=(1+pypl_dataframe["daily_returns"]).cumprod()

#Transforming a series to a dataframe and renaming columns
growth_of_1usd_investment=growth_of_1usd_investment.to_frame().rename(columns={'daily_returns':'Growth of 1 USD Investment'})
print("\033[1m Table: Evolution of a $1 initial investment on Dec 15th 2016 on the ETF.")

display(growth_of_1usd_investment)

# Create an interactive visaulization with hvplot to plot the cumulative returns for PYPL.
growth_of_1usd_investment.hvplot(
    title="Paypal Holdings Inc -- Growth of 1 USD Initial Investment -- Period Dec-16-2016 to Dec 4th 2020"
    ,ylabel="Initial Investment \n plus Cumulative Return"
    ,xlabel= "Date"
    ,width=900

)<jupyter_output>[1m Table: Evolution of a $1 initial investment on Dec 15th 2016 on the ETF.<jupyter_text>Optimize the SQL QueriesFor this part, we continue to analyze a single asset (PYPL) from the ETF. We use SQL queries to optimize the efficiency of accessing data from the database.  Step 1: Access the closing prices for PYPL that are greater than 200 by completing the following steps:    - Write a SQL `SELECT` statement to select the dates where the PYPL closing price was higher than 200.0.    - Using the SQL statement, read the data from the database into a Pandas DataFrame, and then review the resulting DataFrame.    - Select the “time” and “close” columns for those dates where the closing price was higher than 200.0.<jupyter_code># Write a SQL SELECT statement to select the time column 
# where the PYPL closing price was higher than 200.0.
query = """
SELECT time 
FROM PYPL
WHERE close > 200
"""

# Using the query, read the data from the database into a Pandas DataFrame, and convert date strings to date
fmt='%Y%m%d %H:%M:%S'
pypl_dates_higher_than_200 = pd.read_sql_query(query, engine, parse_dates={'time':fmt})

# Review the resulting DataFrame
print("\033[1m Older dates when close price of Paypal is higher than $200. Data comes from SQL database:")
display(pypl_dates_higher_than_200.head())

# Select those dates from the pypl dataset
pypl_higher_than_200 = pypl_dataframe.loc[pypl_dates_higher_than_200['time'],'close'].to_frame()
print("\033[1m  Older dates and close price of Paypal when higher than $200 in pandas dataframe")
display(pypl_higher_than_200.head())<jupyter_output>[1m Older dates when close price of Paypal is higher than $200. Data comes from SQL database:<jupyter_text>Step 2: Find the top 10 daily returns for PYPL by completing the following steps:    -  Write a SQL statement to find the top 10 PYPL daily returns. Make sure to do the following:        * Use `SELECT` to select only the “time” and “daily_returns” columns.        * Use `ORDER` to sort the results in descending order by the “daily_returns” column.        * Use `LIMIT` to limit the results to the top 10 daily return values.    - Using the SQL statement, read the data from the database into a Pandas DataFrame, and then review the resulting DataFrame.<jupyter_code># Write a SQL SELECT statement to select the time and daily_returns columns
# Sort the results in descending order and return only the top 10 return values
query = """
SELECT time, daily_returns
FROM PYPL
ORDER by daily_returns  desc
LIMIT 10
"""

# Using the query, read the data from the database into a Pandas DataFrame
# Counting is useful to visualize amount of data, so index is not change to time
fmt= '%Y%m%d %H:%M:%S'
pypl_top_10_returns = pd.read_sql_query(query, engine, parse_dates={'time':fmt})

pypl_top_10_returns['daily_returns']=pypl_top_10_returns['daily_returns']*100

print("\033[1m Table with the dates when the top 10 larger daily returns of Paypal Inc. occured:")
display(pypl_top_10_returns[['time']])
    
# Review the resulting DataFrame
print ("\n")
print("\033[1m Table with the top 10 larger daily returns of Paypal stock in percentages (%):")
display(round(pypl_top_10_returns,2))<jupyter_output>[1m Table with the dates when the top 10 larger daily returns of Paypal Inc. occured:<jupyter_text>Analyze the Fintech ETF PortfolioFor this part, we build the entire ETF portfolio and then evaluate its performance. To do so, we build the ETF portfolio by using SQL joins to combine all the data for each asset.  Step 1: Write a SQL query to join each table in the portfolio into a single DataFrame. To do so, complete the following steps:    - Use a SQL inner join to join each table on the “time” column. Access the “time” column in the `GDOT` table via the `GDOT.time` syntax. Access the “time” columns from the other tables via similar syntax.    - Using the SQL query, read the data from the database into a Pandas DataFrame. Review the resulting DataFrame.<jupyter_code># Create a SQL query to join each table in the portfolio into a single DataFrame 
# Use the time column from each table as the basis for the join
query = """
SELECT  *
FROM GDOT, GS, PYPL, SQ
WHERE GDOT.time=GS.time
AND PYPL.time=SQ.time
AND GDOT.time=PYPL.time
"""
#['GDOT', 'GS', 'PYPL', 'SQ']
# Using the query, read the data from the database into a Pandas DataFrame
frm='%Y%m%d %H:%M:%S'
etf_portfolio = pd.read_sql_query(query, engine, parse_dates={'time':frm})

# Review the resulting DataFrame
print('\n')
print("\033[1m Join Tables from ['GDOT', 'GS', 'PYPL', 'SQ'] on dates")
display(etf_portfolio.head())<jupyter_output>[1m Join Tables from ['GDOT', 'GS', 'PYPL', 'SQ'] on dates<jupyter_text>Step 2: Create a DataFrame that averages the “daily_returns” columns for all four assets. Review the resulting DataFrame. We assume that this ETF contains equally weighted returns, and average the daily returns for all assets to get the average returns of the portfolio. We use the average returns of the portfolio to calculate the annualized returns and the cumulative returns. For the calculation to get the average daily returns for the portfolio, we use the following code: ```python etf_portfolio_returns = etf_portfolio['daily_returns'].mean(axis=1) ```<jupyter_code># Create a DataFrame that averages the “daily_returns” columns for all four assets. Review the resulting DataFrame.
etf_portfolio_returns = etf_portfolio['daily_returns'].mean(axis=1)
print('\033[1mETF Portfolio Returns (%)')
display(round((etf_portfolio_returns*100),2).head(10))
# As a second view, we create a DataFrame that displays the value of the “daily_returns” for all four assets only, and assign an index.
# Use the time column from each table as the basis for the join
query2 = """
SELECT  GDOT.time, GDOT.daily_returns as 'GDOT.daily_returns',
   GS.daily_returns as 'GS.daily_returns',
   PYPL.daily_returns as 'PYPL.daily_returns',
   SQ.daily_returns as 'SQ.daily_returns'
FROM GDOT, GS, PYPL, SQ
WHERE GDOT.time=GS.time
AND PYPL.time=SQ.time
AND GDOT.time=PYPL.time
"""

#['GDOT', 'GS', 'PYPL', 'SQ']
# Using the query, read the data from the database into a Pandas DataFrame
fmt='%Y%m%d %H:%M:%S'
etf_portfolio2 = pd.read_sql_query(
        query2
        , engine
        ,parse_dates={'time':fmt}
)
etf_portfolio2=etf_portfolio2.set_index("time") 
print('\n')
print('\033[1m                         Daily individual returns in time (%)')
display(round((etf_portfolio2*100),2))


# We repeat the calculation of average daily returns using this table
etf_portfolio_returns = etf_portfolio2.mean(axis=1)

# Review the resulting DataFrame
display("ETF Returns (%)")
display(round(etf_portfolio_returns*100,2))<jupyter_output>[1m                         Daily individual returns in time (%)<jupyter_text>Step 3: Use the average daily returns in the etf_portfolio_returns DataFrame to calculate the annualized returns for the portfolio. Display the annualized return value of the ETF portfolio.To calculate the expected annualized returns, we multiply the mean of the `etf_portfolio_returns` values by 252.To convert the decimal values to percentages, we multiply the results by 100 before printing or plotting the values.<jupyter_code># Use the average daily returns provided by the etf_portfolio_returns DataFrame 
# to calculate the annualized return for the portfolio. 
annualized_etf_portfolio_returns = etf_portfolio_returns.mean()*252

print(f"The expected annualized return, calculated using daily average return in the period, times 252 trading days is: {annualized_etf_portfolio_returns*100:,.2f}% ")<jupyter_output>The expected annualized return, calculated using daily average return in the period, times 252 trading days is: 43.83%<jupyter_text>Step 4: We use the average daily returns in the `etf_portfolio_returns` DataFrame to calculate the cumulative returns of the ETF portfolio.<jupyter_code># Use the average daily returns provided by the etf_portfolio_returns DataFrame 
# to calculate the cumulative returns
# This is the growth of 1[USD] initial investment
etf_cumulative_returns = (1+etf_portfolio_returns).cumprod()
# ROI
etf_cumulative_return_above_initial_investment=etf_cumulative_returns[len(etf_cumulative_returns)-1]-1
growth_of_1usd_initial_investment=etf_cumulative_return_above_initial_investment+1

# Display the final cumulative return value
print(f"The cumulative return of the investment in the full period, above the initial investment (no-annualized) is of {etf_cumulative_return_above_initial_investment*100:,.2f}%")
print(f"The growth of $1.00 initial investment in the full period is ${growth_of_1usd_initial_investment:.2f} ")
# Adjusting columns names for proper graph variables
etf_cumulative_returns_df = pd.DataFrame(etf_cumulative_returns, columns=['Growth of 1[USD] Initial Investment'])

etf_cumulative_returns_df.tail()<jupyter_output><empty_output><jupyter_text>Step 5: Using hvPlot, we create an interactive line plot that visualizes the cumulative return values of the ETF portfolio. Reflect the “time” column of the DataFrame on the x-axis.<jupyter_code># Using hvplot, create an interactive line plot that visualizes the ETF portfolios cumulative return values.
etf_cumulative_returns_df.hvplot(
    title="ETF - Equally Weighted FinTech Stocks (GDOT, GS, PYPL, SQ) Growth of 1 USD Initial Investment -- Dec-16-2016 to Dec 4th 2020"
    , ylabel="Cumulative Investment [$]"
    ,xlabel= "Date"
    ,width=900
)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.07748049052396878, 'fraction_numerical': 0.018115942028985508, 'mean_word_length': 5.328483245149912, 'pattern_counts': {'":': 0, '<': 41, '<?xml version=': 0, '>': 42, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '12', 'dir': 'jupyter-structured-clean-dedup', 'id': '594686', 'license': 'MIT', 'n_tokens_mistral': 4517, 'n_tokens_neox': 4176, 'n_words': 1763, 'path': 'etf_analyzer.ipynb', 'repo_name': 'paocarvajal1912/DB_WebApp_using_SQL_Voila', 'size': '14352'}