Re: Need some tips on query generating averages on time series data

Hello Lennart:

Thank you for attempting.

Here is my calculation of hold times and wted-avg hold times:

time	 qnt	cumul   hold_time  held-qnt  wted-hold-time	
12459.6	-100	-100	    174.4	100           17440	
12464.8	-100	-200	    171.4	100	      17140	
12568.3	-100	-300	     67.9	100	       6790	
12568.3	-200	-500	    109.7	200	      21940	
12634	 100	-400				
12636.2	 100	-300				
12636.2	 100	-200				
12678	 200	   0				
				         totalqnt  total-w-h-t wted-avg-hold-time
				              500        63310	      126.62

As you can see, I had to break down some trades into smaller quantities in order to match the buy-sell and come up with the holdtime.

And as you can see, the order of first in first out is important to the calc. If I were to use LIFO, my numbers would be slightly different.

If you need Oracle access, please send me your email address at ecotaj at gmail dot com.

_at_Peter: you make an assumption that I was the guy that lost $5B at JPM ;-) in a single day of trading.

On Wednesday, October 23, 2013 1:12:33 AM UTC-5, Lennart Jonsson wrote:
>
> I'm not entirely sure how you define hold time for a stock, is it per
> share? Otherwise why is rule 4 necessary? Can you provide the expected
> result for the sample data above?
>
> I don't have access to an Oracle instance so I ran on a competitor:
> with T(stock, ts, qnt) as (
>
> values ('GOOG', 12459.6, -100)
> , ('GOOG', 12634.0, 100)
> , ('GOOG', 12636.2, 200)
> , ('GOOG', 12464.8, -100)
> , ('GOOG', 12568.3, -300)
> , ('GOOG', 12678.0, 200)
> )
>
> select stock, ts, qnt, sum(qnt) over (partition by stock
> order by ts) as cumulative_sum from t;
>
> What is the average hold times above?
>
> /Lennart
Received on Thu Oct 24 2013 - 06:02:29 CEST