General Topics
Markets
Technical Topics
Brokerage Firms
Community Lounge
Site Support

# Excel help

Discussion in 'Automated Trading' started by billp, Oct 12, 2006.

1. ### billp

Can someone help with the below? Thanks.

I am familiar with pivot tables and like to use it in Excel to do my computations. The problem is whenever I run it now, the Excel says that it has run into problems and I have to close it.
Does anyone know what's the problem here?

Also, I would like to know is there any way I can do the below in Excel as now I can't use pivot table to do these now:

I download my time and sales into Excel. What I need to do is :
1) Sum all the shares for a particular price (irregardless of time)

2) Currently I am using the 'subtotal function' to sum all shares for a particular price that occurs within around the same price. The problem is I also want to sum and do the average number of shares for each 50 subtotal price levels. What Excel functions can I use to do the below? I will provide an explanation below :

10am \$40 = 1000 shares
10.01am \$40 = 2500 shares
Subtotal \$40 = 3500 shares

10.01am \$40.01 = 500 shares
10.01 am \$40.01= 800 shares
10.02 am \$40.01 = 700 shares
Subtotal \$40.01 = 2000 shares

10.03am \$40.03 = 2500 shares
10.04am \$40.03= 500 shares
Subtotal \$40.03 = 3000 shares

Thus, for example if I'm interested in the sum and average for 3 price levels, the calculation will be :

Sum = 3500 + 2500 + 3000 = 8500 shares
Average= 8500/3= 2833 shares

Appreciate some help here. Thanks

2. ### swandro

If you put the subtotals into a separate column you can then put a total at the bottom of that column. It does not matter if there are blank cells in the range. Excel will ignore them.

As for you problem with Excel crashing out, it is difficult to say what is happening, without more information. But what you could do is remove the pivot tables and then recreate them. That might fix it.

Hope this helps

Rob

3. ### billp

Thanks for sharing. However, the subtotal is not in a separate column as this is the way Excel subtotal function works. For pivot tables, the Excel just crash before the pivot table can be generated. I've run pivot tables many times in other Excel version and I suspect that most likely this Excel version which I have must have some bugs in it.

Anway managed to find a solution to this although a much more tedious one ie breaking up the cell .

Thanks once again

4. ### teun

Just use array functions. There's not 1 thing which you can not solve with them.

5. ### billp

Thanks. Will look into it as have never used them before.

ET IS FREE BECAUSE OF THE FINANCIAL SUPPORT FROM THESE COMPANIES: