Explore stock market data

When you’ve successfully collected 1-min intraday stock data, it’s time to have some fun and explore the data.

Because of the high granularity of the dataset, there are numerous ways to explore it. For example, you could analyze stock prices and volumes on a minute-by-minute basis. With TimescaleDB, you could also bucket records into custom intervals (for example, 2 min or 15 min) using TimescaleDB aggregate functions.

Let’s see how it’s done!

Install Plotly and Pandas

To get started with data exploration, you need to install a couple of tools first:

• Pandas, to query and structure the data (this is already installed if you have completed the steps in the previous sections)
• Plotly, to create visualizations quickly

Install both

2. pip install plotly pandas

When you have those installed, you need to open a new Python file, or use a Jupyter notebook to start exploring the dataset.

Establish database connection

Use the configuration file you created earlier with psycopg2 to create a database connection object:

2. import config, psycopg2

4. conn = psycopg2.connect(database=config.DB_NAME,

6. host=config.DB_HOST,

8. user=config.DB_USER,

10. password=config.DB_PASS,

12. port=config.DB_PORT)

In each data exploration script, you need to reference this connection object to be able to query the database.

Generate stock market insights

Let’s start off analyzing trading volumes, then have a look at weekly price points, and finally, dig deep on price changes. The results of the queries shown are visualized using Plotly.

tip

Let these queries serve as inspiration to you, and feel free to change things up, like the analyzed bucket, the symbol or other parts of the query. Have fun!

1. Which symbols have the highest transaction volumes?
2. How did Apple’s trading volume change over time?
3. How did Apple’s stock price change over time?
4. Which symbols had the highest weekly gains?
5. Weekly FAANG prices over time?
6. Weekly price changes of Apple, Facebook, Google?
7. Distribution of daily price changes of Amazon and Zoom
8. Apple 15-min candlestick chart

1. Which symbols have the highest transaction volumes?

Let’s generate a bar chart that shows the most traded symbols in the last 14 days:

2. import plotly.express as px

4. import pandas as pd

6. query = """

8. SELECT symbol, sum(trading_volume) AS volume

10. FROM stocks_intraday

12. WHERE time > now() - INTERVAL '{bucket}'

14. GROUP BY symbol

16. ORDER BY volume DESC

18. LIMIT 5

20. """.format(bucket="14 day")

22. df = pd.read_sql(query, conn)

24. fig = px.bar(df, x='symbol', y='volume', title="Most traded symbols in the last 14 days")

26. fig.show()

2. How did Apple’s trading volume change over time?

Now let’s try a similar query focused on the daily trading volume of one symbol (for example, ‘AAPL’).

2. import plotly.express as px

4. import pandas as pd

6. query = """

8. SELECT time_bucket('{bucket}', time) AS bucket, sum(trading_volume) AS volume

10. FROM stocks_intraday

12. WHERE symbol = '{symbol}'

14. GROUP BY bucket

16. ORDER BY bucket

18. """.format(bucket="1 day", symbol="AAPL")

20. df = pd.read_sql(query, conn)

22. fig = px.line(df, x='bucket', y='volume', title="Apple's daily trading volume over time")

24. fig.show()

3. How did Apple’s stock price change over time?

This query returns the weekly stock price of Apple over time:

2. import plotly.express as px

4. import pandas as pd

6. query = """

8. SELECT time_bucket('{bucket}', time) AS bucket,

10. last(price_close, time) AS last_closing_price

12. FROM stocks_intraday

14. WHERE symbol = '{symbol}'

16. GROUP BY bucket

18. ORDER BY bucket

20. """.format(bucket="7 days", symbol="AAPL")

22. df = pd.read_sql(query, conn)

24. fig = px.line(df, x='bucket', y='last_closing_price')

26. fig.show()

4. Which symbols had the highest weekly gains?

Now generate a table containing the symbols with the biggest weekly gains:

2. import plotly.express as px

4. import pandas as pd

6. query = """

8. SELECT symbol, bucket, max((closing_price-opening_price)/closing_price*100) AS price_change_pct

10. FROM (

13. SELECT

16. symbol,

19. time_bucket('{bucket}', time) AS bucket,

22. first(price_open, time) AS opening_price,

25. last(price_close, time) AS closing_price

27. FROM stocks_intraday

29. GROUP BY bucket, symbol

31. ) s

33. GROUP BY symbol, s.bucket

35. ORDER BY price_change_pct {orderby}

37. LIMIT 5

39. """.format(bucket="7 days", orderby="DESC")

41. df = pd.read_sql(query, conn)

43. print(df)

price_change_pct shows the price change that happened between the start and end of the week.

