To use hive features for data engineering or analysis and sharing the actionable insights
NewYork stock exchange data of seven years, between 2010 to 2016, is captured for 500+ listed companies. The data set comprises of intra-day prices and volume traded for each listed company. The data serves both for machine learning and exploratory analysis projects, to automate the trading process and to predict the next trading-day winners or losers.. The scope of this project is limited to exploratory data analysis.
Exploratory analysis to understand how MoM or YoY companies from different sectors or industries and states have progressed in a period of 7 years
Two datasets StockPrices.csv and Stockcompanies.csv files having the following features:
StockPrices.csv:
Date: Trading date
Symbol: Ticker code or listed company code on NY stock exchange
Open: Intra-day opening price for each listed company
Close: Intra-day closing price for each listed company
Low: Intra-day lowest price for each listed company
High: Intra-day highest price for each listed company
Volume: Number of shares traded per day per company
Stockcompanies.csv:
Ticker_Symbol: Country to which the customer belongs
Security: Legal name of the listed com
Sector: Business vertical of the listed company
Sub_Industry: Business domain of the listed company within a Sector.
Headquarter: Headquarters address
1) Created a data pipeline using sqoop to pull the data from the tables(STOCK_COMPANIES AND STOCK_PRICES) from SQL server into Hive.
a. MYSQL DATABASE NAME: BDHS_PROJECT
i. STOCK_PRICES
ii.STOCK_COMPANIES
TABLE: STOCK_PRICES
Column Name Datatype
Trading_date Date
Symbol String
Open double
Close double
Low double
High double
Volume int
TABLE: STOCK_COMPANIES
Column Name Datatype
Symbol String
Company_name String
Sector String
Sub_industry String
Headquarter String
2) Created a new hive table with the following fields by joining the above two hive tables and used appropriate Hive built-in functions for columns (a,b,e and h to l).
Trading_year: Should contain YYYY for each record
Trading_month: Should contain MM or MMM for each record
Symbol: Ticker code
CompanyName: Legal name of the listed company
State: State to be extracted from headquarters value.
Sector: Business vertical of the listed company
Sub_Industry: Business domain of the listed company within a sector
Open: Average of intra-day opening price by month and year for each listed company
Close: Average of intra-day closing price by month and year for each listed company
Low: Average of intra-day lowest price by month and year for each listed company
High: Average of intra-day highest price by month and year for each listed company
Volume: Average of number of shares traded by month and year for each listed company
3) Find the top five companies that are good for investment
4) Show the best-growing industry by each state, having at least two or more industries mapped.
5) For each sector find the following.
a. Worst year
b. Best year