Results 1 to 11 of 11
  1. #1
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Formula for Stocks (Excel 2003)

    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.
    Legare Coleman

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Formula for Stocks (Excel 2003)

    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

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Excel Formula for Stocks (Excel 2003)

    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

  4. #4
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Excel Formula for Stocks (Excel 2003)

    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

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Formula for Stocks (Excel 2003)

    =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

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Formula for Stocks (Excel 2003)

    Then I think you want a formula something like:

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


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

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Excel Formula for Stocks (Excel 2003)

    Legare:
    Many thanks, this is terriffic
    Jerome

  8. #8
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Excel Formula for Stocks (Excel 2003)

    Steve:
    Many thanks this is wonderful
    Jerome

  9. #9
    Lounger
    Join Date
    Feb 2004
    Location
    Buffalo, New York, Wales
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Formula for Stocks (Excel 2003)

    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

  10. #10
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Re: Excel Formula for Stocks (Excel 2003)

    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

  11. #11
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    NY, NY, USA
    Posts
    654
    Thanks
    10
    Thanked 0 Times in 0 Posts

    Excel Formula for Stocks (Excel 2003)

    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

Posting Permissions

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