Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.