We help IT Professionals succeed at work.

Merge Time Series

daphren
daphren asked
on
Hi,

how can I merge two time series (like IND1 and IND2) to get IND3. The calculations to join both series can be seen in the attached excel file.
I want IND3 to consist of IND1 until the MERGE_DATE and thereafter of IND2 (as specified in the table mergetable).

In the original file IND3 is missing and should be calculated via  VBA (or SQL if possible).

In this example the timestamps of IND1 and IND2 are identical, that is not always the case
 therefore  before MERGE_DATE the timestamps should be from IND1 (MNEMONIC_first) thereafter from IND2 (MNEMONIC_second)

Thanks for your help.

Regards,
daphren
DB-TEST.mdb
MERGETABLE-Calculations.xls
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Does the spreadsheet contain the values you want?  Is this the logic:
If Date< 5/5/2011 then take next days IND3 * current day IND1 / next day IND1
If date >= 5/5/2011, then ID3 = IND2

Author

Commented:
Yes the spreadsheet contains the values I want. The logic is correct.

Author

Commented:
Too difficult?
Jeffrey CoachmanMIS Liason
CERTIFIED EXPERT
Most Valuable Expert 2012

Commented:
Too "Confusing"
<how can I merge two time series>
I only see "Numbers" in IND1 and IND2, not "Time series"

It is not clear to me how any of the tables in the Access db relate to the issue...

Author

Commented:
I apologize for any confustion.

Please have a look at the excel file. There I have two time series IND 1 and IND 2. IND 1 has a longer history than IND2 but no observations at the ending dates. IND 2 has newer observations but a shorter history. Therefore I want to combine both series. The result is IND3. How can I achive this in access, given the information in mergetable (i.e. which time series to merge (MNMONIC_first and MNEMONIC_second), when to combine (MERGE_DATE) and the MNEMOINC of the newly build time series MNEMONIC_new).

Hope this helps.

Author

Commented:
Still too confusing? Any help is welcome.
CERTIFIED EXPERT
Commented:
Check sample (all code is in module). Press button Run in form
DB-TEST.mdb

Author

Commented:
Thanks a lot.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.