Sanjeev Kumar Ranjan

Job Responsibilities:

• Involved in a proactive role gathering requirements and providing solutions through automation, data analysis and dashboard creation using VBA/Advanced Excel functions and SQL.
• Collaborated with cross-functional teams to understand reporting requirements and ensure alignment with business objectives.
• Proficiently prepared Daily, Weekly, and Monthly reports in Excel, showcasing a high level of attention to detail and accuracy.
• Integrated data from diverse sources, including QlikView, Archer etc.,to compile comprehensive and insightful reports.
• Proficiently managed large quantities of data by exporting from Archer, demonstrating strong data handling and extraction skills.
• Implemented effective data filtering strategies to extract relevant information for analysis and reporting purposes.
• Uploaded cleaned and filtered data into SQL Server, showcasing expertise in database management and data integration.
• Developed and executed complex SQL queries to retrieve meaningful insights from the data, meeting stakeholder requirements.
• Designed and created multiple tables across various databases, ensuring optimal database structure for efficient data storage and retrieval. Played a key role as an Individual Contributor in current responsibilities.
• Implemented a seamless process for importing new data into the Data Warehouse, maintaining data integrity and accuracy.
• Demonstrated agility in addressing changes by promptly fixing tables, views, and stored procedures to accommodate evolving business needs.
• Received positive feedback for the clarity and effectiveness of reports, contributing to improved decision-making within the organization.

Projects Accomplished in Capco:

Name : Automated Exception Process Management.

Skill: VBA and MS Excel

Description: Spearheaded the automation of the Exception Process Management Dashboard, delivering the project to stakeholders within the established timeline. Applied Agile Scrum methodology to effectively manage the project lifecycle, ensuring flexibility and responsiveness to evolving requirements. Collaborated closely with stakeholders to gather project requirements, facilitating clear communication and understanding of expectations. Implemented a transparent project progress tracking system, regularly sending updates to the Delivery team for review.

Conducted daily Scrum calls with the Delivery team to discuss progress, address challenges, and gather updated requirements to ensure alignment with stakeholder needs. Successfully delivered a comprehensive Dashboard comprising one Summary and three reporting sheets, catering to the specific needs of stakeholders. Automated the integration of the Dashboard with four different data sets, enhancing data accuracy and accessibility. Employed logical and functional criteria to transform questionnaire responses at the Engagement level, contributing to more insightful and actionable reporting. Demonstrated a high level of proficiency in data analysis and interpretation, resulting in a well-structured and informative summary and reporting sheets.

Name : Automated ITP Compliance report.

Skill: VBA and MS Excel

Description: Successfully automated the ITP Compliance report for the Delivery Team, streamlining the reporting process across all regions. Engineered a system to prepare raw data for the report using multiple data sets, ensuring accuracy and efficiency in data processing. Implemented automation to dynamically refresh multiple pivots and various summary pages, allowing users to select different parameters for customized views.

Task: Fixed the Data warehouse (SQL Database) for routine reporting after the new fields changes in data source

Skill: SQL

Description: Coordinated efforts to fix tables, views, and stored procedures to accommodate changes in data structure. Worked closely with stakeholders to understand their reporting needs and incorporated necessary modifications into routine reports. Ensured seamless integration of updated data fields into existing reporting frameworks, maintaining data accuracy and report reliability. Collaborated with cross-functional teams to communicate changes, address concerns, and facilitate a smooth transition in reporting processes. Played a pivotal role in maintaining the consistency and relevance of routine reports, meeting stakeholder expectations.

Client: Deutsche Bank

Job Responsibilities:

● Worked closely with our client team (Deutsche Bank) and give them the better solution by analyzing and automating tasks.

● Update Global, European, US and APAC Broker votes in database and analyze ranking then prepare dashboard in BI tools i.e Tableau and Power Bi.

● Prepare live reports in R-Home based on various parameters according to client requirements.

● Prepare weekly, monthly and Yearly dashboard. Data capture from multiple data sources like QlikView, Rware sales tool, SharePoint, SQL database and many more.

● Utilize VBA to build user defined functions and automate processes.

● Create/update model for existing vba enabled dashboard.

● Prepare PMO Matrix for Deutsche bank analyst basis of various KPIs.

● Create dataset for II survey, Extel survey, Analyst broker votes and analyze the data for final report.

