Asset Price Case Questions and Visualization

xlsx

School

Loyola University Chicago *

*We aren’t endorsed by this school

Course

MISC

Subject

Statistics

Date

Feb 20, 2024

Type

xlsx

Pages

Uploaded by DukeTankLoris35

Number 1 2 3 4 5 6 7 8 9 10 11

Question Group member 1 - Full name Group member 2 - Full name Group member 3 - Full name Who is the project manager for the group? Who is the spreadsheet manager for the group? Does the group certify that this workbook contains your group's work and not that of someone else? (Answer Yes if True) What does each row in the dataset represent? What is the unique identifier in the data? What is the key outcome variable in each row of the data? In one sentence, describe the main business "problem" that you are solving. There are three worksheets after this one that work with the data. In a text box below, write a paragraph about your key takeaways from S3 and S4: If you were to have a portfolio of these assets, how would you allocate your money across the four of them? Think about returns, standard deviation, and correlation. Is there a smart investment strategy here?

Answer

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Collecting and Organizing the Data For this case, you are going to collect stock data on SPY, TSLA, and GM. 1. Go to https://www.marketwatch.com to collect financial data. MarketWatch is one of many sources of fina 2. Click on the search button (magnifying glass) in the upper right corner of the MarketWatch homepage, and S&P 500 ETF Trust. On the SPY page, click on “Historical Quotes.” Change the date range to Start Date Date 10 Download Data to download the CSV file. 3. Click again on the MarketWatch search button, and enter “TSLA” into the search bar. Select the option “TSL the date range to Start Date 10/3/2022 and End Date of 10/2/2023. Click on Update Results. Click on Downlo 4. Repeat step 3 searching for “GM”, which is General Motors Co and "BTCUSD" which is the price of Bitcoin in 5. Add the four csv files into the Asset Price Case Questions and Visualization workbook (.xlsx). The first works worksheet should be GM data, labeled “GM,” second TSLA data, labeled “TSLA,” and fourth the Bitcoin data in 6. In this step, you are going to subset and organize the data. Create a fifth worksheet after BTCUSD that com should have Date in the first column and then the close price for each of the three stocks in the same order a and TSLA too. 7. Merge the BTCUSD data onto the Working Data by using a VLOOKUP. The identifier is the date and you pull an exact match. Use the header "BTC Close." There will be 5 columns of data in Working Data when you're do 8. Sort the data on Date from Oldest to Newest. 9. The Working Data worksheet is your product from this worksheet. But to complete this page, insert a text b Why did we not use all of the closing BTCUSD prices and how did the VLOOKUP help us combine the stock dat

ancial securities prices on the internet. d enter “SPY” into the search bar. Select the option “SPY” for the SPDR 0/3/2022 and End Date of 10/2/2023. Click on Update Results. Click on LA” for Tesla Inc. On the TSLA page, click on “Historical Quotes.” Change oad Data to download the CSV file. n dollars. Download the CSV files for the same period of time. sheet should be the SPY data, with the worksheet labeled “SPY.” Second n "BTCUSD." mbines and sorts the data. This worksheet (labeled “Working Data”) as the worksheets. Label the SPY Close as “SPY Close” and so on for GM l the data from the BTCUSD worksheet. Make sure to use the option for one. box below and answer this question: ta with the crypto data?

Visualizing the Data (S2 must be completed before working on this sheet. Both charts below should be referencing Working Data. 1. Create a line chart below that shows the four data series of SPY Close, GM Close, TSLA Close, and BTC Close the chart to label the series. Make sure the x axis provides a clear labeling of the date range. 2. Below the chart, insert a text box explaining why this is not a very good chart. 3. In this step, you are going to adjust the data. Back in the working data, add a 6th column labeled “SPY Close Close on the first date to the value of 100. Your calculation will look like “=(B2/B2)*100." This will convert the normalize the rest of the values by B2 as well. For instance, the second date will be "=(B3/B2)*100." Repeat t Close 100", “TSLA Close 100” and "BTC Close 100." 4. Create a second line chart to the right of your first chart that shows the four data series of SPY Close 100, G the chart a title and a legend at the bottom of the chart to label the series. Make sure the x axis provides a cle 5. Below the chart, insert a text box explaining answering the following questions: - Why this is a better chart? - Which investments (GM, TSLA, or BTC) outperformed SPY over this period? - Why is that interesting?

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

.) e. Give the chart a title and a legend at the bottom of e 100.” In cell F2, enter a formula to convert the SPY e value on the first date to 100. You then want to this process in the next three columns to create “GM GM Close 100, TSLA Close 100, and BTC Close 100. Give ear labeling of the date range.

SPY GM TSLA BTC Min Close Value Min Date Max Close Value Max Date Return from first date to last Standard deviation SPY-GM Correlation SPY-TSLA Correlation SPY-BTC Correlation GM-TSLA Correlation GM-BTC Correlation TSLA-BTC Correlation Statistics (S2 must be completed before working on this sheet. All functions below should be referencing Working Data.) In this sheet, you will fill out the two tables below and then interpret your findings. 1. Fill in the Min Close Values and the Max Close values using function references to the data. This should be the o 2. For the Min Dates and Max Dates, use the XLOOKUP function to find the dates associated with each of the valu value of Date (the value being searched) is to the left of Close (the lookup value). For XLOOKUP, the first input is t (e.g., Min Close Value for SPY), the second input is the column in the working data where the close value occurs (e column where the date can be found (Date). You do not need to do use any optional inputs. The output of the XLO sense at first. Change the format of these cells to Short Date and you will see the correct date. 3. To calculate the returns, use the following formula: (Final Close – Initial Close)/Initial Close. This is based on the be in percentage format with two decimal places. 4. For the standard deviations and correlations, use the full column of data for “Close” for each of the assets. To c function. Show 3 decimal places for the standard deviations and correlations. 5. Use a Pivot Table to calculate the monthly average close price for SPY and BTCUSD. Then calculate the monthly rate of return to the annual rate of return (the ones you calculated from Q3). 6. Insert a text box to the right of the tables that answers these questions. Separate your answers for each questio - Which of the assets reached it's minimum value first and why is this interesting? - Which of the assets reached it's maximum value first and why is this interesting? - Which of the assets had the highest return and why is this interesting? - Which of the assets had the highest standard deviation and why is this interesting? - Which of the assets had the lowest standard deviation and why is this interesting? - Which of the asset pairs had the highest correlation and why is this interesting? - Which of the asset pairs ad the lowest correlation and why is this interesting?

original values, not the "100" versions. ues. You are using XLOOKUP because the the relevant cell in the statistics worksheet e.g., Close SPY), and the third input is the OOPUP will be a number that will not make e first date and the last date. Returns should calculate standard deviation, use the STDEV.S y rate of return. Then compare the monthly on.

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version