Hi, there,How to calculate the portfolio return rate by group and date.I have two table : one is daily stock return rate ‘stk_rt’ table, likethis :sym date rt--------------------------------SH600074 2012.03.02 0.00821917SH600074 2012.03.01 0.00274725SH600074 2012.02.29 -0.005464476SH600074 2012.02.28 -0.03430076SH600074 2012.02.27 -0.002631576SH600074 2012.02.24 0.02981027SH600074 2012.02.23 -0.00539083SH600074 2012.02.22 0.01366119the other table is the portfolio ‘grp’ , like this :port_sym sym------------------HLY_BOPET SZ000859HLY_BOPET SZ000973HLY_BOPET SZ002263HLY_CDM SZ000155HLY_CDM SZ000401HLY_CDM SZ000539HLY_CDM SZ000601So i want to calculate the daily return rate for each portfolio, theresult would like this :port_sym date grp_rt-----------------------------------HLY_BOPET 2012.03.02 0.0052HLY_CDM 2012.03.02 0.0078…(the grp_rt is the average rt of the stock ‘sym’ belong to portfolio’port_sym’ in certain day ).How to do that in q ?Thanks for your help,Halley
To: personal-kdbplus@googlegroups.com
X-Mailer: Apple Mail (2.1257)
X-Gm-Message-State: ALoCoQkL1CI5qD+nIu8loB8A/nQFvNI2Vi3L7ayzAHT+WWmQFDlwKi/32hsj7UeRIzSTFmuj9Qkq
select avg rt by port_sym, date from ungroup stk_rt lj select port_sym =
by sym from grp
Andras
2012.03.07. d=E1tummal, 10:11 id=F5pontban bigbug =EDrta:
> Hi, there,
>
> How to calculate the portfolio return rate by group and date.
>
> I have two table : one is daily stock return rate ‘stk_rt’ table, like
> this :
>
>
> sym date rt
> --------------------------------
> SH600074 2012.03.02 0.00821917
> SH600074 2012.03.01 0.00274725
> SH600074 2012.02.29 -0.005464476
> SH600074 2012.02.28 -0.03430076
> SH600074 2012.02.27 -0.002631576
> SH600074 2012.02.24 0.02981027
> SH600074 2012.02.23 -0.00539083
> SH600074 2012.02.22 0.01366119
>
> the other table is the portfolio ‘grp’ , like this :
>
>
> port_sym sym
> ------------------
> HLY_BOPET SZ000859
> HLY_BOPET SZ000973
> HLY_BOPET SZ002263
> HLY_CDM SZ000155
> HLY_CDM SZ000401
> HLY_CDM SZ000539
> HLY_CDM SZ000601
>
> So i want to calculate the daily return rate for each portfolio, the
> result would like this :
>
> port_sym date grp_rt
> -----------------------------------
> HLY_BOPET 2012.03.02 0.0052
> HLY_CDM 2012.03.02 0.0078
> …
>
>
> (the grp_rt is the average rt of the stock ‘sym’ belong to portfolio
> ‘port_sym’ in certain day ).
>
> How to do that in q ?
>
> Thanks for your help,
>
> Halley
>
> –
> You received this message because you are subscribed to the Google =
Groups “Kdb+ Personal Developers” group.
> To post to this group, send email to =
personal-kdbplus@googlegroups.com.
> To unsubscribe from this group, send email to =
personal-kdbplus+unsubscribe@googlegroups.com.
> For more options, visit this group at =
http://groups.google.com/group/personal-kdbplus?hl=en.
>
Thanks Andras.I use " stk_rt lj (`sym xkey port_sym ) " to replace " stk_rt ljselect port_sym by sym from grp " , that generates same result butcost only 1/10 time.But I still can’t run on my 32b trial version KBD+ the line “selectavg rt by port_sym, date from ungroup stk_rt lj select port_sym by symfrom grp” for the memory explode.(‘count stk_rt’ will have 5304782 rows, ‘count grp’ will have 15199rows ) . I think the ‘ungroup’ will creat a huge table that my 3G RAMmachine can’t suffer .Is there better way? Anyway, I just want a simple result table. I amOK for a little bit slow running script.Thanks,HalleyOn 3?7?, ??6?14?, Bohák András <and…> wrote:> select avg rt by port_sym, date from ungroup stk_rt lj select port_sym by sym from grp>> Andras>> 2012.03.07. dátummal, 10:11 id?pontban bigbug írta:>>>> > Hi, there,>> > How to calculate the portfolio return rate by group and date.>> > I have two table : one is daily stock return rate ‘stk_rt’ table, like> > this :>> > sym date rt> > --------------------------------> > SH600074 2012.03.02 0.00821917> > SH600074 2012.03.01 0.00274725> > SH600074 2012.02.29 -0.005464476> > SH600074 2012.02.28 -0.03430076> > SH600074 2012.02.27 -0.002631576> > SH600074 2012.02.24 0.02981027> > SH600074 2012.02.23 -0.00539083> > SH600074 2012.02.22 0.01366119>> > the other table is the portfolio ‘grp’ , like this :>> > port_sym sym> > ------------------> > HLY_BOPET SZ000859> > HLY_BOPET SZ000973> > HLY_BOPET SZ002263> > HLY_CDM SZ000155> > HLY_CDM SZ000401> > HLY_CDM SZ000539> > HLY_CDM SZ000601>> > So i want to calculate the daily return rate for each portfolio, the> > result would like this :>> > port_sym date grp_rt> > -----------------------------------> > HLY_BOPET 2012.03.02 0.0052> > HLY_CDM 2012.03.02 0.0078> > …>> > (the grp_rt is the average rt of the stock ‘sym’ belong to portfolio> > ‘port_sym’ in certain day ).>> > How to do that in q ?>> > Thanks for your help,>> > Halley>> > –> >
Submitted via Google Groups</and…>
To: personal-kdbplus@googlegroups.com
X-Mailer: Apple Mail (2.1257)
X-Gm-Message-State: ALoCoQkb2yrgNEIPVjNZoJsKwdCU6nW9PvvjJ0PQ9zpdTyFo134EAreOT9j0cnOCJiNCOQsH0NYN
Hi,
I don’t understand this one:
> I use " stk_rt lj (`sym xkey port_sym ) " to replace " stk_rt lj
> select port_sym by sym from grp " , that generates same result but
> cost only 1/10 time.
port_sym is a table too?
I also use the 32-bit version, if memory is the bottleneck, something =
like this usually helps:
raze {:() xkey select port_sym:x, avg rt by date from stk_rt where sym =
in (exec sym from grp where port_sym=x)} each exec distinct port_sym =
from grp
This calculates the portfolio returns one-by-one, so as long as stk_rt =
fits in memory, this one should be able to run. It is a good idea to =
start q with -g 1.
Andras
2012.03.08. d=C3=A1tummal, 3:49 id=C5=91pontban bigbug =C3=ADrta:
> Thanks Andras.
>
> I use " stk_rt lj (`sym xkey port_sym ) " to replace " stk_rt lj
> select port_sym by sym from grp " , that generates same result but
> cost only 1/10 time.
>
> But I still can’t run on my 32b trial version KBD+ the line “select
> avg rt by port_sym, date from ungroup stk_rt lj select port_sym by sym
> from grp” for the memory explode.
>
> (‘count stk_rt’ will have 5304782 rows, ‘count grp’ will have 15199
> rows ) . I think the ‘ungroup’ will creat a huge table that my 3G RAM
> machine can’t suffer .
>
> Is there better way? Anyway, I just want a simple result table. I am
> OK for a little bit slow running script.
>
> Thanks,
> Halley
>
>
> On 3=E6=9C=887=E6=97=A5, =E4=B8=8B=E5=8D=886=E6=97=B614=E5=88=86, =
Boh=C3=A1k Andr=C3=A1s <and…> wrote:
>> select avg rt by port_sym, date from ungroup stk_rt lj select =
port_sym by sym from grp
>>
>> Andras
>>
>> 2012.03.07. d=C3=A1tummal, 10:11 id=C5=91pontban bigbug =C3=ADrta:
>>
>>
>>
>>> Hi, there,
>>
>>> How to calculate the portfolio return rate by group and date.
>>
>>> I have two table : one is daily stock return rate ‘stk_rt’ table, =
like
>>> this :
>>
>>> sym date rt
>>> --------------------------------
>>> SH600074 2012.03.02 0.00821917
>>> SH600074 2012.03.01 0.00274725
>>> SH600074 2012.02.29 -0.005464476
>>> SH600074 2012.02.28 -0.03430076
>>> SH600074 2012.02.27 -0.002631576
>>> SH600074 2012.02.24 0.02981027
>>> SH600074 2012.02.23 -0.00539083
>>> SH600074 2012.02.22 0.01366119
>>
>>> the other table is the portfolio ‘grp’ , like this :
>>
>>> port_sym sym
>>> ------------------
>>> HLY_BOPET SZ000859
>>> HLY_BOPET SZ000973
>>> HLY_BOPET SZ002263
>>> HLY_CDM SZ000155
>>> HLY_CDM SZ000401
>>> HLY_CDM SZ000539
>>> HLY_CDM SZ000601
>>
>>> So i want to calculate the daily return rate for each portfolio, the
>>> result would like this :
>>
>>> port_sym date grp_rt
>>> -----------------------------------
>>> HLY_BOPET 2012.03.02 0.0052
>>> HLY_CDM 2012.03.02 0.0078
>>> …
>>
>>> (the grp_rt is the average rt of the stock ‘sym’ belong to portfolio
>>> ‘port_sym’ in certain day ).
>>
>>> How to do that in q ?
>>
>>> Thanks for your help,
>>
>>> Halley
>>
>>> –
>>> You received this message because you are subscribed to the Google =
Groups “Kdb+ Personal Developers” group.
>>> To post to this group, send email to =
personal-kdbplus@googlegroups.com.
>>> To unsubscribe from this group, send email to =
personal-kdbplus+unsubscribe@googlegroups.com.
>>> For more options, visit this group =
athttp://groups.google.com/group/personal-kdbplus?hl=en.- =
=E9=9A=90=E8=97=8F=E8=A2=AB=E5=BC=95=E7=94=A8=E6=96=87=E5=AD=97 -
>>
>> - =E6=98=BE=E7=A4=BA=E5=BC=95=E7=94=A8=E7=9A=84=E6=96=87=E5=AD=97 -
>
> –
> You received this message because you are subscribed to the Google =
Groups “Kdb+ Personal Developers” group.
> To post to this group, send email to =
personal-kdbplus@googlegroups.com.
> To unsubscribe from this group, send email to =
personal-kdbplus+unsubscribe@googlegroups.com.
> For more options, visit this group at =
http://groups.google.com/group/personal-kdbplus?hl=en.
>
</and…>
Sorry, I amke a typo here, should be "stk_rt lj (sym xkey grp )", andthe result is not equal to your script.Your second solution works, takes about 9 seconds on my computer,Thanks..On 3?9?, ??5?15?, Bohák András <and...> wrote:> Hi,>> I don't understand this one:> I use " stk_rt lj (sym xkey port_sym ) " to replace " stk_rt lj> > select port_sym by sym from grp " , that generates same result but> > cost only 1/10 time.>> port_sym is a table too?>> I also use the 32-bit version, if memory is the bottleneck, something like this usually helps:>> raze {:() xkey select port_sym:x, avg rt by date from stk_rt where sym in (exec sym from grp where port_sym=x)} each exec distinct port_sym from grp>> This calculates the portfolio returns one-by-one, so as long as stk_rt fits in memory, this one should be able to run. It is a good idea to start q with -g 1.>> Andras>> 2012.03.08. dátummal, 3:49 id?pontban bigbug írta:>>>>>>>> > Thanks Andras.>> > I use " stk_rt lj (`sym xkey port_sym ) " to replace " stk_rt lj> > select port_sym by sym from grp " , that generates same result but> > cost only 1/10 time.>> > But I still can’t run on my 32b trial version KBD+ the line “select> > avg rt by port_sym, date from ungroup stk_rt lj select port_sym by sym> > from grp” for the memory explode.>> > (‘count stk_rt’ will have 5304782 rows, ‘count grp’ will have 15199> > rows ) . I think the ‘ungroup’ will creat a huge table that my 3G RAM> > machine can’t suffer .>> > Is there better way? Anyway, I just want a simple result table. I am> > OK for a little bit slow running script.>> > Thanks,> > Halley>> > On 3?7?, ??6?14?, Bohák András <and…> wrote:> >> select avg rt by port_sym, date from ungroup stk_rt lj select port_sym by sym from grp>> >> Andras>> >> 2012.03.07. dátummal, 10:11 id?pontban bigbug írta:>> >>> Hi, there,>> >>> How to calculate the portfolio return rate by group and date.>> >>> I have two table : one is daily stock return rate ‘stk_rt’ table, like> >>> this :>> >>> sym date rt> >>> --------------------------------> >>> SH600074 2012.03.02 0.00821917> >>> SH600074 2012.03.01 0.00274725> >>> SH600074 2012.02.29 -0.005464476> >>> SH600074 2012.02.28 -0.03430076> >>> SH600074 2012.02.27 -0.002631576> >>> SH600074 2012.02.24 0.02981027> >>> SH600074 2012.02.23 -0.00539083> >>> SH600074 2012.02.22 0.01366119>> >>> the other table is the portfolio ‘grp’ , like this :>> >>> port_sym sym> >>> ------------------> >>> HLY_BOPET SZ000859> >>> HLY_BOPET SZ000973> >>> HLY_BOPET SZ002263> >>> HLY_CDM SZ000155> >>> HLY_CDM SZ000401> >>> HLY_CDM SZ000539> >>> HLY_CDM SZ000601>> >>> So i want to calculate the daily return rate for each portfolio, the> >>> result would like this :>> >>> port_sym date grp_rt> >>> -----------------------------------> >>> HLY_BOPET 2012.03.02 0.0052> >>> HLY_CDM 2012.03.02 0.0078> >>> …>> >>> (the grp_rt is the average rt of the stock ‘sym’ belong to portfolio> >>> ‘port_sym’ in certain day ).>> >>> How to do that in q ?>> >>> Thanks for your help,>> >>> Halley>> >>> –> >>>
Submitted via Google Groups</and…></and…>