You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
BigQuery SQL to get the Adjusted Closing Price for the Last Trading Day in every Quarter (via dimension date table)
WITH
ASX_DATA AS (
SELECT'ASX 200 (XJO)'AS Index
,DT.Date
,DT.Month
,DT.Year
,DT.Quarter
,DT.EndOfQuarter
,DT.Workday_YN
,A.Adj_CloseAS ADJUSTED_CLOSE
--,Volume
,LAG(A.Adj_Close) OVER (ORDER BYDT.DateASC) AS LAG_ADJUSTED_CLOSE
,ROW_NUMBER() OVER (PARTITION BY DT.Year, DT.QuarterORDER BYDT.DateDESC) AS RNK
FROMtransformation.ASX_AXJO_200_INDEX_TRF A
LEFT JOINDWH.DIM_DATE DT
ONA.Date=DT.DateWHERE1=1AND Adj_Close IS NOT NULLAND Adj_Close !=0
)
,QUARTER_ASX_DATA AS (
SELECT
Index
,DateAS Last_Working_Day
,EndOfQuarter
,ADJUSTED_CLOSE
,LAG_ADJUSTED_CLOSE
,ROW_NUMBER() OVER (ORDER BYDateASC) AS QTR_RNK
FROM ASX_DATA
WHERE1=1AND RNK =1
)
,ANCHOR_PRICE AS (
SELECT Index, ADJUSTED_CLOSE AS ANCHOR_PRICE
FROM QUARTER_ASX_DATA
WHERE QTR_RNK =1
)
SELECTQ.Index
,Q.Last_Working_Day
,Q.EndOfQuarter
,Q.ADJUSTED_CLOSE
,A.ANCHOR_PRICE
,Q.LAG_ADJUSTED_CLOSE
,ROUND((Q.ADJUSTED_CLOSE-Q.LAG_ADJUSTED_CLOSE)/Q.LAG_ADJUSTED_CLOSE, 2) AS PERCENTAGE_CHANGE
,ROUND((Q.ADJUSTED_CLOSE-A.ANCHOR_PRICE)/A.ANCHOR_PRICE, 2) AS CUMULATIVE_PERCENTAGE_CHANGE
FROM QUARTER_ASX_DATA Q
LEFT JOIN ANCHOR_PRICE A
ONQ.Index=A.Index
;