Log In
Register
Home
Active Member
July 28, 2024
Offline
Hi all,
I hope I'm just being dumb here but I just can't figure out what the problem is here. I have stock data that I want to create in a tale and have it in columns (TRANSFORM) and then sorted descending. I am using this formula =SORT(TRANSPOSE(STOCKHISTORY(C3,TODAY()-365,TODAY(),2,0,0,1)),1,-1) and it just will not sort descending.
I tried the SORT function on its own and it works, also the Transform works, just not when used together.
I would appreciate any help on this, thanks so much!
Moderators
January 31, 2022
Offline
I couldn't test the formula as I received a #CONNECT! error, but I believe you want to sort a transposed list by dates in columns in descending order.
Then you must addd the optional argument TRUE to the SORT function.
=SORT(TRANSPOSE(STOCKHISTORY(C3,TODAY()-365,TODAY(),2,0,0,1)),1,-1,TRUE)
This should work. Without it, you will be sorting based on the words in the ow headers, Date and Close.
By the way you don't need the blue bolded parts as these are defaults for the STOCKHISTORY and SORT functions.
Personally I would first sort and then transpose. Feels more natural and requires fewer steps. The formula would then look like this:
=TRANSPOSE(SORT(STOCKHISTORY(C3,TODAY()-365,TODAY(),2,0)),,-1))
Active Member
July 28, 2024
Offline
Riny,
Thank you for our response, I did figure out what is happening. You are correct that its sorting on the date. On the first row the date is included in the parameter list and the sort does work correctly when sorting after the Transpose. With the next row that does not include the date as a parameter, but is a criteria that I want so the data will be sorted the same as the first row, it is sorting the data based on descending data, which does make sense because the date is not specified in the parameter list. I did verify that the close price on the stocks is correct on the first row but is not correct on subsequent rows. Your solution does work but it sorts on the data and not on the date.
So my theory is that you can't transpose stock history into rows and sort it without the date property in the parameter list. Which in my opinion is not useful if you are compiling a list of stocks for analysis based on the past history. I find this odd because the Today()-180 is from the past to Today() is now.
Apparently STOCKHISTORY needs to add a property that allows sorting in reverse order.
Active Member
July 28, 2024
Offline
Sorry, forgot to add the workbook
Moderators
January 31, 2022
Offline
Thanks for the file. That clarifies the issue. The trick is that you need to include the Date initially in order to be able to sort on it, but then you can use the TAKE function to display the last row only. I've added this to your file.
D2 remains as it was so that you display the dates. D3 now contains this formula:
=TAKE(SORT(TRANSPOSE(STOCKHISTORY(C4,TODAY()-180,TODAY(),2,0,0,1)),1,-1,TRUE),-1)
That's the same as in D2, but with the bold red parts added.
1 Guest(s)
