Data Analytics in Finance – lamontblack.com

This is module 7 of 10 for the Business Analytics course.

Readings

Self-Directed Learning

Search the internet for articles, other resources, and examples of how business analytics is used in finance. See what you can learn from what others are saying. Finance is an important application of analytics for studying investments, corporate finance, personal finance, and lending.

Online Discussion of Module Topic

Write down your thoughts and experiences while you’re learning. For this module, this should include writing about your experience of learning about the use of data analytics in finance. Here are some prompts to get you started for writing about the module topic:

Provide links to any useful and relevant resources about data analytics in finance that you can find on the internet.
Do you have any experience in using analytics in finance? Maybe in a course, a job, or your own investments?
How do “fundamental analysis” and “technical analysis” in investing use data analytics?
What is “quantitative” or “algorithmic” trading and how is this an application of analytics?
What are the pros and cons of financial trading becoming more automated and high speed? What is a “flash crash”?
How might companies analyze their own financial performance to make business decisions?
Why are “cash flow projections” so important? How does this relate to predictive analytics?
What is “roboadvising” and how does it relate to data analytics? Have you ever used one?
How do you feel about the use of FICO scores by lenders for making personal loans?
Can you find examples of online lenders who are using data analytics to make credit decisions?
What do you think is the future of data analytics in finance?

Online Discussion of Module Submission

For this module, the discussion of the submission should include writing about your experience of working on the submission. You should write about your experience working with the specific data, but also try to think about how this approach could be applied to other datasets. Here are some prompts to get you started for writing about it:

Why is financial data so readily available?
How can statistics be useful for summarizing financial data?
How can data visualization be used to highlight key investment opportunities or risks?
How do you feel about the potential of this type of analysis for making investment decisions?
What analytics might you use when making your own investment decisions?
What did you conclude about how a portfolio of investments could be analyzed?
Why is the final step of drawing investment insights so important?

Module Submission

This assignment can be done in groups of up to 3 people or it can be done independently. You will be submitting your work under the Module 7 Submission Folder. You will need to join a group in D2L in order to view the folder. The submission should be done by pasting a link to your Excel workbook in OneDrive.

Here are the steps for working on the module submission:

Step 1. Go to https://www.marketwatch.com to collect financial data. MarketWatch is one of many sources of financial securities prices on the internet.

Step 2. Click on the search button (magnifying glass) in the upper right corner of the MarketWatch homepage, and enter “SPY” into the search bar. Select the option “SPY” for the SPDR S&P 500 ETF Trust. On the SPY page, click on “Historical Quotes.” Change the date range to Start Date 10/19/2020 and End Date 10/18/2021. Click on Update Results. Click on Download Data to download the CSV file.

Step 3. Click again on the MarketWatch search button, and enter “TSLA” into the search bar. Select the option “TSLA” for Tesla Inc. On the TSLA page, click on “Historical Quotes.” Change the date range to Start Date 10/19/2020 and End Date 10/18/2021. Click on Update Results. Click on Download Data to download the CSV file.

Step 4. Repeat step 3 searching for “GM”, which is General Motors Co. Download the CSV file for the same period of time.

Step 5. Combine the three csv files into a single Excel workbook (.xlsx). The first worksheet should be the SPY data, with the worksheet labeled “SPY.” Second worksheet should be TSLA data, labeled “TSLA,” and third worksheet should be GM data, labeled “GM.”

Step 6. Create a fourth worksheet that combines and sorts the data. This worksheet (labeled “Working Data”) should have Date in the first column and then the open and close price for each of the three stocks in the same order as the worksheets. Label the SPY Open and Close as “SPY Open” and “SPY Close” and so on for the other stocks too. There will be 7 columns of data. Sort the data on Date from Oldest to Newest.

Step 7. Visualize the data. Create a line chart that shows the three data series of SPY Close, TSLA Close, and GM Close. Give the chart a title and a legend at the bottom of the chart. Add a fifth worksheet labeled “Charts” and move this chart to the Charts worksheet so that it is not on your working data worksheet. Below the chart, write a statement about why this is not a very good chart.

Step 8. Adjust the data. Back in the fourth worksheet, add an 8th column labeled “SPY Close 100.” In cell H2, enter a formula to convert the SPY Close on 10/19/20 to the value of 100. Your calculation will look like “=C2-xxx” and you have to figure out the xxx. Once you have correctly calculated 100, double click on the lower right corner of the cell to fill in the remainder of the column. Repeat this process in the next two columns to create “TSLA Close 100” and “GM Close 100.”

Step 9. Visualize the data again. Step 7. Visualize the data. Create a line chart that shows the three data series of SPY Close, TSLA Close, and GM Close. Give the chart a title and a legend at the bottom of the chart. Move this chart to the Charts worksheet and, below the chart, write a statement about why this is a better chart.

Step 10. Statistics. Add a sixth worksheet labeled Statistics. Your statistics page should have the following structure:

Excel screenshot

Cells B2 through D2 should use the MIN function to show the minimum closing values for SPY, TSLA, and GM (the originals, not the “100” version). Cells B5 through D5 should use the MAX function to show the maximum closing values for SPY, TSLA, and GM (the originals, not the “100” version). For the Min Date and Max Date, use the XLOOKUP function to find the dates associated with each of the values. You are using XLOOKUP because the value of Date (the value being searched) is to the left of Close (the lookup value). For XLOOKUP, the first input is the relevant cell in the statistics worksheet (e.g., Min Close Value for SPY), the second input is the column in the working data where the close value occurs (e.g., Close SPY), and the third input is the column where the date can be found (Date). You do not need to do use any optional inputs. The output of the XLOOPUP will be a number that will not make sense at first. Change the format of these cells to Short Date and you will see the correct date.

To calculate the min to max returns for SPY and TSLA, use the following formula: (max – min)/min*100.

For the standard deviations and correlations, use the full column of data for “Close” for each of the three stocks. To calculate standard deviation, use the STDEV.P function (we are studying population, not a sample).

Please convert the returns, standard deviations, and correlations to show three decimal places.

Step 11. Extra analysis. If your group would like to do any of your own analysis on the data, please add this on worksheets following the ones explained above. You may want to do additional analysis on daily returns or make a scatter plot or even add some information about the fundamentals for TSLA and GM.

Step 12. Concluding writeup. Copy a single chart from your analysis, either the second chart on the Charts worksheet or a chart from your analysis. Under this chart, provide a brief concluding write-up about what you found most interesting about this analysis. What do you think are potential takeaways for investors considering these three securities?