bucket shows (the first day of) the week.

tip

Change orderby to “ASC” to query the biggest losses.

5. Weekly FAANG prices over time?

Let’s see a line chart with the FAANG (Facebook, Apple, Amazon, Netflix, Google/Alphabet) weekly stock prices:

2. import plotly.express as px

4. import pandas as pd

6. query = """

8. SELECT symbol, time_bucket('{bucket}', time) AS bucket,

11. last(price_close, time) AS last_closing_price

13. FROM stocks_intraday

15. WHERE symbol in {symbols}

17. GROUP BY bucket, symbol

19. ORDER BY bucket

21. """.format(bucket="7 days", symbols="('FB', 'AAPL',  'AMZN', 'NFLX', 'GOOG')")

23. df = pd.read_sql(query, conn)

25. fig = px.line(df, x='bucket', y='last_closing_price', color='symbol', title="FAANG prices over time")

27. fig.show()

6. Weekly price changes of Apple, Facebook, Google?

Analyzing the price points directly can be useful when you are looking at one specific symbol, but if you want to compare different stocks, it might be better to look at price changes instead. Let’s compare the price changes of Apple, Facebook, and Google:

2. import plotly.express as px

4. import pandas as pd

6. query = """

8. SELECT symbol, bucket, max((closing_price-opening_price)/closing_price) AS price_change_pct

10. FROM (

13. SELECT

16. symbol,

19. time_bucket('{bucket}}', time) AS bucket,

22. first(price_open, time) AS opening_price,

25. last(price_close, time) AS closing_price

27. FROM stocks_intraday

29. WHERE symbol IN {symbols}

31. GROUP BY bucket, symbol

33. ) s

35. GROUP BY symbol, s.bucket

37. ORDER BY bucket

39. """.format(bucket="7 days", symbols="('AAPL', 'FB', 'GOOG')")

41. df = pd.read_sql(query, conn)

43. figure = px.line(df, x="bucket", y="price_change_pct", color="symbol", title="Apple, Facebook, Google weekly price changes")

45. figure = figure.update_layout(yaxis={'tickformat': '.2%'})

47. figure.show()

7. Distribution of daily price changes of Amazon and Zoom

Now let’s generate a scatter chart to look at the distribution of daily price changes of Amazon and Zoom. Analyzing this data enables you to better understand the volatility of individual stocks and how they compare to each other.

2. import plotly.express as px

4. import pandas as pd

6. query = """

8. SELECT symbol, bucket, max((closing_price-opening_price)/closing_price) AS price_change_pct

10. FROM (

13. SELECT

16. symbol,

19. time_bucket('{bucket}', time) AS bucket,

22. first(price_open, time) AS opening_price,

25. last(price_close, time) AS closing_price

27. FROM stocks_intraday

29. WHERE symbol IN {symbols}

31. GROUP BY bucket, symbol

33. ) s

35. GROUP BY symbol, s.bucket

37. ORDER BY bucket

39. """.format(bucket="1 day", symbols="('ZM', 'AMZN')")

41. df = pd.read_sql(query, conn)

43. figure = px.scatter(df, x="price_change_pct", color="symbol", title="Distribution of daily price changes (Amazon, Zoom)")

45. figure = figure.update_layout(xaxis={'tickformat': '.2%'})

47. figure.show()

8. Apple 15-min candlestick chart

Finally, because this is a tutorial about stocks, let’s generate a 15-min candlestick chart for Apple:

For candlestick charts, you need to import Plotly’s graph_object module.

2. import pandas as pd

4. import plotly.graph_objects as go

6. query = """

8. SELECT time_bucket('{bucket}', time) AS bucket,

11. FIRST(price_open, time) AS price_open,

14. LAST(price_close, time) AS price_close,

16. MAX(price_high) AS price_high,

18. MIN(price_low) AS price_low

20. FROM stocks_intraday

22. WHERE symbol = '{symbol}' AND date(time) = date('{date}')

25. GROUP BY bucket

27. """.format(bucket="15 min", symbol="AAPL", date="2021-06-09")

29. df = pd.read_sql(query, conn)

31. figure = go.Figure(data=[go.Candlestick(x=df['bucket'],

33. open=df['price_open'],

35. high=df['price_high'],

37. low=df['price_low'],

39. close=df['price_close'],)])

41. figure.update_layout(title="15-min candlestick chart of Apple, 2021-06-09")

43. figure.show()

tip

Change date to see the candlesticks for another day.

Resources

• Intraday stocks analysis on Github
• Alpha Vantage API docs
• Pandas docs
• Plotly docs
• Timescale free trial
• Analyze cryptocurrency data