Mentored Minds

Virtual Internship in Data Analytics

Didn't Receive Offer Letter?

Want to Know what is this Program About?

1.) Introduction

Assume there is a Finance Company who provides Stock Market Reports to their Clients

They are Looking for a Data Analytics Intern who can perform Automations, Data Manipulations & Reporting inside Google Sheets!

You will be trained by your Mentor at frequent periods of this Virtual Internship & then you will be performing Real-World Tasks

This Role is for Candidates who want to gain Practical Experience of Real-World Problems in Data Analytics Field Working Upon Customized Solutions

You would also be getting access to some Career Boosting Products in this Virtual Internship

2.) Instructions

• You have to perform all the Tasks inside your Personal Google Drive Account

• You have to perform all tasks as per requirement & create a Google Document by attaching screenshots & explanation of each task you are doing

• At the End of the Virtual Internship, You will fill the Certificate Request Form & attach the Google Document in PDF Format
  

3.) Training

Context Covered

Formulas Training

Google App Script Training

Reporting Training

Basics About Data Analytics

Task 1

Data Set

Make a Copy of the Above Data Set Google Sheet

Objectives

1.) Data Manipulation Tasks

Task 1 : First Create a New Sub Sheet & Fetch all the Data from the Sheet called "Data Set" where Data from the PE Ratio is not Blank & PE Ratio < 100 using Query Function and sort the data by Descending of Market Cap Amount.

Task 2 : Now Create 2 Columns of Industry & NSE/BSE Code & Fetch Data from the Second Sheet with Vlookup, Make sure the Vlookup Formula is used with "ARRAYFORMULA" meaning formula will be written only once but will be applicable throughout the column,

Also make sure the Formula of Vlookup will be applied only until when data is there in the column, handle this with IF CONDITIONS in that same formula.

Task 3 : Now Create 2 More Columns at the End with the Heading "Bear Mode 1" & "Bear Mode 2"

Bear Mode 1 Condition : Use IF Condition to print "Yes" if % Change from 52 Week High > 30 else "No"

Bear Mode Condition 2 : Use IF Condition to print "Yes" if % Change from 52 Week Low < 30 else "No"

Stock Status : Now Create a Column named "Stock Status" & then apply multiple IF Conditions to check if PE Ratio <65, Bear Mode 1 & 2 = "No" then Print "Good Stock" in the column else "".

Task 4 : Now find out the Highest value of the Market Cap & then categorize 60% and Above of the Highest Value as Large Cap Stocks, 40-60% as Medium Cap Stocks & 0-40% as Low Cap Stocks in a new Column at last naming it as Cap Size.

Task 5 : Now create a new sheet & fetch all Data where Stock Status = 'Good Stock' which will be your Final Organized Data Source so Create Proper Formatting to this Sheet like the Data Source Sheet & also add Appropriate Conditional Formatting Colour Scales.

Note : All the IF Conditions Formula should be applied Using "ARRAYFORMULA" which will be dynamically valid throughout the entire column for n number of rows we add in future!

Task #2

2.) Report Dashboarding

Task 1 : Connect the Data Source Sheet which you created above in Task 5 to Google Data Studio.

Task 2 : Now if specific requirements are given creating Reports is easy, so we keep upto you that how well you understand the Data Set & Provide with Good Analysis which will actually be meaningful & Also Attractive.

Note : The Report Dashboard should have the Following :

1. A Heading
   
2. Appropriate Filters
3. Scorecard Charts
4. Various Types of Graphical & Tabular Analysis which will help to analzye Data
   

Task - 3

3.) Google App Script Tasks

Sheet Link

Make a Copy of the Above Sheet, Go Inside App Script & Fetch all Data From Source to DB, Give Permissions to Script & Run it Successfully!

Task - 4

Consider this Scenario,

A Top Cricket Scoring App is hiring a Data Analyst Intern to Analyze some Cricket Data & provide Meaningful Information and Statistics from it.

You can use any of the Following :

1. Pivot Tables in Excel
2. Pivot Tables in Google Sheets
3. Power BI
4. Tableau
5. Google Data Studio
6. Any other tool

Instructions :

Each Data Set is a Google Sheet in View Access :

You can Download it as Excel or make a Copy of the Google Sheet itself to use it as a Data Source

Data Set 1 : T-20 Player Performance

Data Set

Question 1 : Which Players have a Career Span of More than 5 years but still have not Scored any Century in T-20 Internationals?

Question 2 : Which players have Strike Rate above 140 & Average above 30?

Question 3 : What is the average number of 4’s scored during a Century?

Data Set 2 : Matches Data 2004-2021

Data Set

Question 1 : How Many Test Matches are Played Every Year and Graphical Comparison of last 5 Years with % Growth or Declination?

Question 2 : What is the average run rate of Team India in ODIs and comparison of 2004-2010 vs 2010-2021

Question 3 : How Many Times has India Bowled out before 50 Overs in ODI while playing in First Innings Compared with Australia and England

Question 4 : Create a Report with a Slicer where whichever country we select, their Average T20, ODI Score is displayed and comparison from 1st Inning vs 2nd Innings

Data Set 3 : King Kohli 71 Centuries

Data Set

Question 1 : How many centuries did Virat scored Outside India as a Captain?

Question 2 : What is the average days Virat Takes to score a Century?

Question 3 : Which is the innings where He scored a Century lowest in batting order, with highest strike rate and as a Captain where India Won and he was the Man of the match playing Away and not against Afghanistan?

Question 4 : In which Cities did Virat Scored Century in a Losing Cause more than Once?

Question 5 : What will be the predicted next century score based upon the previous pattern.

11. Premium CV Templates

CV Template - 1

CV Template - 2

CV Template - 3

• Open the Link & Sign Up on Canva Account,
• Edit the CV Template & Download it as PDF,
• You have a Lifetime Free Access to these CVs.
  

Internship Certificate

To Earn a Certificate for this Internship and Use this Experience on your CV & Linkedin for Job or College Purpose & also to Get your Work Evaluated, You need to pay a Nominal Fee on the Button Below as we have already provided you access to a lot of Valuable things for your Career :

1.) Offer Letter from Mentored Minds Pvt Ltd

2.) Training on Google Sheets Advanced Formulas

3.) Practical Business Scenario with Hands-on tasks

4.) 3 Paid Premium ATS Friendly CV Templates

5.) Access to 3 Career Boosting Products

You can Generate Certificate anytime even with Partial Solutions!

© Mentored Minds Pvt Ltd 2019 All Rights Reserved.

Powered by Mentored Minds