Jan 09 2021 10:20 PM
Hi All,
I wonder if there is an easy way with PQ to get a from a data table -> a summary table (for the last 4 year that each year will be in a separate row with the following columns-
1. open rate of the first day of the year (not always 01/01/20XX)
2. close rate of the last day of the year (not always 31/12/20XX)
the raw data is from yahoo with open and close rates for GOLD stock (attached please find)
sometimes the first trade day in a month is 2nd or 3rd so I didn't find any pattern to use...
I succeed to get the job done with simple excel formulas bu PQ is a game changer :)
my idea was to marge the same table again with an index and keep a unique row (transform- group by Year) but I'm still trying this
thank you in advance
Tomer
Jan 09 2021 11:26 PM - edited Jan 10 2021 12:19 AM
Attached two solution with dynamic array formulas and with PQ (used only UI-available commands).
Jan 10 2021 10:49 AM
Variant with formulas
If convert source data into the table Source
Open (PY)
=IFNA(INDEX(Source[Open],MATCH(MINIFS(Source[Date],Source[Year],[@Year]-1),Source[Date],0)),"no data")
Open (CY)
=IFNA(INDEX(Source[Open],MATCH(MINIFS(Source[Date],Source[Year],[@Year]),Source[Date],0)),"no data")
Close (CY)
=IFNA(INDEX(Source[Close],MATCH(MAXIFS(Source[Date],Source[Year],[@Year]),Source[Date],0)),"no data")