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.
