Here is what I came up with: The data in the rebuild columns is erased when the data stream updates. So I cannot compare to find the largest number.
 data stream 0.0008 existing data =E8 highest =C5+C2 sell price 0.001 trail price =MAX(D10 610) vlookup =VLOOKUP(\$C\$2,A10:C610,1,TRUE) Index =INDEX(A10:C610,MATCH(C2,A10:A610,4),2) existing existing updated rebuild rebuild 0.00009 A10 =IF(B10=\$E\$8,A10,) =IF(B10=\$E\$8,A10,) =IF(B10=\$E\$8,A10,) 0.0001 A11 =IF(B11=\$E\$8,A11,) =IF(B11=\$E\$8,A11,) =IF(B11=\$E\$8,A11,) 0.0002 A12 =IF(B12=\$E\$8,A12,) =IF(B12=\$E\$8,A12,) =IF(B12=\$E\$8,A12,) 0.0003 A13 =IF(B13=\$E\$8,A13,) =IF(B13=\$E\$8,A13,) =IF(B13=\$E\$8,A13,) 0.0004 A14 =IF(B14=\$E\$8,A14,) =IF(B14=\$E\$8,A14,) =IF(B14=\$E\$8,A14,) 0.0005 A15 =IF(B15=\$E\$8,A15,) =IF(B15=\$E\$8,A15,) =IF(B15=\$E\$8,A15,) 0.0006 A16 =IF(B16=\$E\$8,A16,) =IF(B16=\$E\$8,A16,) =IF(B16=\$E\$8,A16,) 0.0007 A17 =IF(B17=\$E\$8,A17,) =IF(B17=\$E\$8,A17,) =IF(B17=\$E\$8,A17,) 0.0008 A18 =IF(B18=\$E\$8,A18,) =IF(B18=\$E\$8,A18,) =IF(B18=\$E\$8,A18,) 0.0009 A19 =IF(B19=\$E\$8,A19,) =IF(B19=\$E\$8,A19,) =IF(B19=\$E\$8,A19,) 0.001 A20 =IF(B20=\$E\$8,A20,) =IF(B20=\$E\$8,A20,) =IF(B20=\$E\$8,A20,) 0.0011 A21 =IF(B21=\$E\$8,A21,) =IF(B21=\$E\$8,A21,) =IF(B21=\$E\$8,A21,) 0.0012 A22 =IF(B22=\$E\$8,A22,) =IF(B22=\$E\$8,A22,) =IF(B22=\$E\$8,A22,) 0.0013 A23 =IF(B23=\$E\$8,A23,) =IF(B23=\$E\$8,A23,) =IF(B23=\$E\$8,A23,) 0.0014 A24 =IF(B24=\$E\$8,A24,) =IF(B24=\$E\$8,A24,) =IF(B24=\$E\$8,A24,) 0.0015 A25 =IF(B25=\$E\$8,A25,) =IF(B25=\$E\$8,A25,) =IF(B25=\$E\$8,A25,)  Reply With Quote

2. Hello Wa
Im afraid that I can't glean much from your last post because there is just too much unformatted data. This is due to this software being unable to give a tabular
structure because of the text limitations. However, this website will allow you to upload a workbook, so if you create some sample data, I can at least see a proper
column structure and what the cell formulae are referring to.
At the moment, you just need to convey how the data you get is handled, and what decisions are being made from it, so you dont need a lot of data. You could probably
do it with a short history of one stock or commodity or whatever. Once I get my head round this and a few other issues I will be able to advise you better.
Questions:
1. You talk about a data stream update from the broker. Is this a full schedule of prices on another spreadsheet, or do they arrive piecemeal as individual records?
2. Do they "hit" your system automatically, or can you control/collate data before presenting it to your systems?
3. How often do you get updates ?
4 You have an ask price and your sell price. If the ask price goes up, so does the sell price. By the same amount ? By the same %?
5 When the ask price falls, you don't drop your sell price, but you send detail to the broker ? Is that as soon as it falls, or are there other factors ?

