Replacing missing Values

Sundi — Tue, 27 Sep 2022 16:51:36 GMT

Dear all,

I have got a table with price data for various instruments at given dates.

Date	Price	Ticker	CCY
1/2/2020	$ 57.92	SPLV	USD
1/2/2020	€ 53.68	SPY4	EUR
1/2/2020	$ 45.40	R2US	USD
1/2/2020	$ 50.24	IEUR	USD
1/2/2020	€ 5.90	EUMD	EUR
1/2/2020	€ 5.90	EUMD	EUR
1/2/2020	$ 66.23	MCHI	USD
1/2/2020	$ 20.09	DXJZ	USD
1/2/2020	$ 111.88	IGLO	USD
1/2/2020	$ 165.85	IGIL	USD
1/2/2020	€ 55.58	IUSP	EUR
1/2/2020	€ 30.67	D5BK	EUR
1/2/2020	€ 5.20	AYEP	EUR
1/2/2020	€ 21.49	IPRV	EUR
1/2/2020	$ 26.65	GOEX	USD
1/3/2020	$ 58.02	SPLV	USD
1/3/2020	$ 494.45	CSNDX	USD
1/3/2020	€ 53.67	SPY4	EUR

I need to clean up the data and replace missing values in the "Price" field.

My problem is two fold:

1. Missing Values in existing rows:
Not every Instrument gets price data at a given trading day. Hence I have got N/A s in the data that I need to replace with the price data of the previous day. Example:

Date	Price	Ticker	CCY
1/2/2020	$ 57.92	SPLV	USD
1/2/2020	€ 53.68	SPY4	EUR
1/2/2020	$ 45.40	R2US	USD
1/2/2020	N/A	IEUR	USD

2. Missing Date-Rows. Since the "Tickers" are traded on different stock exchanges they don't share the same trading calendar. Hence For certain instruments I don't have a date row at all for a given day.
Example: See table above but the "Ticker" IEUR would not have a row entry for 1/2/2020 at all.

My goal is to have a table with a standard calendar of all working days, and for all instrument a valid price. If there is no price data available for a given day I would replace it with the price data from the previous day.

Please help. Tried a lot with Cross tables and joins but am failing miserably.

Re: Replacing missing Values

deepanshuSh — Tue, 27 Sep 2022 17:00:28 GMT

Hi @Sundi , for the First problem you need to use the peek or previous function to get the date and replace it using if condition, for example; if (Price = N/A or Null(), Peek (price), Price) as Price

For second, you need to create a master calendar using the link table (search community for the same) thereby you can fill all the dates between min and max (date) or today's date.

topic Replacing missing Values in App Development

Replacing missing Values

Re: Replacing missing Values