Help to solve =IF(LINEST(Data!X:X)>0,"↑","↓") formula

[excelnabb]

Hi

I need help to find a formula in Cell AF7 on the Day sheet. The formula should find whether there is a upward or downward trend for the volume (F coloumn in Data sheet) in the first hour on day 3. Where day 3 starts, can be seen as the third a in the A Column in the Data sheet. The thing is that the row position on the data on the Data sheet varies, so a helper sheet is created. The formula in AC7 finds the highest value within the first hour, so I tried to combine this formula with the trend formula as you can see in AF7, however this doesn't work.
The post for the other formulas can be found here: https://www.excelforum.com/excel-for...formula-4.html

[Glenn Kennedy]

I don't really follow what you're trying to do in AF7, at least partly because LINEST is not what you want. I did notice you have a similar problematic formula at U7, however.

It should read:
=IF(SLOPE(INDIRECT("'Data'!F"&Helper!C9&":F"&Helper!C8),INDIRECT("'Data'!G"&Helper!C9&":G"&Helper!C8))>0,"↑","↓")

In addition, your MATCH formulae on helper were not returning the correct ranges, they should be:
=MATCH(B8,Data!$G:$G,0)

copied down. If you agree with my interpretation of your issues, can you have another go at explaining what you EXPECT to see in AF7?

[excelnabb]

Okay, what I want to find in U7, is the trend (upward or downward) of the volume for the last hour of day 2. Day 2 starts after the second a in coloum A on the data sheet.
With these new formulas you made, I get the wrong answer for the time, volume and price (r,s,t)and probably for the trend as well then. If you see here on post #44 John Topley made a formula to the helper sheet:

https://www.excelforum.com/excel-for...formula-3.html

[Glenn Kennedy]

Baby steps. look at helper. Your formula gives the wrong start/end date (green), while mine (yellow) is correct?? yes/no??

[Glenn Kennedy]

baby step 2. You want SLOPE, not LINEST. LINEST will give the slope, in one of its (multiple) output cells. As you are (trying to) use it, it will not, I'm pretty certain, work.

[Glenn Kennedy]

Also check F8-G9 of the first sheet I posted, (Post 2). There you will see the slope calculated a) using SLOPE-INDIRECT and b) using the exact ranges of the last hour. the slopes are equal. So, I don't see what is incorrect about what I did at Post 2.

[excelnabb]

Baby steps. look at helper. Your formula gives the wrong start/end date (green), while mine (yellow) is correct?? yes/no??

Yes your formula seems to work (don't know why I got wrong numbers first time though )

[excelnabb]

baby step 2. You want SLOPE, not LINEST. LINEST will give the slope, in one of its (multiple) output cells. As you are (trying to) use it, it will not, I'm pretty certain, work.

I'm not completely sure what formula I will have to use. The result should be the change in the trend of the volume, if its uptrending or downtredning. If I use slope, does it calculate the slop from the first volume at 15:00, and then creates a straigt line to the last volume at 16:00? If so, then I don't want to use slope, as the the first or the last volume can be completely different than the average trend it goes.

[excelnabb]

Also check F8-G9 of the first sheet I posted, (Post 2). There you will see the slope calculated a) using SLOPE-INDIRECT and b) using the exact ranges of the last hour. the slopes are equal. So, I don't see what is incorrect about what I did at Post 2.

Okay, so I will get the same result with both of these? I'm not sure what slope indirect does? I'm not even sure if Slope is the correct formula to use?

[Glenn Kennedy]

SLOPE is the correct function to use. It's syntax is =SLOPE(known y's, known x's). SLOPE eturns the slope of the linear regression line through data points in known_y's and known_x's, all of them.

INDIRECT is picking up the (corrected) starting and finishing rows fro your data:

=SLOPE(INDIRECT("'Data'!F"&Helper!C9&":F"&Helper!C8),INDIRECT("'Data'!G"&Helper!C9&":G"&Helper!C8))
will resolve to:

=SLOPE(Data!$F$566:$F$614,Data!$G$566:$G$614)

the main difference being that the inclusion of INDIRECT dynamically picks up the start (Helper C9) and end (Helper C8) rows of your data


Are you familiar with formulas/evaluate formula/evaluate? Try it to see how Excel processes a formula. The only snag is that the tiny window cannot be resized!!!

Regarding LINEST, it IS OK, too. I misread the formula evaluation. It resolves to =IF({FALSE,TRUE},"Up","Down")... (or {TRUE,TRUE}, or whatever) whereas my blinkered eyes saw: =IF({FALSE,TRUE}>0,"Up","Down"). These have different outcomes.

[excelnabb]

Thank you very much! I used the =IF(SLOPE(INDIRECT("'Data'!F"&Helper!C9&":F"&Helper!C8),INDIRECT("'Data'!G"&Helper!C9&":G"&Helper!C8))>0,"↑","↓") in cell U7 on the data sheet to find the trend in volume in the last hour of day 2. This as I understands finds the trend in volume from 15.00 to 16.00(and not the other way around)?

In AF7 on the data sheet, I need to find the trend in volume the FIRST hour of day 3. This means I need to have new start and end times on the helper sheet, and also a modified formula to find the rows. You can see what I did in the helper sheet (marked red), and the modified formula in AF7. Don't know if I typed them correctly though, as I get a #N/A for the first hour +1.
Could you help me with this?

