Data Analytics Internship Assignment
Uploaded by
Manav KawaleData Analytics Internship Assignment
Uploaded by
Manav KawaleCommon questions
Powered by AIEfficient use of VLOOKUP in large datasets can be achieved by incorporating the ARRAYFORMULA feature, which ensures that the formula is written once and applied to the entire column, minimizing redundancy and manual input. It is also important to use IF CONDITIONS to limit the application of VLOOKUP only to rows where data exists, thereby improving performance and avoiding unnecessary computations. This method enhances processing efficiency, especially when dealing with extensive datasets frequently updated or queried .
The QUERY function in Google Sheets allows users to filter and sort data through SQL-like commands. A common method involves selecting rows based on specific conditions, such as non-blank PE Ratios and PE Ratios below a given threshold (< 100), then sorting these results by descending values of a specific column, such as market cap. This technique provides a powerful tool for extracting tailored datasets from large tables and for organizing data according to user-defined criteria .
Conditional formatting can be applied to financial data by using color scales to visually differentiate numerical ranges, such as market cap values, and by setting rules to highlight specific data points, such as PE Ratios or 'Good Stock' status. For instance, applying different colors to indicate the level of a PE Ratio or using a color gradient to represent market cap size can help quickly identify trends and outliers. This technique allows for a more intuitive analysis of data, supporting easier decision-making processes .
Using multiple IF conditions to determine stock status ensures accuracy in classifying stocks based on multiple criteria, such as PE Ratio and bear mode statuses. This methodology facilitates nuanced analysis for stock evaluation by accommodating various financial indicators rather than relying on a single metric. By combining multiple logical tests within IF statements, one can classify stocks into complex categories like 'Good Stock,' which is crucial for making informed investment decisions and for providing detailed insights in analytical reporting .
The 'Bear Mode' concept is useful as it signifies significant price changes from key periods—specifically, 52-week highs and lows. 'Bear Mode 1' indicates caution when prices drop significantly from their highs, and 'Bear Mode 2' indicates potential interest if prices rise from their lows, both suggesting shifts in market sentiment or volatility. This assessment helps investors recognize potential buying or selling opportunities based on a stock's volatility and market movements .
To create a pivot table report analyzing market cap data by industry, first collect and organize data where the stock status is classified as 'Good Stock'. Then, format this into a structured sheet and apply conditional formatting. In creating the pivot table, include fields for the total count of companies per industry, the average market cap, and the sum of the market cap. Proper formatting, headings, slicers, and color scales are added to enhance the readability and professionalism of the report .
Advanced Google Sheets formulas such as IF Conditions, QUERY Function, VLOOKUP with ARRAYFORMULA, and Pivot Tables are used to manipulate a financial dataset. These tools allow for filtering data (e.g., selecting PE Ratios that are not blank and less than 100), merging datasets from different sources (e.g., adding Industry and NSE/BSE Code columns with VLOOKUP and ARRAYFORMULA), analytical calculations (e.g., determining 'Bear Mode' status and 'Stock Status' based on conditions), and summarizing data into reports (e.g., creating pivot tables for industry-wise analytics).
Dynamic dashboards in Google Data Studio should be configured by linking them directly to Google Sheets as the data source. This can be achieved by setting up data connectors that automatically refresh and reflect updates made in the sheets. It's crucial to ensure that data fields and metrics align between the Sheets and Data Studio configurations so that any formulas or calculated fields carry over seamlessly. Adding filters and slicers allows users to interactively drill down into specific data subsets, maintaining a real-time and user-friendly interface .
Calculating total and average market caps per industry provides a comprehensive view of a sector's economic scale and the relative size of its constituents. Total values give an overall representation of the industry's market valuation, while averages offer insights into typical company sizing within the sector. This dual perspective aids analysts in understanding the distribution of market capitalization and identifying trends or anomalies in the concentration of industry wealth .
Best practices involve compiling a well-structured dataset within Google Sheets using advanced functions for accurate data manipulation. This data should be organized into meaningful categories, such as stock status, with appropriate labels and conditional formatting. When designing reports in Google Data Studio, maintaining consistency with visually distinct templates, clear labels, interactive filters, and updated data links ensures clarity and engagement. Properly integrating Sheets data with dynamic dashboards helps in providing a comprehensive, interactive analytical report that meets professional standards .