Back to questions
The Bloomberg terminal is the go-to resource for financial professionals, offering convenient access to a wide array of financial datasets. As a Data Analyst at Bloomberg, you have access to historical data on stock performance for the FAANG stocks.
Your task is to analyze the inter-month change in percentage for each FAANG stock by month over the years. This involves calculating the percentage change in closing price from one month to the next using the following formula:
Inter-month change in percentage = (Current month's closing price - Previous month's closing price) / Previous month's closing price x 100
For each FAANG stock, display the ticker symbol, the last day of the month, closing price, and the inter-month value change in percentage rounded to two decimal places for each stock. Ensure that the results are sorted by ticker symbol and date in chronological order.
| Column Name | Type | Description |
|---|---|---|
| date | datetime | The specified date (mm/dd/yyyy) of the stock data. |
| ticker | varchar | The stock ticker symbol (e.g., AAPL) for the corresponding company. |
| open | decimal | The opening price of the stock at the start of the trading day. |
| high | decimal | The highest price reached by the stock during the trading day. |
| low | decimal | The lowest price reached by the stock during the trading day. |
| close | decimal | The closing price of the stock at the end of the trading day. |
Note that the table below displays randomly selected AAPL, AMZN, and GOOG data.
| date | ticker | open | high | low | close |
|---|---|---|---|---|---|
| 01/31/2020 00:00:00 | AAPL | 74.06 | 81.96 | 73.19 | 77.38 |
| 02/29/2020 00:00:00 | AAPL | 76.07 | 81.81 | 64.09 | 68.34 |
| 03/31/2020 00:00:00 | AAPL | 70.57 | 76.00 | 53.15 | 63.57 |
| 01/31/2020 00:00:00 | AMZN | 93.75 | 102.79 | 90.77 | 100.44 |
| 02/29/2020 00:00:00 | AMZN | 100.53 | 109.30 | 90.56 | 94.19 |
| 03/31/2020 00:00:00 | AMZN | 95.32 | 99.82 | 81.30 | 97.49 |
| 01/31/2020 00:00:00 | GOOG | 67.08 | 75.16 | 67.08 | 71.71 |
| 02/29/2020 00:00:00 | GOOG | 73.10 | 76.61 | 63.55 | 66.97 |
| 03/31/2020 00:00:00 | GOOG | 67.58 | 70.51 | 50.68 | 58.14 |
| ticker | date | close | intermth_change_pct |
|---|---|---|---|
| AAPL | 01/31/2020 00:00:00 | 77.38 | NULL |
| AAPL | 02/29/2020 00:00:00 | 68.34 | -11.68 |
| AAPL | 03/31/2020 00:00:00 | 63.57 | -6.98 |
| AMZN | 01/31/2020 00:00:00 | 100.44 | NULL |
| AMZN | 02/29/2020 00:00:00 | 94.19 | -6.22 |
| AMZN | 03/31/2020 00:00:00 | 97.49 | 3.50 |
| GOOG | 01/31/2020 00:00:00 | 71.71 | NULL |
| GOOG | 02/29/2020 00:00:00 | 66.97 | -6.61 |
| GOOG | 03/31/2020 00:00:00 | 58.14 | -13.19 |
The dataset you are querying against may have different input & output - this is just an example!
First, we utilize the window function to retrieve the closing price from the previous month for each ticker symbol.
Next, we enclose the query from Step 1 into a CTE called .
Now, we'll calculate the inter-month change in percentage using the formula:
Inter-month change in percentage = (Current month's close price - Previous month's close price) / Previous month's close price x 100
This formula is applied as . We subtract the previous month's closing price from the current month's closing price, divide by the previous month's closing price, and then multiplying by 100 to convert it into a percentage. Additionally, we round the result to two decimal places.
Finally, the results are ordered by ticker symbol and last day of the month.