[Glenn Kennedy]

This as I understands finds the trend in volume from 15.00 to 16.00(and not the other way around)? Correct.

Can we take a step back for a moment and look at how the date/time in data G:G is being generated. It looks awfully messy. Can you explain how you know if the new day starts at 09:30 or 09:31? It would be good to make the generation of that column of data more rational..

[excelnabb]

Okay, So the script that finds new stocks data works like this: http://investexcel.net/free-intraday-stock-data-excel/

I download 3 days for every minute each time I push the "Get data from google" button.

The a1515767460 (and similar) is a:
Column A contains encoded date and time data; You see a time zone offset, time stamp (which is repeated if the backfill crosses more than one day) and a number.

The time stamp is of the form a1404826200 (the numbers after the “a” differ). This is a Unix time stamp
The time zone offset is a constant offset from the time stamp
The numbers below the time stamp (in column A) are integer multiples of the backfill interval

This means that the data below the first a1515767460 (and similar) is the data for the first day, the second a1515767460 (and similar) is for the second day, and then you have the third a1515767460 (and similar)

The 1,2 and 3 in coloumn A on the helper sheet, is derived from if its the first, second or third a in coloumn A on the Data sheet. The a's is a unix time stamp, and changes for every stock depending on what day I recieve the stock data and how many records there are from the price of the stock (not all prices are recorded for every minute for every stock, some are missing).

So every time I press the "get data from google" button on the Parameters sheet, ALL of the data on the Data sheet is replaced with new data(this mean I can NOT have any formulas on the Data sheet, as they will be deleted when retrieving a new stock), and since the row position for the first, second and third a unix time stamp change, then I need a formula that finds their position so I can retrieve and calculate the data I want from B,C,D,E,F,G columns on the Data sheet.

The formulas in B8,B10,B13 B14, C8,C9,C13 and C14, are formulas that someone else has made for me to find if it's the first, second or third a.

Did this make any sense? (sorry for my bad english)

[Glenn Kennedy]

Back half a step!!

In column G of data, you have 3 days worth of data. 10-12 January. Two of these start at 09:30 and finish at 16:00. The third (12th) starts at 09:31 (cell G790) and finishes at 16:00. Why does it start at 09:31? How can you tell?

In column I I have entered a more rational way of calculating the date/time, based on an 09:30 start time. I need to know why (your formula) the 12th starts at 09:31 and HOW YOU KNOW that it starts at 09:31.... or does it start at 09:30, and you made a mistake.....

[excelnabb]

So all the data on the Data sheet is download directly from google and the whole Data sheet is wiped and replaced with new data every time you press the get data from google button. As I mention earlier, not all prices are recorded for every minute for every stock, some are missing, in this case you found out that the data for 0930 is missing, so then it shows the data for 0931 instead (as this is the next minute it has recorded the price and the volume). It would be best if all the data was recorded for every minute, but since it's not always the case, I need to use a formula that calculates from the three a's in column A to get the first/last hour at every day.

[Glenn Kennedy]

Yes, but... How can you tell what time 12th January starts at? How do you know if it's 09:30, 09:31, or whatever.

maybe I'm being dense...

[Glenn Kennedy]

The value in column G is being calculated by a formula that (I assume) you add, every time you refresh the sheet. So, how can you tell what time the data starts at??

[excelnabb]

I can understand it's confusing, as I'm not 100% sure of all the formulas myself (most of them are from people on this forum).
This is how I think it works: the helper sheet has 1,2 and 3, in column A depending if it is the first, second or third a in column A on the Data sheet. The formula in cell B8 on the Helper sheet finds if the value in column a on the helper sheet match the number 3, if so, then it finds the corresponding cell on the data sheet, and picks the cell above it (since the cell is for the third day(number 3)). The formula in the row C8 on the data sheet then finds what row this is on, and the formula on the Day sheet finds the values on this row(max/min, vloume, time, and so on).


2018-01-25_18-40-12.jpg

[Glenn Kennedy]

Now I'm confusing you. Forget the helper sheet. Stick to the data sheet. Why does cell G790 start at 09:31, as opposed to 09:30?

[excelnabb]

I don't think I understand what you mean, check with this sheet, is it the same thing here? (this one miss 0930 and starts on 0931 on 399, as there are no data for 0930)

[excelnabb]

Now I'm confusing you. Forget the helper sheet. Stick to the data sheet. Why does cell G790 start at 09:31, as opposed to 09:30?

As there doesn't exist any price data recorded the 12 Jan 2018 9:30 on googles data base for this stock.

[Glenn Kennedy]

Apologies.... I didn't see your reply until a few seconds ago. Can you post an Excel sheet, showing EXACTLY what you download from the web, BEFORE you add any formulae to the raw data sheet.

[excelnabb]

This is the original excel sheet I started with. All the info about the sheet can be found here:
http://investexcel.net/free-intraday-stock-data-excel/

[Glenn Kennedy]

OK. Finally.... I think that I understand, and I apologise for the delay in getting here... Assuming that they are not needed elsewhere, the tan cells in helper can be deleted. U7 and AF7 have now been fixed and do not need the tan cells. I no longer need to use the volatile INDIRECT function to calculate your up/down arrows.