Search code examples
excelkdb

Can we do asof join in excel as we do in kdb

I have no knowledge of excel and I am wondering if there is a way to convert this asof join statement in q-coding to one using excel formula

:update string issueSeries from aj[`sym`issueSeries`stockExchange`time;update `$issueSeries from table;update `g#sym from `time xasc select time+maxinterval,sym,`$issueSeries,stockExchange,refprice,refpricetime,prevpricetime,prevprice from reftable];

Table 1
 Time        Exc    sym  price
2:40:29 PM  25001   AMX 14.47
2:40:48 PM  25001   AMX 14.48
2:40:53 PM  25001   AMX 14.45
2:41:21 PM  25001   AMX 14.44
2:41:29 PM  25001   AMX 14.49   

   Table 2
    Exc     Sym  Refprice  ref-time
    25001   AMX   14.47   2:39:54 PM
    25001   AMX   14.46   2:40:36 PM
    25001   AMX   14.46   2:41:19 PM
    25001   AMX   14.46   2:41:20 PM
    25001   AMX   14.46   2:41:31 PM

Table 3 (result)
Time        ref-time     sym   exc    price   refprice

2:40:29 PM  2:39:54 PM   AMX   25001  14.47   14.47
2:40:48 PM  2:40:36 PM   AMX   25001  14.48   14.46
2:40:53 PM  2:40:36 PM   AMX   25001  14.45   14.46
2:41:21 PM  2:41:20 PM   AMX   25001  14.44   14.46
2:41:29 PM  2:41:20 PM   AMX   25001  14.49   14.46

Simplified example where exchange and sym are held constant.

The ref-time column of table3 is different from ref-time column of table2, due to asof join which is (aj) in the code.

Solution

  • It looks like you just made a copy of table 1 and then looked up associated ref-time and refprice from table 2 based on Exc and sym. If that's the case, and assuming a datasetup like this (note that tables 1 and 2 are sorted by Exc then sym then Time):

    enter image description here

    I initially copied table 1 and pasted as-is into K2. I then inserted new columns for ref-time and refprice.

    • Formula in cell L2 and copied down to get ref-time: =MAX(INDEX($I$3:$I$7*($F$3:$F$7=M3)*($G$3:$G$7=N3)*($I$3:$I$7<=K3),))
    • Formula in cell P2 and copied down to get refprice: =INDEX($H$3:$H$7,MATCH(1,INDEX(($F$3:$F$7=M3)*($G$3:$G$7=N3)*($I$3:$I$7=L3),),0))

    Adjust the ranges to fit your actual data-set.