incrementing in cell

wa6aik

New member
Joined
Dec 11, 2013
Messages
12
Reaction score
0
Points
0
I have two numbers. if one goes up I want the other one to go up. If the first one goes down I want the second one to stay where it is. I cannot figure out how to do this please help me.
 

wa6aik

New member
Joined
Dec 11, 2013
Messages
12
Reaction score
0
Points
0
In what way are changes made to these numbers?
Thanks for looking at my problem.
The data in one cell comes from a stream provided by my broker. That data is the current price of a stock that I have selected. This is the ask price.
The second cell is the data that I want to send back to the broker. This my sell price. When the ask price stops going up and starts to go down I then want
to send the sell price to the broker. All of that is worked out except for the ask price, sell price interaction. I sure hope you can help me.
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
Not provided that you post links to all the other sites. Generally respondents are reluctant to work on this kind task when other websites are involved from the outset. For example I recently spent two full days working on a task involving business mergers and takeovers keeping the poster advised all the way, but he only came back after Id completed telling me he'd had a solution for a few days!
Your welcome to take a look if you like.

http://www.excelguru.ca/forums/show...pply-mutliple-search-criteria-in-huge-dataset
 

wa6aik

New member
Joined
Dec 11, 2013
Messages
12
Reaction score
0
Points
0
Wow I see your point. I don't want to exercise anyone. I am 80 yrs old and retired from 25yrs in R&D on computer hard drives. This is to supplement my pension. If you are not able to help me what should I do?
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
Based on what you have said, I would lean towards using Index/Match or Vlookup functions. My inital thoughts are you need three sets of tables
1) Your existing data
2) updated data
3) rebuild of existing data
Then you lookup new information in 2) relating to 1) and decide whether or not to change 1) based on comparing the values, or just copy across to 3). Thats a very "rough
and ready" assessment but it might give you somewhere to start.

Best of luck
 

wa6aik

New member
Joined
Dec 11, 2013
Messages
12
Reaction score
0
Points
0
Based on what you have said, I would lean towards using Index/Match or Vlookup functions. My inital thoughts are you need three sets of tables
1) Your existing data
2) updated data
3) rebuild of existing data
Then you lookup new information in 2) relating to 1) and decide whether or not to change 1) based on comparing the values, or just copy across to 3). Thats a very "rough
and ready" assessment but it might give you somewhere to start.

Best of luck
Thanks for the advice. I started as you said and stumbled at the just copy across to 3. I am trying to make this an automatic process with out keyboard input. Is this possible?
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
Thanks for the advice. I started as you said and stumbled at the just copy across to 3. I am trying to make this an automatic process with out keyboard input. Is this possible?

I didn't envisage using the keyboard apart from setting up formulae. I was thinking that you would have existing, Updated and Rebuild as 3 tables. The rebuild formula would either stick with existing, or accept the update
figure depending on which way the value had moved.
It was the stick with existing that I was referring to as "Copy Across" but doing it via the formula.
 
Last edited:

wa6aik

New member
Joined
Dec 11, 2013
Messages
12
Reaction score
0
Points
0
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 stream0.0008
existingdata=E8
highest=C5+C2
sell price0.001
trail price=MAX(D10:D610)
vlookup=VLOOKUP($C$2,A10:C610,1,TRUE)
Index=INDEX(A10:C610,MATCH(C2,A10:A610,4),2)
existing existingupdatedrebuildrebuild
0.00009A10=IF(B10=$E$8,A10,)=IF(B10=$E$8,A10,)=IF(B10=$E$8,A10,)
0.0001A11=IF(B11=$E$8,A11,)=IF(B11=$E$8,A11,)=IF(B11=$E$8,A11,)
0.0002A12=IF(B12=$E$8,A12,)=IF(B12=$E$8,A12,)=IF(B12=$E$8,A12,)
0.0003A13=IF(B13=$E$8,A13,)=IF(B13=$E$8,A13,)=IF(B13=$E$8,A13,)
0.0004A14=IF(B14=$E$8,A14,)=IF(B14=$E$8,A14,)=IF(B14=$E$8,A14,)
0.0005A15=IF(B15=$E$8,A15,)=IF(B15=$E$8,A15,)=IF(B15=$E$8,A15,)
0.0006A16=IF(B16=$E$8,A16,)=IF(B16=$E$8,A16,)=IF(B16=$E$8,A16,)
0.0007A17=IF(B17=$E$8,A17,)=IF(B17=$E$8,A17,)=IF(B17=$E$8,A17,)
0.0008A18=IF(B18=$E$8,A18,)=IF(B18=$E$8,A18,)=IF(B18=$E$8,A18,)
0.0009A19=IF(B19=$E$8,A19,)=IF(B19=$E$8,A19,)=IF(B19=$E$8,A19,)
0.001A20=IF(B20=$E$8,A20,)=IF(B20=$E$8,A20,)=IF(B20=$E$8,A20,)
0.0011A21=IF(B21=$E$8,A21,)=IF(B21=$E$8,A21,)=IF(B21=$E$8,A21,)
0.0012A22=IF(B22=$E$8,A22,)=IF(B22=$E$8,A22,)=IF(B22=$E$8,A22,)
0.0013A23=IF(B23=$E$8,A23,)=IF(B23=$E$8,A23,)=IF(B23=$E$8,A23,)
0.0014A24=IF(B24=$E$8,A24,)=IF(B24=$E$8,A24,)=IF(B24=$E$8,A24,)
0.0015A25=IF(B25=$E$8,A25,)=IF(B25=$E$8,A25,)=IF(B25=$E$8,A25,)
 

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
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.
 

wa6aik

New member
Joined
Dec 11, 2013
Messages
12
Reaction score
0
Points
0
no problems with the questions. I have put together a bunch of files to help.
 

Attachments

  • trailing stop3.pdf
    374.7 KB · Views: 8
  • trailing stop2.pdf
    369.9 KB · Views: 9
  • trailing stop 1.pdf
    402.7 KB · Views: 11
  • screen shot 2 days.jpg
    screen shot 2 days.jpg
    99.7 KB · Views: 8
  • Etrade test for trailing stops 4.xlsx
    32.9 KB · Views: 10
  • EXCEL copy of Etrade excel.xlsx
    11.9 KB · Views: 12
  • Excel work books and explanation sent to Excel guru.pdf
    198.8 KB · Views: 20

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
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
as your adding it to your selling price 0.001.
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.
 

Attachments

  • Etrade test for trailing stops 5.xlsx
    35.3 KB · Views: 24
Last edited:

wa6aik

New member
Joined
Dec 11, 2013
Messages
12
Reaction score
0
Points
0
I cannot open your PHP file, and I don't know why.
 

Attachments

  • etrade excel diagram.jpg
    etrade excel diagram.jpg
    60.7 KB · Views: 14
  • Etrade Excel answers 1.pdf
    156.3 KB · Views: 13

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
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.

Good luck with your endeavours
 

wa6aik

New member
Joined
Dec 11, 2013
Messages
12
Reaction score
0
Points
0
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 .
 

Attachments

  • Etrade test for trailing stops 4.xlsx
    18.7 KB · Views: 13

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
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 ?
 

wa6aik

New member
Joined
Dec 11, 2013
Messages
12
Reaction score
0
Points
0
I have uploaded the answer
 

Attachments

  • The spreadsheet actually tracks just one stock purchase.pdf
    154.1 KB · Views: 12

Hercules1946

New member
Joined
Mar 22, 2013
Messages
794
Reaction score
0
Points
0
Location
York, England
Excel Version(s)
2010
I have uploaded the answer

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
 
Top