Exel Help Please...

I have a excel sheet with a bunch of trades entered on it....

Each row has a trade and each component of the trade is in its own column.

I want to sort the trades into 2 sections of Listed and Nasdaq (3 letters in the symbol or less = listed)

Anyone have a method to sort the trades this way??

Thanks for the help...

 

i don't know if this can help you, but maybe use LEN somehow.

for example: =IF(LEN(A1)=3, do something, if not do something else)

 

Your a life saver Mr. Gecko!

But I have another one for you...

Can I make a if statement that adds fill color to a cell or series of cells.

e.g =IF(A1>10,000,Fill Color B1 red, Fill Color B1 blue)

 

Use Conditional Formatting in Format.

 

probably.. try what Mr Sub. said

 

I could not get a whole row to highlight with conditional format, but I got exactly what I need.

As always ET is a wealth of info! Thanks to the both of you!

 

I receive in one cell in exel real time data and i want to sum that data in other cell. My english is bad so i will try with example:
In A1 i receive different numbers and i want to sum A1 value + previous A1 value in A2 . and by end of the trading day in A2 i want to have cumulative value of A1.
I hope u understand my english,
thx a lot

 

You cannot replace the value in A1 all day long. You should have each new value written in the cell below the prior number, then sum all of them at the bottom.

 

I have found that using the Excel help tool is incredibly useful. Just type in the question you have and a few trials you'll almost always get the answer you desire and/or better still learn some more.

 

Kamburov:

yeah you can do that. I do exactly what you are describing in keeping track of VWAP in excel. This means I have cells (Price and size) that constantly update in real-time through a dde link and keep a cumulative total. As an example, let's say you want to keep a running total of volume.

First, you need to be able to recognize when the field has been updated. For this I use the trade time as the trigger - if you used the volume field itself, 2 consecutive trades of 100 shares would not register as a difference.

To do this, assume the trade time is updating in cell A1.

In cell A2: If(Reset=0,0,if(A1<>B2,2,0)

The reset referred to is a named cell that is set to either 0 or 1 manually. If it's set to zero, this stops excel looping.

The cell B1 is where the previous value of A1 is held.

So, if the value in A1 has changed, A2 will equal 2, otherwise 0.

In cell A3: If(reset=0,0,min(A2,A3+1)

This is a circular reference so excel will give you dire warnings about this. Ignore them. Ensure that iteration is enabled under Tools, Options, Calculation otherwise it won't work. If you are doing this real time, set iterations to a low number (I use 5) instead of 100 to cut down on possible loops.

This will set the value of A3 to the lower of A2 (which will be either 0 or 2) and thecurrent value in A3 + 1. If the current value is 0, it will change to 1; if 1 to 2. It will change back to 0 when the value in a2 reverts to 0.

This basically has set up a loop based on a change in the value of cell A1.

Next, we use the changing values of A3 to copy the new value in A1.

So, in B1: if(reset=0,0,if(A3=1,A1,B2))

When the value in A1 has changed, the loop will trigger and A3 will be set to 1. When this is so, copy the value in A1 to B1, otherwise leave it as it is. Again, this is a circulkar reference.

In B2, we will terminate the loop:

If(A3=2,B1,B2)

This copies the value from B1 into B2 when the loop in A3 increments from 1 to 2. By copying the new value of A1 into B1 and then into B2, now the loop is terminated (see the comparison in A2 between A1 and B2).

Now, using this looping structure, you can track changes in a real-time dde link and can do whatever you like. Like I say, I track VWAP for example. There is a caveat though: when you enter stuff in excel manually, it stops updating the dde link while you are entering data, so don't do it or you will miss ticks. Use another pc with excel to actually enter stuff while the dde link is active.


BTW, I am not the genius who thought this method up. I got it from a MrEXcel.com link to a guy called Stephen Bullen in Uk who had some example spreadsheets which i can't find anymore on my pc.