Stock Market Research

Disclaimer: This article is for informational purposes only and should not be considered financial advice.

Problem

We will be exploring the HyperData transformation engine by researching Consolidated Edison, Inc. (NYSE:ED). Consolidated Edison is an energy company that provides energy utilities to millions of Americans. Let's say we have a thesis that the stock price of ED is affected by the weather since homes use more electricity in colder temperatures. To test this hypothesis, we can start by accessing data on the air temperature and precipitation in New York, NY as well as the price history and financials of NYSE:ED stock.

After pulling raw data from these different sources, we want to start doing things with the data like visualization, forecasting, statistical analysis, machine learning model training, and more to explore our investment thesis. The problem now lies in transforming the data in a way that can power all of these applications and be flexible enough to add new data sources in the future.

Solution

We can use the HyperData API to simplify the data integration process and create a single source of truth. This way

Source Data

Stock Price

We can get the stock price and volume data from https://www.alphavantage.co. The following file is the daily open, high, low, close, and volume of NYSE:ED pulled from the Alpha Vantage API.

304KB

stock_price.csv

Raw stock price and volume data from https://www.alphavantage.co

Here's a sample row from the dataset:

time	open	high	low	close	volume
2024-03-25	88.75	89.04	88.00	88.50	1163704

Weather

We can use the https://open-meteo.com/ free weather API to get the average daily temperature and precipitation in New York, NY for every day for the last four years. The following file is the raw data pulled from the Open Meto API.

29KB

weather.csv

Raw weather data from https://open-meteo.com

Here's a sample row from the dataset:

time	temperature	precipitation
2020-03-01	-0.6	0.0

Financials

To better understand Consolidated Edison, Inc. we can access the yearly financials from https://financialmodelingprep.com. The following file has the raw financial statement data on NYSE:ED for the past four years retrieved from the Financial Modeling Prep API.

3KB

financials.csv

Raw financial statement data from https://financialmodelingprep.com

Transforming with Hyperdata

The source data is in different formats and will be updated at different frequencies so we will use the HyperData API to transform the source data into a single format for use. Here's the code we will use to do this:

import hdata as hd

# Load the raw data
ny_weather = hd.Source(transformation_key='*****', data='weather.csv')
ed_stock_price = hd.Source(transformation_key='*****', data='stock_price.csv')
ed_financials = hd.Source(transformation_key='*****', data='financials.csv')

# Transform
sources = [ny_weather, ed_stock_price, ed_financials]
entity, attribute, record = hd.transform(sources, auth_token='*****')

Here is the output of the transformation in CSV format for reference. The data is returned from the transform function as three pandas data frames.

entity

193B

entity.csv

attribute

821B

attribute.csv

record

3MB

record.csv

With this clean data feed, we can now apply visualization, forecasting, statistical analysis, machine learning model training, and more to validate our thesis and dive deep into equity research. We can even add more sources later on without breaking the pipeline. This is made possible by creating a single source of truth for external data with the HyperData API.