Help with stock data calculation in excel

Appreciate any help I can get!

Cross-posted at https://www.mrexcel.com/board/thread...excel.1198254/ --6SJ

Hope this clears up any confusion!

Here is the data.

Please ask a question in your post rather than just showing data. We don't know what your problem is.

Also:

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

I added it for you this time because you are a new member. But please take the time to review our rules. There aren't many, and they are all important.

In what ways is this different from yesterday's thread https://www.excelforum.com/excel-for...d-formula.html

If I assume only one "bounce", I could do something like =MAX(A2:A5)-MIN(C2:C8), but that is simply the difference between global max and global min, there is no signal processing to locate "bounces".

My answer is still the same I gave yesterday, this feels like a signal processing problem. Help us understand exactly how you want to process the signal so you can identify "bounces", and we should be able to help you program that algorithm into the spreadsheet.

Originally Posted by MrShorty

In what ways is this different from yesterday's thread https://www.excelforum.com/excel-gen...-in-excel.html

The link is back to this same thread.

Hello, MrShorty thank you for helping me with this. I was able to make progress on the problem from yesterday's post, but I'm still not fully there.

I was able to combine the who columns of time into one column and find the bounces using IF test. But I think there is a better way to do this. I still need to be able to calculate the difference between the true low of the bounce and the high of the bounce for every bounce, as well as get the time of the low of the bounce and time of high of the bounce.

I have included a picture of the stock the sample data is taken from. You see what I'm trying to do is only get bounces after the stock has performed its high of the day. I want all data for the bounces until it hits the low of the day.

I was testing your idea of using sign() to get the local min and local max but i'm unsure how to use this further to get the true low and true high of the bounces.

I have included the latest spreadsheet.

Also the values in the picture are only roughly the same but you get the idea.

graph.jpg

I was testing your idea of using sign() to get the local min and local max but i'm unsure how to use this further to get the true low and true high of the bounces.

As I tried to explain yesterday, the simple SIGN() function logic is only the simplest logic and usually requires further elaboration. I'm not sure, either, how you are identifying bounces. As I tried to suggest yesterday, a big part of programming signal processing algorithms is developing the logic into something programmable.

Looking at your picture and the max/min points you have highlighted, a few questions I would start asking myself:

1) You've highlighted minima at 11:00, 2.9 and 12:00, 2.64 (a complete bounce??) Why did you not highlight the minimum at about 11:30 and use that as the end of the bounce?
2) You've highlighted a maximum at 14:15, 2.9. Why did you not highlight the adjacent maxima at 13:50 and 14:30?
3) Assuming the 13:35 -- 14:15 -- 14:45 is a "bounce", why only one bounce here and not three?

This is often the hardest part of developing signal processing algorithms -- the actual algorithm part before you worry about which programming language you want to use and how to express that algorithm in the chosen programming language.

@6stringjazzer: I edited my post to get the correct link (I hope). Not sure how I made that mistake.

To get the local maxima and min in the spreadsheet what I did was to compare the current high with the next 15 highes and previous 15 highes. If the current high is greater than both the previous 15 high and the next 15 high, I keep it. If you have any smarter ways of doing this, please let me know.

First, being neither and expert in signal processing nor an expert in stock analysis, I am not going to have a smarter way of doing this. Someone with greater expertise in those fields will need to step forward in order to critique your decision criteria, because I have no expertise to bring to those kinds of questions.

So far, you are looking for
1)something that changes more than 5% (relative percent changes like this require two values -- will it be easier to establish an absolute change criteria rather than a relative change? Say something like "most values are between 2.5 and 3, so 5% of 2.75 is 0.14, so we will look for an absolute change of 0.14 rather than a relative change of 5%"? Sometimes I think the programming is easier if I think in absolute rather than relative terms).
and 2) something that takes a certain minimum time to occur (something on the order of 30 minutes).

Is that enough information to do the programming? Not sure, going to need to think on it...