Im sorry about all the questions but I like to get a proper understanding otherwise I dont think I will be able to give the best help.  Reply With Quote

3. no problems with the questions. I have put together a bunch of files to help.  Reply With Quote

4. Wa
Thanks for the information which has helped me somewhat. Im going to concentrate on the trailing stops worksheet, which contains the data your working with.
If Im understanding correctly, this is part of a workbook into which you extract the streamed data from another sheet/workbook that is integrated with the Etrade system?
If the data is streaming by the minute, how do you keep up with the extraction process?
It does sound as though this is all about organising the data properly and then the calculations should be straightforward.
However, Im struggling to interprete your data.
1. You have a watch list of 601 stocks with existing prices starting in A10, with names /labels starting im B10.
2. You have Col C to enter updated prices, and Col D to build an amended list.
3. From the stream you have received a price adjustment of +0.0008. Ive assumed its an adjustment and not a price
4. You've matched this to stock code A18, which works in this case as you only have one stock with this price.
5. You have calculated the adjustment into the correct row of the update column (C) and it remains to recompute the prices
in the rebuild column (D) which I have done. The formula in D will carry across the existing price unless the amended
price has increased.
6. Most of the formulae that you have entered in D are invalid as (eg) in Cell D32 the formula evaluates to D32, which is a recursive calculation.
7.This method will work if your dealing with a small number of price adjustments at a time, but you may need a more programed approach for
a large number.

I would suggest that you need a unique key such as a stock identifier for your lookups as with 600 items, you cannot guarantee that all the prices will be different.

I look forward to your comments and hope we're going in the right direction.  Reply With Quote

5. I cannot open your PHP file, and I don't know why.  Reply With Quote

6. Hello Wa
Im not sure what you mean by a php file. Are you saying you cant open my .xlsx attachment in post 14?
I can appreciate that the data is for testing purposes rather than being the real thing, but its all your giving me to work with. The errors, disorganisation and the lack
of explanation of the contents mean that so far I am unable to work out what I need to do to help you, even though I have a basic understanding of what your trying to do.
It may be that your best way forward is to seek assistance from someone familiar with the Etrade package who has faced similar problems and developed a
solution of their own that they can share with you. I don't like admitting defeat, but I don't think that Im going to get a sufficient grasp of this by exchanging forum messages, unless
you can give me a spreadsheet that conveys what you need and that I can understand.  Reply With Quote

7. Thank you for your efforts. I know this is confusing but I can not find anyone who has tried this. I hae uploaded a xlsx file showing what I want he sheet to look like .  Reply With Quote

8. Originally Posted by wa6aik Thank you for your efforts. I know this is confusing but I can not find anyone who has tried this. I hae uploaded a xlsx file showing what I want he sheet to look like .
I wouldn't use the word confusing, probably meaningless as many of my working spreadsheets would be to others!
After reading your pdf in post #15, its clear that I'd taken an entirely wrong view of the Stops 4 spreadsheet. Let me try again, and PLEASE CORRECT ANYTHING I GET WRONG
1. The spreadsheet actually tracks just one stock purchase, rather than 601 as I thought.
2. The table of data in cols A and B represent A - Listing of possible prices for a unit purchase of the stock item in question; B - A corresponding list of "identity codes" for the items in A.
3. So you purchase XYZ Stock thats on a rising price cycle at 0.0002, so it jumps into the chart at A12.
4. As time progresses, it rises in price ....(A) 14,16,19 and 21.
5. But then it drops a place in the chart ..... A20. (Bells sound, Action required)

So why can't you simply have a warning flag, or an immediate sale if you like, when it first moves down the chart ?  Reply With Quote  Reply With Quote

10. Originally Posted by wa6aik Wa
Thats good news, as it brings me a few steps closer to being able to help you by starting the design of a solution. I still have a few questions for you
before I start that process, as I want to be confident that it will deliver.
Check your personal messages in 10-15 minutes  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•