Using BI to Show Stock and Index Trends Over Different Periods

Background Requirement

It is necessary to display the stock price of a listed company and the corresponding market index trend. For intuitive comparison, the requirements are:

• Comparison periods: last 5 days, last 15 days, last 30 days, last 60 days, last 90 days, last 180 days, last 1 year, and last 3 years;
• Show three metrics: stock closing price, cumulative increase of the stock and the index (not daily increase);
• When comparing in the above periods, the starting point for the stock and the index should be the same (i.e., all lines start from the same point).

Final Effect

Implementation

Idea: First, use ETL to calculate the cumulative increase of the stock and the index for each period; then, on the page, use global parameters to display the corresponding data fields.

1. Data Preparation

• Aggregate the dataset by primary key (institution code, institution name (corresponds one-to-one with code), Date)
• Create a text-type global parameter

2. Use ETL to Calculate Cumulative Increase for Each Period

2.1. Method 1

Take the calculation of the cumulative increase for the stock and index in the past year as an example:

1. Create a calculated field to get the max date in the dataset:

Max date in current dataset: MAX([Date]) OVER (PARTITION BY NULL)

2. SQL node: filter for the past year's data; can also use a filter node:

SELECT * from input1 
where input1.`Date`>= DATE_SUB(input1.`Max date in current dataset`,364)

3. Group by institution code, sort by date:

Rank: row_number() over (partition by [Institution Code] order by [Date])

4. Get base data for stock and index:

Stock base: IF([Rank]=1,[Stock Closing Price],null)
Index base: IF([Rank]=1,[Index Closing Price],null)

5. Get final base values for stock and index:

Stock base final: max([Stock base])over(partition by [Institution Code])
Index base final: max([Index base])over(partition by [Institution Code])

Explanation: Use max window function to replace nulls in [Stock base], [Index base], value remains unchanged.

6. Calculate cumulative increase:

Stock cumulative increase (1 year): [Stock Closing Price]/[Stock base final]-1
Index cumulative increase (1 year): [Index Closing Price]/[Index base final]-1

7. Use multiple branches to calculate cumulative increase for each period, use the longest period (last 3 years) as the main table, join other periods' data for output.

ETL model reference:

2.2. Method 2

1. Create a calculated field to get the max date in the dataset:

Max date in current dataset: MAX([Date]) OVER (PARTITION BY NULL)

2. Mark different periods for grouping and filtering:

3 years ago initial date: [Max date in current dataset] - interval 3 years
Last 1 year: case when [Date]>[Max date in current dataset]- interval 1 year then 'Last 1 Year' end
Last half year: case when [Date]>=[Max date in current dataset]-interval 179 days then 'Last Half Year' end

3. Filter for the longest period -- last 3 years: [Date] > [3 years ago initial date]

4. Calculate base data for each period:

Stock base_Last 1 Year: nvl2([Last 1 Year],first([Stock Closing Price])over(partition by [Institution Code],[Last 1 Year] order by [Date]),null)
Stock base_Last 3 Years: first([Stock Closing Price])over(partition by [Institution Code] order by [Date])

Explanation: Sort by [Date], use first window function to get the earliest [Stock Closing Price] in the period; nvl2 checks for non-null in [Last 1 Year] to only calculate for that period.

5. Calculate cumulative increase; select columns for output.

Stock cumulative increase (1 year): [Stock Closing Price]/[Stock base_Last 1 Year]-1
Index cumulative increase (1 year): [Index Closing Price]/[Index base_Last 1 Year]-1

ETL model reference:

Comparison:

Method 1: Simple logic, easy to start; more ETL branches, slightly more complex structure. Method 2: Requires proficiency with window functions, more complex logic; single ETL line, simpler structure.

3. Dashboard Card Creation

3.1. Use Global Parameters to Show Dynamic Metrics

Create 2 numeric fields, put in the value bar for dynamic metrics; 1 boolean field, put in the filter bar for dynamic date switching.

