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

  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 :D.

    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.