Hi everybody,
I'm working with a sample of financial data. I have data on several mutual funds, each of which has a ISIN number for identification (varibale name is isin). For each isin, I have daily data (eg. inflows, outflows, currentValue, etc.) for the period between june 2012 and june 2016.
To give you an impression of what I'm talking about:
isin.............date................inflow ...
DE1234......09jun2012..........
DE1234......10jun2012..........
...
DE1234......30jun2012..........
DE1235 .....09jun2012..........
DE1235......11jun2012..........
....
Unfortunately, some dates are missing for some isin.
I would like to
a) aggregate the daily data to monthly data, within one isin, so in the end I have just one row for a specific isin in e.g. june 2012.
b) For the other variables (inflow, etc.) I would like to create an average value per month, EXEPT
c) for the variables open (= opening value of the fund on a particular day) and close (=closing value of a fund on particular day), here, I need the open value of the first day in the month that i have data for and respectively the close value for the last day in that month i have data for.
isin...............date............inflows........ ........................................open...... .........................................close
DE 1234.......jun2012......[average of DE1234 in june 2012].......[open value of e.g. 09jun2012]........[closing value of 30jun2012]
DE 1234.......jul2012.......[average of DE1234 in july 2012]...........
...
DE1235........jun2012......[average of DE1235 in junu 2012]
I hope I could express my problem in a understandable way. Could anybody give me a clue on how to achieve this?
Thanks a lot for your efforts in advance!
I'm working with a sample of financial data. I have data on several mutual funds, each of which has a ISIN number for identification (varibale name is isin). For each isin, I have daily data (eg. inflows, outflows, currentValue, etc.) for the period between june 2012 and june 2016.
To give you an impression of what I'm talking about:
isin.............date................inflow ...
DE1234......09jun2012..........
DE1234......10jun2012..........
...
DE1234......30jun2012..........
DE1235 .....09jun2012..........
DE1235......11jun2012..........
....
Unfortunately, some dates are missing for some isin.
I would like to
a) aggregate the daily data to monthly data, within one isin, so in the end I have just one row for a specific isin in e.g. june 2012.
b) For the other variables (inflow, etc.) I would like to create an average value per month, EXEPT
c) for the variables open (= opening value of the fund on a particular day) and close (=closing value of a fund on particular day), here, I need the open value of the first day in the month that i have data for and respectively the close value for the last day in that month i have data for.
isin...............date............inflows........ ........................................open...... .........................................close
DE 1234.......jun2012......[average of DE1234 in june 2012].......[open value of e.g. 09jun2012]........[closing value of 30jun2012]
DE 1234.......jul2012.......[average of DE1234 in july 2012]...........
...
DE1235........jun2012......[average of DE1235 in junu 2012]
I hope I could express my problem in a understandable way. Could anybody give me a clue on how to achieve this?
Thanks a lot for your efforts in advance!
Comment