Stock cumulative increase:
case [DYNAMIC_PARAMS.Stock Price Quick Date Filter]  
when 'Last 5 Days' then [Stock Cumulative Increase (5 Days)]  
when 'Last 15 Days' then [Stock Cumulative Increase (15 Days)]  
when 'Last 30 Days' then [Stock Cumulative Increase (30 Days)]  
when 'Last 60 Days' then [Stock Cumulative Increase (60 Days)]  
when 'Last 90 Days' then [Stock Cumulative Increase (90 Days)]  
when 'Last 180 Days' then [Stock Cumulative Increase (Half Year)]  
when 'Last 1 Year' then [Stock Cumulative Increase (1 Year)]  
when 'Last 3 Years' then [Stock Cumulative Increase (3 Years)]  
else null  end

Index cumulative increase:
case [DYNAMIC_PARAMS.Stock Price Quick Date Filter]  
when 'Last 5 Days' then [Index Cumulative Increase (5 Days)]  
when 'Last 15 Days' then [Index Cumulative Increase (15 Days)] 
when 'Last 30 Days' then [Index Cumulative Increase (30 Days)]  
when 'Last 60 Days' then [Index Cumulative Increase (60 Days)]  
when 'Last 90 Days' then [Index Cumulative Increase (90 Days)]  
when 'Last 180 Days' then [Index Cumulative Increase (Half Year)]  
when 'Last 1 Year' then [Index Cumulative Increase (1 Year)]  
when 'Last 3 Years' then [Index Cumulative Increase (3 Years)]  
else null  end

Date filter boolean:
Method 1:
case [DYNAMIC_PARAMS.Stock Price Quick Date Filter]
when 'Last 5 Days'  then  datediff([Max date in current dataset],[Date])<=4
when 'Last 15 Days' then  datediff([Max date in current dataset],[Date])<=14
when 'Last 30 Days' then  datediff([Max date in current dataset],[Date])<=29
when 'Last 60 Days' then  datediff([Max date in current dataset],[Date])<=59
when 'Last 90 Days' then  datediff([Max date in current dataset],[Date])<=89
when 'Last 180 Days' then  datediff([Max date in current dataset],[Date])<=179
when 'Last 1 Year' then  datediff([Max date in current dataset],[Date])<=364
when 'Last 3 Years' then  datediff([Max date in current dataset],[Date])<=1094
end
Method 2: [Last 5 Days] etc. can be replaced with calculated cumulative increase fields
case [DYNAMIC_PARAMS.Stock Price Quick Date Filter]
when 'Last 5 Days'  then  [Last 5 Days] is not null
when 'Last 15 Days' then  [Last 15 Days] is not null
when 'Last 30 Days' then  [Last 30 Days] is not null
when 'Last 60 Days' then  [Last 60 Days] is not null
when 'Last 90 Days' then  [Last 90 Days] is not null
when 'Last 180 Days' then [Last Half Year] is not null
when 'Last 1 Year'  then  [Last 1 Year] is not null
when 'Last 3 Years'  then  [Last 3 Years] is not null
end

3.2. Set Multi-Line Chart and Page Linkage

3.2.1. Card Tips

To ensure all three metrics start from the same point and the stock closing price is prioritized, note the following when creating a multi-line chart:

1. Since the stock price and cumulative increase have very different value ranges, putting all three fields in the value bar (including stacked chart value bar) results in poor visualization. Also, the stock cumulative increase is calculated from the closing price, so their trends are the same; just showing [Stock Cumulative Increase] represents the stock price trend.

Solution: Put the two cumulative increases in the value bar, and all three fields in "More Tooltips"; check "Show Only Tooltips".

2. After this, the legend color in the tooltip may not match the line color. This is because the number of fields in the value bar and tooltip bar are different, and each uses theme colors independently. To fix, ensure the number of fields in both bars is the same.

Solution: Create a numeric constant field (e.g., null or 0), put it first in the value bar. This field won't affect the chart and can be aliased as blank to reduce its presence.

3. Other visualization tips:

• Set the first theme color to white, so the legend dot for closing price is white, distinguishing it from other metrics;
• Hide axes, data labels, and legend.
• 

3.2.2. Page Tips

• Create a parameter filter, select the parameter used in the card, and the filter will automatically link to cards using that parameter on the page. You can set the filter as a "button bar" style.
• Since the multi-line chart hides the legend, you can create a custom legend below the chart as needed. To show fixed content, place an image below the chart; to link the filter (e.g., show the selected "Institution Name" for the stock, corresponding market for the index), use a "carousel list" card. For implementation, see [Tips for Using Carousel List](../3-Visualization/31-Tips for Using Carousel List.md).

All done!