Registered User
Joined: 5/2/2007
Posts: 59
|
Im looking for a way to write formulas in Microsoft excel where by i just go in and replace the current number or letter to give me what im looking for instantly, i've seen someone do it but i never bothered asking,last time i did some programming was on a casio calculator years ago and it had just integrations(ohhh........ how i loved maths)
Anyway say i don't wanna press the calculator buttons 80,000 times a night, can some one help on that?
i'll be honest i don't know much about excel but i know this guy he just punches in say close,high.....and voi la he got his entry point, target, RRR.... so when he goes into his trading platform he just enters those figures, the formulars are already stored in excel.and the beauty of it , you just change one figure or two every nite say to adjust your stop losses instead of re-calculating on a daily basis, i tried plotting them in telechart,or as pcfs, very possible, but for stock A you may enter .05 cents above say high, but for stock B it.25 so u gotta keep going back into the pcf and adjust those values.
|
|
Registered User
Joined: 12/21/2004
Posts: 902
|
excel is all about easily copyable formulas - say you put your data in columns A, B, and C, put your formula that uses that data in D and then copy the formula down the column. On each succeeding row, as you input your new data into A, B, and C, the results will automatically come up where you copied the formula to on D.
Thing is, you gotta know what you want to use the formulas for first. After that, it's just copying.
|
|
Registered User
Joined: 1/28/2005
Posts: 6,049
|
It sounds like you don't have exact formula's.
If every stock changes there is no way around it.
(if one is 5 cents above a high and one is 25
cents)
In excel you normally have a formula in a cell
and it addresses another cell. When you change the value it should update.
Although I must admit your question is not clear.
Thanks
diceman
|
|
Registered User
Joined: 9/22/2005
Posts: 849
|
My spreadsheet is Microsoft Works but it works about the same as Excel.
To expand on hohandy’s post a bit.
If you put 5 in cell A1, 3 in cell B1, 2 in cell C1, and the formula A1+B1+C1 in cell D1, then 10 will appear in cell D1.
If you then put 10 in cell A1, 15 immediately appears in cell D1.
You can highlight Column D for as many rows as you need, then on the Task Bar, click on Edit, then click Fill Down. This copies the formula into all of the rows you highlighted Column D for. The formula in cell D2 is A2+B2+C2, and so on down.
Now you are ready to enter data in all of the rows.
This is a very simplistic description of the process. You will want to do much more sophisticated things which will be limited only by your imagination and your knowledge of your spreadsheet procedures.
|
|
Registered User
Joined: 4/18/2005
Posts: 4,090
|
"i know this guy he just punches in say close,high.....and voi la he got his entry point, target, RRR.... so when he goes into his trading platform he just enters those figures"
I have set this up for my self.
these are the formulas I use. for going long I enter over a previous high.. either the previous day or the high of a consolidation, unless that high is way away .. then I MAY cheat and enter over the close. I use buy sto limit ranges for buy orders ... and stops .25 below a suport number I enter. My entry is tighter for cheaper stocks so it depends on price.
for for example I entered FALC on 8/23 by entering above previous cndle. The calculations were set up thus:
column A = ticker put in the row.
column B = buy stop price:
IF(E5<5,E5+0.06,IF(E5<10,E5+0.125,E5+.25)
Column c = buy limit price
B5+0.375
column D = Sell Stop price (market)
If(F5<5,F5-0.08,F5-0.25)
column e = Previous high or calculation high. Enter the high number here... what ever you use.
column f = suport low. enter the price by what ever criteria you use that you aren't interested in holding any more. Notice stop loss is .25 lower (except in cheap stocks)
Column G = Resistance or a target price you think likely to recieve.
column H = Risk (per share)
B5-D5
column I = Reward (per share)
G5-B5
column J = Ratio
I5/H5
column K = Risk to cost
H5/B5
skiping some maintenance cells
column V = # Shares .. enter the number you are thinking about (make this column "L" instead.
column M = dollar cost.
B5*L5
column N = total protfolio dollars
SUM(M5:M30).... unless you plan on holding more than 30 positions. I leave some stocks on the calculator if they are breakout entries untill its clear that they are going to fail, or favorites or what ever.
column 0 = total capital .. enter current liquidation amount.
column P = Margine... enter you margine amount OR the amount of margine you are comfortable using.
column Q = Cash margine over under
P5-O5
Column S = Cash Over under
Q5/2
Column T = Total dollar risk
H5*L5
Column U = risk to capital
T5/O5... I set the format of these to give me quick slap in the face also. I woften work late at night so if the Risk to reward is not acceptable to me it can be set to turn red, if it's marginal.. block out red if it's unacceptable.. or better yet block out the ticker in red if the R/R is bad. Then I cant accidentaly enter the order (don't ask)
Also i'd suggest making the risk to capital turn red if it's more than 2%. Mine is alwayse red but that i's becaseu i'm to lazy to change it and I generally alwasye check. Whenever I starting thinking grandiose and want to buy several thousand of XYZ I go ahead and put in the number of shares i'm thinking and then I see exactly what the dollar figure is I stand to loose if i'm wrong and what affect that will have in terms of percentages on my trading capital.
I hope this helps.
scott.
Oh.. all cell references that have a 5 in them are referencing the Row number. Rows 1-4 were eaten up with headings and titles and what not.
|
|
Registered User
Joined: 4/18/2005
Posts: 4,090
|
IF(E5<5,E5+0.06,IF(E5<10,E5+0.125,E5+.25)
this basically says if the price of the stock is less than five dollars enter .06 over the high, If less than 10.00 but greater than 5.00 enter .125 over the high and other wise enter .25 over the high..... or close or what ever. Adjust the formula to fit your needs.
|
|
Registered User
Joined: 4/18/2005
Posts: 4,090
|
There may be mistakes above... so I sent a ziped copy of my workbook to worden suport asking them to fwd it to you. This is just easier. you'll probably want to make some changes to suit your trading style.
|
|
Registered User
Joined: 1/28/2005
Posts: 6,049
|
There is an interesting example of using telechart
for position sizing by Tanstaafl.
(from an old Worden report)
The dates are
May,22,2003
May, 23 2003
They can be found here:
http://www.worden.com/training/default.aspx?g=posts&t=1514
Thanks
diceman
|
|
Registered User
Joined: 9/22/2005
Posts: 849
|
Scott
I would like a copy of the workbook als0. Would ask Wordens to send me a copy.
|
|
Registered User
Joined: 5/2/2007
Posts: 59
|
Scottnlena im yet to read your longest reply there.Thanks guys but really what im looking for is say you got
A
B
C
D,
they are all values and my formular(example) is
A(DC*1.6)-B*75= X... you get the point, i got a column for each i just come in and replace the letters with numbers and see what i want for X,i can enter as many values for each as i want to get different values for X, that sort of thing is what i meant. the other thing is i could Have say my Entry point,Stop loss, Limit, target....name it formulars all on one page so instead of calulating each separate for each stock(even those i don't intend on getting in but i wanna check if they are worth the risk) i just replace the A,B C,and D in our example above with real figures.
|
|
Registered User
Joined: 1/28/2005
Posts: 6,049
|
1btrader
I guess the questions would be.
What (exactly) are you trying to calculate?
What are you having trouble with?
You use values such as open, high, low,
close to calculate risk and somehow it is
not working?
Unless you can give us an example and
why it is not working. The question is
difficult to answer.
Thanks
diceman
|
|
Registered User
Joined: 4/18/2005
Posts: 4,090
|
1btrader:
those are the cell refference formulas.
|
|
Registered User
Joined: 4/18/2005
Posts: 4,090
|
Well sending this is a bit problematic. their spam filter kicks back my email and worden suport tells me to get the email address of the parties I wan't to send the work book to ... but how? We cant exchange emails here. Well for what it's worth I have a yahoo account that is the same as user name here.
drop me a line and I'll send it to those who are interested.
|
|
Registered User
Joined: 12/21/2004
Posts: 902
|
1b - per Diceman, it does sound like you're not giving us enough information as to what the exact problem is for us to help you.
It sounds to me that maybe you just really need some very basic Excel skills.
If that is the case - try this - go to cell C1 and put in the formula =a1+b1. In terms of Excel, what this is saying that whatever shows up in cell C1 will be the sum of whatever is in the cell 2 to the left, plus whatever is in the cell 1 to the left.
now put any combinations of numbers in a1 and b1 - whenever you change any of the numbers, what is in c1 will also change to reflect the new sum. You can do whatever you want to a1 and b1 - as long as you don't delete the formula in c1, it will always reflect the sum of a1 and b1. You can either work solely with those 3 cells, or copy the formula down to c2, c3, c4, etc. and input new numbers into A and B on those rows also.
Now move over to cell f1 and input the formula =d1*e1. And do the same drill, and f1 will always equal whatever is in d1 times whatever is in e1. You can put as many formulas on an Excel worksheet as you want, as long as the formula cells and the input cells don't interfere with other formula cells and input cells you won't have any problems (of course, you can get into complex formulas where the formula cell from one equation acts as an input cell to a second equation that automatically changes as the origninal data changes). But the thing to remember is that new formulas on the same worksheet will only affect the cells specifically referenced and won't affect already existing formulas in other cells.
|
|
Registered User
Joined: 4/18/2005
Posts: 4,090
|
1btrader and bcraig check your email accounts. Worden suport was kind enough to give me you're emails so I could send the sizeable file direct.
|
|
Registered User
Joined: 9/22/2005
Posts: 849
|
Scott
Igot your zipped spreadsheet. I don't have Excel and my Works can't open it, but thanks anyway
|
|
Registered User
Joined: 4/18/2005
Posts: 4,090
|
That sucks.... well hang on to it and see if you can get a copy of excel. IMO it's more powerful than other spreadsheets. I havea "borowed" copy of excel 2000
|
|
Registered User
Joined: 5/2/2007
Posts: 59
|
Scottnlena, thanks but that things you sent me(its an excel spreadsheet)i don't know whats behind all those number but it paralysed my little hard drive(120G), maybe its the processor,Do those figures update via the net or you go in and punch in the numbers? it looked like it was updating, also its very different from what i asked,now you gotta explain that too  .
Well i guess i'll put it this way, i'll post exactly what i meant after i slave away for 8hrs(its called employment),i guess i didn't wanna let the world in on how i do my calculations(there are Gurus waiting to dump their righteousness on me) but yeah i guess will.
|
|
Registered User
Joined: 4/18/2005
Posts: 4,090
|
Huh... sorry to hear you had the problem.
I'm running a 100 gig ram and similar hard drive. It's a bit sizeable but there is nothing automatic there. I ran my norton before attaching it so there shouldn't be anything in it in terms of buggies.
If you get it open you'll click on long calculation page. In column cells A type the ticker then skip to input previous days high low and resistance, those are the only squares in that section that are modifiable... clicking any of the others will display the formula in the bar above. There is also a way to make the cells show thier formulas but I for get how. Most are long and would be blocked from view anyway.
The next segment is a maintenance section... input updated closed here and any changes to suport while holding the trade and it gives you the new stop loss and distance to target.
beyond that is money management. you'll notice that the tickers carry forweard .... just amkes it a bit easier but that column isn't necessary. enter the number of shares you are thinking therer and enter the amount of your net liquidation in "total capital" and any margine you want to use in the "cap+Marg/ buying power" cell. Now when you enter the quantity of shares you are thinking it will tell you at the endof the sheet what the total dollars at risk for each trade and the summed amount, plus each as a percnetage of your cash capital base. "total portfolio dollars" is a sum of capital in all positions considered or in.
The short calculator works the same. they are not connected so the shorts calculator wont figure in the affects of other positions on the long page. also you have two choices of how to give suport.... by calculating the swing high OR entering a candle high or what ever. the rest is the same.
The diary page is just a place to log your trades and some strategic thoughts giong in and coming out and finaly a month later or so. I also have a section where I have a quick coment on indicators at the time of analysis and entry. Later I'll create other charts to track which combination are pressent in my most sucessfull trades.... and focus on those only. those may not beindicators that you use... clear them out and put the ones you like.
I would recomend keeping the parts on strategic notes and the trade record portions.
I got the idea for Dr. Alexander Elders "Entries and Exits" excellent book for beginning taders by the way. you can buy his version for several hundred dollars or I just gave you mine.
|
|
Registered User
Joined: 5/2/2007
Posts: 59
|
Thanks scott, its finally coming all together.
|
|
Registered User
Joined: 4/18/2005
Posts: 4,090
|
Glad to hear it.  if you take your time and look through it you should find the logic behind it.
if you didn't want to enter off the high but off the close then just enter the close price in the "high" box and it will work the same.... but with a bit tighter entry. I sometimes do this if whicks are verry long.
|
|
Registered User
Joined: 2/26/2007
Posts: 4
|
Hi Scott,
Just sent you an e-mail at the yahoo address.
if you would please send me a copy of the work book also. I too have tried several formulas and so far no luck in what I want.
Thanks
Dan
|
|
Registered User
Joined: 4/18/2005
Posts: 4,090
|
What is it that you want?
I thought about making another page for other entry strategies.. like entering below the close by way of a limit order.... but I wouldn't use this personaly.. it seems to me that more than i've been screwed by entering above current price i've been saved from a bad pick becaseu the order never filled.
|
|
|
Guest-1 |