(how-do-i) produce a stock change movement formula from this...

[newton112]

how do i produce a formula that performs what a stock change movement 'up' or 'down' will do?
i.e. stock price is $10 in the initial instance; stock price moves to $12 in the next instance reflecting a +2 value in change. I want to write a formula that will juxtapose two different instances of the same given cell.
I tried this:
=IF(E3 < E3,"DOWN", "UP")
There is an obvious complication in this formula, and so i present it only to demonstrate from this point how i aim to alter it.
The first instance of E3, lets call it '1st E3' for simplicity, is meant to be a stock price change at that moment. However, in the next moment, '2nd E3', the excel sheet updates (every minute) and one three things will happen. Either the change will remain the same, or it will increase or decrease. Suppose it increases. Consequently the '2nd E3' will reflect this change increase.
So my goal is to write a formula, and I was hoping for an =IF() formula since I am more familiar with it, that can distinguish a particular cell at once instance from that same cell at a later instance where the later instance reflects a change in the excel cell-value (upon a refresh of the document).
I hope i was clear. please get back to me as soon as you could. I consider myself a beginner at excel so try to be explicitly clear and concise with baby steps and language (or try to explain any technical jargon that might be unknown to me) in your explanation please. Thank you

 

[cyrilbrd]

Hi, just being curious, you mentioned only two sets of data, how is the data added? Does it add below the latest entries, thus making a column that will grow in time?
Are you interested only in the latest entries or would you want to display the evolution of the market thru a graph?
Thus may help others giving you best advise as well.

 

[newton112]

Hi, just being curious, you mentioned only two sets of data, how is the data added? Does it add below the latest entries, thus making a column that will grow in time?
Are you interested only in the latest entries or would you want to display the evolution of the market thru a graph?
Thus may help others giving you best advise as well.

Hey, I appreciate the question. I'm only interested in the latest entries for purposes here. However, I hadn't considered the second consideration. Do you know how to do that? If so, I'd still want to know how to have the latest entries save into a column and progressively evolve.

 

[newton112]

Re-explaining the Question.
For anyone reading, I want to reattempt explaining this a bit better.

The data to be added occurs by successive updates to the excel sheet which is streamed from a real-time stock quote site (yahoo finance portfolio).

It was imported by going to: Data/Import External Data/New Database Query/[web URL of stock site]. The whole webpage is imported into one worksheet. On a different worksheet I linked (via. '=' cell by cell) each important piece of data (i.e. "dividend price", "4.64", etc.). So I linked important info out of worksheet 1 and into worksheet 2 where I designed a portfolio table. I linked them as opposed to a simple 'copy/paste' function since the former reflects the updates in worksheet 2, whereas the latter doesn't. This thread concerns worksheet 2's portfolio and its continuously updated information.

So say cell A5 contains '0.30' representing a stock price change. Now a minute passes, and A5 updates to reflect '0.10' (lets refer to this is second instance as A5*). The change here is negative since we moved downwards for a difference of 0.20 cents. My goal is to create a formula that will take A5 and the updated A5* and in a new cell return me a text: either 'down', 'up', or '~movement'. Making this happen is the puzzle I require assistance with.

I planed to design the formula by taking A5 and A5* and assigning an inequality relation. To do this, the sort of function I'm using is an =IF() formula. First, I want to know if it is even possible to use this formula to process the plan I wish to execute..

If so, my thought is to proceed in writing the formula in this fashion:

*plan: find a way to make excel recognize a distinction between the first A5 and the second instance of A5 upon a page refresh. Suppose we could do this for the moment by calling the former A5 and the latter A5* again.

=IF(A5<a5*, "down",="" "up",="" "~movement")
< A5*, "down","up","~movement" )
Q1: How do I make Excel recognize A5 prior to a refresh? Can I? Does it involve a different formula or perhaps macro (unfamiliar with it)?

Q2: I want my '=IF()' formula to have three outcomes.. is this possible? I would like to account for the following outcomes: the cell updates reflecting (1) no change, (2) positive change and (3) negative change.</a5*,>

 

[cyrilbrd]

so if I understood well your data is dynamic (updated via url), and on the second sheet the data is just a mirror of the first sheet (='cell_sheet1'), and you would like to compare the value in cell A5 at time x with the value in cell A5 at time x+1 ? its this correct?

 

[newton112]

yes thats exactly correct