Excel Charts, Graphs, Tableau, Power BI
Last updated:3/26/23 Page 1 MIS-3210: Case Study#2 – Stock Performance Analysis Applies to: • • Unit 3 (Data Visualization) Weeks 6/7/8/9/10 (Excel Charts & Graphs, Tableau, Power BI) Specific Skills Reinforced & Assessed: General Research • • Using web sources to research and download data for use in analytics. Organizing, consolidating, and managing Excel data for use by different analytical tools. Excel Charts & Graphs • • • Organizing and preparing data in Excel format for production of charts & graphs. Production of selected data visualizations using Excel’s built-in chart and graph features. Using data visualizations produced in Excel in other communication tools (presentations, documents, etc.) Tableau • • • • Organizing and preparing data in Excel format for use as a Tableau data source. Connecting to a multi-tab Excel workbook as a Tableau data source. Use of Tableau to produce selected data visualizations. Using data visualizations produced in Tableau in other communication tools (presentations, documents, etc.) Microsoft Power BI • • • • Organizing and preparing data in Excel format for use as a Power BI data source. Importing a multi-tab Excel workbook into Power BI as a data source. Use of Power BI to produce selected data visualizations. Using data visualizations produced in Power BI in other communication tools (presentations, documents, etc.) Overview: In this case study, you are working as an entry-level stock analyst for a national wealth-management business and are going through their new analyst training program. Part of this training program is learning how to conduct research and analysis of various stocks within a given business sector (technology, distribution, finance, logistics, etc.) In this activity, you will conduct analysis of stock trading data for three (3) publicly owned businesses that you will select (or two stocks and an index – please refer to slides for further details). After selecting three companies to research, you will use publicly available online websites to download the most Last updated:3/26/23 Page 2 recent 5 years (or the SAME date range) of historical trading data on all three selected stocks. The downloaded data should be saved in Excel format then organized and consolidated for use as source data in the three data visualization tools covered in this unit of MIS-3210. Organize and align your data columns to ensure they refer to the same MMYY in each row – see step 4 example] Using the prepared Excel data source workbooks, you will produce a set of specific data visualizations using Microsoft Excel, Tableau, and Microsoft Power BI. You will then develop a presentation slide set of your results using the visualizations you developed with these three tools mentioning any insights you have gained from your analysis Gathering Stock Data: Step 1: Identify an industry sector to study Select an industry sector that you find interesting to explore in terms of historical stock price behavior. Examples of sectors might include banking and finance, technology, or consumer goods. Step 2: Select three specific stocks within that sector to analyze Within that sector, identify three (3) publicly traded businesses that you’d like to study and compare. For example, in the technology sector, we have selected Google, Microsoft, and IBM. For your analysis, please choose different stocks. For each of your selected stocks, note its related stock symbol for research purposes (example: TWTR = Twitter). One of the stocks selected can also be an index stock relevant to your selected sector. These are identified by a ^ in front of the ticker symbol e.g ^DJI. Step 3: Using any appropriate public-facing web resource, download in Excel format past 5 years of historical trading data for all three of your selected stocks. Use closing monthly values for a recommended total of approximately 60 closing value data points (12 months * 5 years = 60 data points) for each of your three selected organizations. There are a variety of such public sources, but Yahoo Finance (https://finance.yahoo.com) is especially well-suited for this kind of research. Refer to the example video for how to use Yahoo Finance to get data. Step 4: Organize the downloaded data into a single Excel workbook that can be used as a primary data source for a variety of data visualizations and analyses (charts, graphs, pivot table & charts, etc.) Ideally, you will have four (4) columns of data that look like this: Share Price: IBM, GOOG, MSFT Mo-Yr 9/1/2017 10/1/2017 IBM 138.70 147.28 GOOG 47.96 50.83 MSFT 74.49 83.18 Note that this consolidated workbook will be your primary source data for the balance of the work in this case study activity and will be included in the collection of artifacts submitted. Thus, you should keep a back-up copy of this consolidated data as it will be used in multiple phases of this case study activity. Last updated:3/26/23 Page 3 Producing Data Visualizations: Using your downloaded and prepared source data, produce the following visualizations using the tools indicated: Visualization #1: 5-Year Share Priced Comparison, with trend-lines (Line Graph) (Tool = Excel) Last updated:3/26/23 Visualization #2: 5-Year Trading Volume (Scatter Plot) (Tool = Excel) Page 4 Last updated:3/26/23 Visualization #3: Trading Volume Comparison: 5-Year Summary (3-D Stacked Bar, Horizontal) (Tool = Excel, Pivot Chart) Trading Volume Comparison: 5-Year Summary 2022 2021 Sum of IBM 2020 Sum of GOOG 2019 Sum of MSFT 2018 2017 0 1E+10 2E+10 3E+10 Note for the Pivot Table: Group by year, summarized by individual stock ticker volume Page 5 Last updated:3/26/23 Page 6 Visualization #4: Share Price & Trading Volume Comparison (Dashboard) (Tool = Tableau) Notes: • • • • You will need to create a second copy of your source data (used in Excel above) that has been prepared as a data source in Tableau. For example, you should have separate tabs each for the share price and trading volume data. In addition, the first row of each tab should contain the column headings. To create this dashboard, you will need to produce the four visualizations as individual worksheets in the same Tableau workbook. Note that you will need to plot the AVERAGE share value in the upper chart. This will require adjusting it from the default of SUM. You do not need to mimic the exact colors shown above but do ensure that separate colors are applied to various separate elements as reflected in this example. Make sure you save your completed Tableau workbook as a “Tableau Packaged Workbook” which will ensure your prepared data is included with the workbook visualizations. Last updated:3/26/23 Visualization #5: Share Price and Trading Volume by Individual Stock ID (line & bar) (Tools = Produced using Power BI and exported to PowerPoint) Page 7 Last updated:3/26/23 Page 8 Notes: • • • To prepare the data for use in Power BI, you will need a separate copy of your 2-tab workbook with the ranges of data converted into an Excel Table. You should compare the two data categories (share price, trading volume) for a single stock (organization) on three separate tabs in your Power BI report workbook. Note that the share price needs to be AVERAGE and the trading volume needs to be SUM (total for the year). Use the built-in feature with Power BI to export your results to Microsoft PowerPoint (PPt) format. The exported PPt file will be part of your submitted work for this case study. Last updated:3/26/23 Page 9 Completing the activity: • Ensure you have produced all the required data visualizations described above and saved them in the formats indicated. This should include: o Excel workbook of primary data gathered and prepared for case study analysis. o Excel workbook that includes the data visualizations #1, #2, & #3, including the pivot table associated with visualization #3. o Tableau workbook (in “packaged” format) that includes the final dashboard for visualization #4 with individual related worksheet tabs. o Exported PowerPoint of results for visualization #5. • INSIGHTS & ANALYSIS – Add 1-2 slides to the exported PowerPoint with your insights and any recommended actions on your analysis. • Upload to the Case Study #2 assignment folder all the completed artifacts of your work: o Excel data source (original) o Excel visualizations (#1, #2, #3) o Tableau Packaged Workbook (#4) o PowerPoint file with Power BI results & insights (#5)
Collepals.com Plagiarism Free Papers
Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS
Why Hire Collepals.com writers to do your paper?
Quality- We are experienced and have access to ample research materials.
We write plagiarism Free Content
Confidential- We never share or sell your personal information to third parties.
Support-Chat with us today! We are always waiting to answer all your questions.