● Prepare and analyze data for Sales interaction Dashboard for Deutsche bank Mgt team basis of meeting booked by DB sales with analyst.

· Prepare weekly dashboard in Power Bi and Tableau.

· Using Python created an aggregated report daily for the client to make investment decisions and help analyze market trends.

Projects Accomplished in Evalueserve:

Name : Automated Weekly Data for Analysis Dashboard.

Description: I have automated the Weekly Data for Analysis Dashboard which we used to prepare and update manually and the whole process took around 3 - 4 hrs and now with the help of macro it takes only 45 mins to prepare the complete Dashboard. Earlier report was linked with multiple data sets, now it is merged into one file. We used to copy/paste data, drag formulas and format data into multiple data sheets which takes too much time. Now the whole process can be done in a single click.

Name : Automated FICC Headline Rank Dashboard.

Description: I have automated FICC Headline Rank Dashboard for clients to see the data what they want to see. I have built multiple filters/options to select and view the data. I have automated the dashboard end to end like copy/paste raw data, put formulas, formats and many more. So that client can look at the summary in one click.

Saving: 2 Hours Weekly

Tool Used- VBA.

Apart from the above automation, I have updated the macro for Yearly PMO, Advisory MIS file, Readership and many more.

Data Visualization Accomplished in Evalueserve:

Name: Deutsche Bank, Global:

Tools Used: Tableau, MS Excel

Roles & Responsibilities:

● Built Strategic, Analytical and operational dashboards using Tableau.

● Created a complex dashboard using parameters, sets, groups and calculations and also drill down and drill up in worksheets and customization using filters and actions in Tableau.

● Calculated profit growth, growth percentage and percent of total using table calculations in Tableau.

● Performed Geographical Analysis using filled and symbol maps on the country wise data using Tableau.

● Implemented various advanced table calculations for the dashboards within Tableau.

● Created Bullet graphs to determine profit generation by using measures and dimensions data from MS Excel.

● Blended data from multiple databases into one report by selecting primary key’s from each database for data validation.

Client: Johnson Controls

JOB RESPONSIBILITIES:

● Work in coordination of various delivery teams for their process improvement requirements and providing them the best possible solution by automating tasks.

● Create Dashboard using Excel functions, formulas and report to Client or IBM Team.

● Using SPSS (IBM Analytics Tool) to prepare Predictive Metric Report

● Using Tableau Tool to prepare Dashboard and stories.

● Utilize VBA to build user defined functions and automate processes.

● Automate ERP Report for different Business Units.

● Create Macro for daily, weekly and Monthly reports.

● Prepare SLA Dashboard, Weekly and monthly Volumetric and Weekly governance deck in Excel and upload on SharePoint.

● Good knowledge in writing SQL queries and Joins.

Projects Accomplished in IBM:

Name- VBA Macro enabled spreadsheet to Automate Quality Check Sampling

Description- A VBA Macro was created to reduce manual effort, enabling users to create a single automated random sample database of multiple business verticals that have different formats of production reports, for their processed invoices counts.

Saving: 2 Hours Daily

Tool Used- VBA.

Name- Automated Weekly Escalation Report

Description- A VBA enabled spreadsheet put in place that automatically pulls pending items from the various reports of all business verticals, prepares pre-defined analysis, and shoots an auto-mailer to all business owners and clients for their required actions.

Saving: 12 Hours Monthly.

Tool Used- VBA.

Name- Auto-formatting of Invoice Upload and Journal Entries files

Description- There was manual effort on formatting spreadsheets received from requesters, before uploading to Oracle. The similar case was with upload of Journal Entries files to Oracle, so two VBA enabled macro spreadsheets were created to auto format various upload files and journal entries files that fully automates the manual effort of formatting.

Saving: 4 Hours Daily

Tool Used- VBA.

JOB RESPONSIBILITIES:

● Utilize VBA to build user defined functions and automate processes.

● Ensure to match data by using common characteristics found within the data set.

● Creating Dashboard using Excel functions, formulas and reports to Clients.

● Create Macro based spreadsheets for daily, weekly and Monthly reports.

● Design and implement software components based on business requirements.

● Ensure maximum performance of processes in coordination with internal IT teams.

● Generated reports from the database using SQL.