Excel Formula for Stocks (Excel 2003)

Topic

New Reply

What is in column K? Is it a number with a custom format that puts the ” YH” after it, or is it a text value with the YH typed into the cell? Attaching that actual workbook would be a big help to those trying to answer your question.

Reply | Quote

Replies

Legare:
Col K draws in the year highs for the stocks from a downloaded spreadsheet. I append the “YH” as a text to the number so I know what it means for instance 39YH means that the stock had a year high of 39 and 39 is a number
Jerome

Reply | Quote

Then I think you want a formula something like:

=IF(ISERROR(E3),"",IF(E3-VALUE(TRIM(LEFT(K3,LEN(K3)-2)))>=-1,"$$$",""))

However, it would make things much easier if you put the YH (and other designations) in separate columns.

Reply | Quote

Then I think you want a formula something like:

=IF(ISERROR(E3),"",IF(E3-VALUE(TRIM(LEFT(K3,LEN(K3)-2)))>=-1,"$$$",""))

However, it would make things much easier if you put the YH (and other designations) in separate columns.

Reply | Quote

Legare:
Many thanks, this is terriffic
Jerome

Reply | Quote

Legare:
Many thanks, this is terriffic
Jerome

Reply | Quote

Legare:
Col K draws in the year highs for the stocks from a downloaded spreadsheet. I append the “YH” as a text to the number so I know what it means for instance 39YH means that the stock had a year high of 39 and 39 is a number
Jerome

Reply | Quote

Extremely wide picture that caused horizontal scrolling reduced in size by HansV; in the future please attach a sample workbook instead of a huge picture of one.

I would like to have column M say on the attached print screen spreadsheet to reflect when a stock is near its high graphically. Any suggestions would be appreciated.
For example IF K5, The year High, – E5, the current price, is > or = – 1 or 0 or whatever number makes sense then Column M might have 2 red ** or 2 red $$ or something else interesting. So can scan a spreadsheet quickly for stocks approaching their 52 week highs.
Thanks
Jerome

Reply | Quote

You could add in M1 the Target percent (eg 80%)
and put something like this in M2 and copy it down
=IF(E2/K2>$M$1,”$$”,””)
and copy it down the rows.

Then when the value was >80% of the max it would display “$$”

Change the percent as desired to match the required sensitivity.

Steve

Reply | Quote

Steve:
That will work if I can get it to ignore n/a values for new stocks that do not have 52 week high values recorded yet in the yahoo database. ie can it not give a #value error for n/a values in K.?
Jerome

Reply | Quote

=if(isna(k2),””,IF(E2/K2>$M$1,”$$”,””))

If you add text to col K, it will not work. You must keep K a number (or put the number into another column and reference that.) I asumed it was custom formatting.

Steve

Reply | Quote

Steve:
Many thanks this is wonderful
Jerome

Reply | Quote

Steve:
Many thanks this is wonderful
Jerome

Reply | Quote

=if(isna(k2),””,IF(E2/K2>$M$1,”$$”,””))

If you add text to col K, it will not work. You must keep K a number (or put the number into another column and reference that.) I asumed it was custom formatting.

Steve

Reply | Quote

Steve:
That will work if I can get it to ignore n/a values for new stocks that do not have 52 week high values recorded yet in the yahoo database. ie can it not give a #value error for n/a values in K.?
Jerome

Reply | Quote

I was hoping you were going to post a copy of your Stock Spreadsheet so everyone at Woody’s could use it to make some extra money. Not even a stock tip…………wheres the appreciation?

yoyophil

Reply | Quote

I wish I was making money, only the oil stocks seem to be approaching highs.
The photo of the spreadsheet kind of explains it all
Jerome

Reply | Quote

I wish I was making money, only the oil stocks seem to be approaching highs.
The photo of the spreadsheet kind of explains it all
Jerome

Reply | Quote

I was hoping you were going to post a copy of your Stock Spreadsheet so everyone at Woody’s could use it to make some extra money. Not even a stock tip…………wheres the appreciation?

yoyophil

Reply | Quote

Extremely wide picture that caused horizontal scrolling reduced in size by HansV; in the future please attach a sample workbook instead of a huge picture of one.

I would like to have column M say on the attached print screen spreadsheet to reflect when a stock is near its high graphically. Any suggestions would be appreciated.
For example IF K5, The year High, – E5, the current price, is > or = – 1 or 0 or whatever number makes sense then Column M might have 2 red ** or 2 red $$ or something else interesting. So can scan a spreadsheet quickly for stocks approaching their 52 week highs.
Thanks
Jerome

Reply | Quote