Algo help needed to find if a stock moved greater than 175% within a 5 day period over one year time

[sanfrancisco]

Digging into Stock enhancement with Excel and GoogleFinance within Sheets and looking for some help to find the most efficient way to build a formula that will take a specific ticker (currently have ~500) and I want to get a BOOL type of response that confirms if the stock had a run up (spiked) 250% or more within a 5 day period.

This could be a one day spike or a multiple day combined price jump up to 5 days where the spike from start to the top tick highest point was 175% or more. Here's the criteria I'm working with and some example tickers.

• Daily Chart
• 1 Year time frame, do not want to look at data before 2023 essentially
• Function I want to build will be one specific cell within the row for that ticker to allow confirmation and further analysis/algo's to proceed if return is true.
• Sasi, Tpst, Cccc, Abvc

Tried to work with the 52 week high capability but that only seems to find a specific point in time and not loop through the price to identify.

 

[offthelip]

I think the easiest way to do that is to use a UDF, this code will do itfor you:

Function jumpfound(rar As Range)
rr = rar.Value
Jlim = 1.75
For i = 5 To UBound(rr, 1)
  maxv = 0
  minv = (2 ^ 31) - 1
  jumpfound = False
  For j = 0 To 4
       If minv > rr(i - j, 1) Then minv = rr(i - j, 1)
   If maxv < rr(i - j, 1) Then maxv = rr(i - j, 1)
   If ((maxv - minv) / minv) > Jlim Then
    jumpfound = True
    Exit For
   End If
  Next j
  If jumpfound Then Exit For
  Next i
 
End Function

Put the code in a standard module in the workbook where you want to use it
to use it you use it just like a normal function. e.g:

=jumpfound(B2:B33)

Set the value in Jlim to jump you want to detect

 

[sanfrancisco]

I think the easiest way to do that is to use a UDF, this code will do itfor you:

Function jumpfound(rar As Range)
rr = rar.Value
Jlim = 1.75
For i = 5 To UBound(rr, 1)
  maxv = 0
  minv = (2 ^ 31) - 1
  jumpfound = False
  For j = 0 To 4
       If minv > rr(i - j, 1) Then minv = rr(i - j, 1)
   If maxv < rr(i - j, 1) Then maxv = rr(i - j, 1)
   If ((maxv - minv) / minv) > Jlim Then
    jumpfound = True
    Exit For
   End If
  Next j
  If jumpfound Then Exit For
  Next i
 
End Function

Put the code in a standard module in the workbook where you want to use it
to use it you use it just like a normal function. e.g:

=jumpfound(B2:B33)

Set the value in Jlim to jump you want to detect

So I guess the harder part is where is this pulling the data from? I'm using =GoogleFinance() function to pull in the data but not sure how to make this work?

For instance, pulling the company name is easy via =GOOGLEFINANCE(A2,"NAME")

But how to tie in your formula with the sheet, see attached..

 

[offthelip]

Where is the price data for the year?? My UDF relies on price data gong down in one column, it could be changed to have the price all in one row, but it relies on selecting a range with all the prices. Note I have never used google finance so I don't know what it does

 

[sanfrancisco]

There is a way to pull that data with the =GoogleFinance(ticker,all,startDate,endDate,"Daily"). That essentially can pull all the information for the information for the specific date and these return values:

Open / High / Low / Close / Volume

 

[offthelip]

If you get your prices in a column in an excel spreadsheet then you can use the function I have written detect the condition you have asked for. How to get your prices from google is a different problem which I have no experience of. You might be best to ask another question specifically about that. Note this forum is the EXCEL forum which is why I tried to answer it. If your question is about google sheets then it should be in the General Discussion & Other Applications forum

​