Transpose & Blank Issues

[Data123]

Hi when I use the =TRANSPOSE formula (see pic) I often get some blank rows. Those cells representing the blank rows have no formula. Meaning the formula is gone for those missing rows and this is what is causing it. I have created a new workbook and the same issue occurs again. I have verified those stocks have data.

Also, but less important why is the formula grayed out for the cell shown in the pic?

Oh one other question, for some reason when I get these blank areas and I try to refresh the data I the get many more blanks with the statement #SPILL! (see other pic).

 

[Data123]

I really like the ease of writing a formula using Transpose, but when in use it seems very fragile. Meaning the issue with blanks and #SPILL!.

 

[Peter_SSs]

There is no formula missing. The formula producing your results on each row is a dynamic array formula which actually only exists in a single cell in your row somewhere to the left of the active cell in your picture. When cell CF237 is selected, you can see a blue line around the row of cells. If you scroll left until you come to the left end of the blue rectangle and select that cell you will see the 'actual' formula cell that is populating all the cells to the right.
Your shorter rows for those stocks where you thought formulas are missing simply indicate that the stocks listed in column A of those rows do not have a full year of stock history available.

 

[Data123]

There is no formula missing. The formula producing your results on each row is a dynamic array formula which actually only exists in a single cell in your row somewhere to the left of the active cell in your picture. When cell CF237 is selected, you can see a blue line around the row of cells. If you scroll left until you come to the left end of the blue rectangle and select that cell you will see the 'actual' formula cell that is populating all the cells to the right.
Your shorter rows for those stocks where you thought formulas are missing simply indicate that the stocks listed in column A of those rows do not have a full year of stock history available.

Thanks Peter!! I see what you are saying, but the strange thing is that there is data for those blank cells. I verified it by creating a STOCKHISTORY going back for 90 days for that symbol and it shows data for those missing dates that TRANSPOSE doesn't. Also, something interesting when I wrote my original post and had the #SPILL! issue. I noticed I had two workbooks open and they both had around 800 rows. I closed one of them and the #SPILL! filled in with values in the book that was still open. However, the blank or missing data from TRANSPOSE continues to be an issue. I have deleted workbooks and recreated the same exact workbook and the same symbols that shows blank now fill in. It just seems temperamental to me. Is this possible? Please see pic of where TRANSPOSE offers more data for row 398 without the dollar sign than the others.

 

[Peter_SSs]

It just seems temperamental to me. Is this possible?

I certainly don't think TRANSPOSE is temperamental. I don't know about STOCKHISTORY as I have not really used it other than to have a brief look at some examples.
I can't really tell anything from an image.
Can you either upload a copy of your workbook (any sensitive data removed or disguised) to DropBox or OneDrive or Google Drive etc and provide a publicly shared link here or else at least advise

• what cell the actual formula is in for row 398
• what the actual formula is in that cell
• what value is in A398
• what value is in A397

 

[Data123]

I certainly don't think TRANSPOSE is temperamental. I don't know about STOCKHISTORY as I have not really used it other than to have a brief look at some examples.
I can't really tell anything from an image.
Can you either upload a copy of your workbook (any sensitive data removed or disguised) to DropBox or OneDrive or Google Drive etc and provide a publicly shared link here or else at least advise

• what cell the actual formula is in for row 398
• what the actual formula is in that cell
• what value is in A398
• what value is in A397

Hi Peter, sorry it took me a while to get back here. I spent several hours trying to upload mini-sheet from the bottom below and Excel 365 would not let me add it so used Google Drive as requested. Mr Excel Help .xlsx

As stated three main issues;
1. Use the bottom slider to move the sheet to the right and look for blank spaces where data is missing/blank. There are many of them. I verified several blank spaces actual have data for those dates, so I don't know why they are blank. Also, if you click on the blank cells, the formula is gone. I know you stated that's because there is no data, but this confuses me since cells with #NA have a formula, but #NA means there is no data, and I have confirmed that is correct (there is no data for the #NA's)

2. When you are at the end of the sheet cell IS, there are cells that are beyond and go to IW and some of those are in non USD. Why are they going beyond IS since I asked for only 368 days?

3. If I try to fix the blank cells often a big part of the worksheet shows #SPILL! and sometimes it's the whole sheet. The only way I have been able to fix this is to delete and restart from scratch.

I really appreciate the help.

 

[Peter_SSs]

I spent several hours trying to upload mini-sheet

Your data is too big for the mini sheet limitations. That's why I didn't suggest mini sheet.
If it was a different problem you had with using XL2BB then check out suggestions in this thread.

I verified several blank spaces actual have data for those dates, so I don't know why they are blank.

Again, I am no expert on STOCKHISTORY but it doesn't work the way I think that you think it works. The data does not necessarily line up with the date headings you have at the top.
In your sample file, take row 91 as an example. If you look at the right hand value in that row, I think that you will find that closing value relates to the right hand date in row 1, which is not in the same column. The values that are missing are values from the first columns (B, C, D, ..), not the last columns.

As I mentioned earlier, shorter rows would indicate that the stock does not have a full year of data history. Take the row 91 example again. When I look up stock TLST on XNAS and look at the Historical Data or the Chart, it shows data from Oct 2023 only, even though I have chosen a 1 year time frame at the top.

When you are at the end of the sheet cell IS, there are cells that are beyond and go to IW and some of those are in non USD. Why are they going beyond IS since I asked for only 368 days?

Because you are getting data from different markets and they will have a different number of holidays (& therefore a different number of trading days) in a year.


To get a better idea of what is happening, I suggest that you get a blank worksheet and experiment with some formulas like this that include the dates with each stock.
I have hidden some columns in this mini sheet and highlighted some cells to note.
My dates are in d/m/y format.

Data123 Mr Excel Help .xlsx
	A	B	C	D																					Y	Z	AA	AB	AC
1	T-Rex 2X Long TESLA DT (XNAS:TSLT)	19/10/2023	20/10/2023	23/10/2023																					21/11/2023	22/11/2023	24/11/2023	27/11/2023	28/11/2023
2		$ 20.46	$ 18.89	$ 18.86																					$ 23.72	$ 22.33	$ 22.47	$ 22.64	$ 24.60
3
4	iShares:MBS ETF (XNAS:MBB)	6/03/2023	7/03/2023	8/03/2023																					6/04/2023	10/04/2023	11/04/2023	12/04/2023	13/04/2023
5		$ 92.30	$ 92.07	$ 92.05																					$ 95.49	$ 94.56	$ 94.90	$ 95.12	$ 95.06
6
7	LS ARKW TRACKER ETP (XLON:ARKB)	6/03/2023	7/03/2023	8/03/2023																					6/04/2023	11/04/2023	12/04/2023	13/04/2023	14/04/2023
8		176.95	174.50	174.75																					166.45	170.10	167.75	170.30	169.00
Sheet1

Cell Formulas
Range	Formula
B1:CP2,B7:IW8,B4:IR5	B1	=TRANSPOSE(STOCKHISTORY(A1, TODAY()-368, TODAY(),0,0,0,1))
Dynamic array formulas.

 

[Data123]

Your data is too big for the mini sheet limitations. That's why I didn't suggest mini sheet.
If it was a different problem you had with using XL2BB then check out suggestions in this thread.

Thanks Peter, you are correct the dates don't match up and the blank spaces are because of lack of data for those specific equities. The issue with the XL2BB was with installation, but not a big deal especially since you stated the workbook was too large anyway. I do like Google Drive.

Hmm I am at a loss now how to proceed. Is there a way to add a space between each row of symbols with a few clicks or is there no other choice, but to click 839 times and do it manually? Or in the cells with price data, is there a way to click to view and verify a date the price data represents? Adding a space would make the number of rows twice as big and the second idea of verifying the date would be better. However, I am very open to any other suggestions of how I can make sure the dates match somehow.

 

[Peter_SSs]

The issue with the XL2BB was with installation,

For the future, check out the link I gave you.

I am very open to any other suggestions of how I can make sure the dates match somehow.

Here are two suggestions I think are better than adding spaces or verifying dates (though what I propose really is verifying dates).

This first example has a formula to list all dates for a year across row 1 (my mini sheet only shows part). The formula in column B is just copied down. This matches up the STOCKHISTORY date with the row 1 date.
As you can see, weekends are blank and if you scroll across to column AJ you see an example of where there must have been a week day where there was no trading in London but there was in the US.
In what I have shown there are no values for TLST but if you replicate this in a worksheet and scroll across you will see TLST values appear from 19 Oct 23.

Data123 Mr Excel Help .xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN
17		7/03/2023	8/03/2023	9/03/2023	10/03/2023	11/03/2023	12/03/2023	13/03/2023	14/03/2023	15/03/2023	16/03/2023	17/03/2023	18/03/2023	19/03/2023	20/03/2023	21/03/2023	22/03/2023	23/03/2023	24/03/2023	25/03/2023	26/03/2023	27/03/2023	28/03/2023	29/03/2023	30/03/2023	31/03/2023	1/04/2023	2/04/2023	3/04/2023	4/04/2023	5/04/2023	6/04/2023	7/04/2023	8/04/2023	9/04/2023	10/04/2023	11/04/2023	12/04/2023	13/04/2023	14/04/2023
18	iShares:MBS ETF (XNAS:MBB)	$ 92.07	$ 92.05	$ 92.46	$ 93.21			$ 94.12	$ 93.67	$ 94.62	$ 94.19	$ 95.09			$ 94.30	$ 94.12	$ 95.38	$ 95.45	$ 95.60			$ 94.42	$ 94.14	$ 94.41	$ 94.46	$ 94.73			$ 94.90	$ 95.47	$ 95.74	$ 95.49				$ 94.56	$ 94.90	$ 95.12	$ 95.06	$ 94.43
19	LS ARKW TRACKER ETP (XLON:ARKB)	$ 174.50	$ 174.75	$ 173.05	$ 162.70			$ 158.30	$ 165.25	$ 163.20	$ 171.70	$ 171.60			$ 167.20	$ 177.10	$ 175.60	$ 175.60	$ 165.40			$ 165.05	$ 163.95	$ 167.25	$ 169.70	$ 175.45			$ 171.05	$ 170.90	$ 165.00	$ 166.45					$ 170.10	$ 167.75	$ 170.30	$ 169.00
20	T-Rex 2X Long TESLA DT (XNAS:TSLT)
Sheet1

Cell Formulas
Range	Formula
B17:NC17	B17	=LET(s,EDATE(TODAY(),-12)+1,SEQUENCE(,TODAY()-s+1,s))
B18:NC20	B18	=LET(sh,TRANSPOSE(STOCKHISTORY(A18, TODAY()-368, TODAY(),0,0,0,1)),IFNA(HLOOKUP(B$17#,sh,2,0),""))
Dynamic array formulas.

My second suggestion is basically the same but the dates across the top are dates for a year, excluding weekends. Here 7 April is blank in all rows, indicating a week day with no trading in both places (Good Friday)

Data123 Mr Excel Help .xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH
1		7/03/2023	8/03/2023	9/03/2023	10/03/2023	13/03/2023	14/03/2023	15/03/2023	16/03/2023	17/03/2023	20/03/2023	21/03/2023	22/03/2023	23/03/2023	24/03/2023	27/03/2023	28/03/2023	29/03/2023	30/03/2023	31/03/2023	3/04/2023	4/04/2023	5/04/2023	6/04/2023	7/04/2023	10/04/2023	11/04/2023	12/04/2023	13/04/2023	14/04/2023	17/04/2023	18/04/2023	19/04/2023	20/04/2023
2	iShares:MBS ETF (XNAS:MBB)	$ 92.07	$ 92.05	$ 92.46	$ 93.21	$ 94.12	$ 93.67	$ 94.62	$ 94.19	$ 95.09	$ 94.30	$ 94.12	$ 95.38	$ 95.45	$ 95.60	$ 94.42	$ 94.14	$ 94.41	$ 94.46	$ 94.73	$ 94.90	$ 95.47	$ 95.74	$ 95.49		$ 94.56	$ 94.90	$ 95.12	$ 95.06	$ 94.43	$ 93.88	$ 93.94	$ 93.94	$ 94.49
3	LS ARKW TRACKER ETP (XLON:ARKB)	$ 174.50	$ 174.75	$ 173.05	$ 162.70	158.30	165.25	$ 163.20	$ 171.70	$ 171.60	$ 167.20	$ 177.10	175.60	175.60	$ 165.40	$ 165.05	$ 163.95	$ 167.25	$ 169.70	175.45	171.05	$ 170.90	$ 165.00	$ 166.45			170.10	167.75	$ 170.30	$ 169.00	$ 167.10	$ 170.75	167.75	164.05
Sheet3

Cell Formulas
Range	Formula
B1:JC1	B1	=LET(s,EDATE(TODAY(),-12)+1,d,SEQUENCE(,TODAY()-s+1,s),FILTER(d,WEEKDAY(d,2)<6))
B2:JC3	B2	=LET(sh,TRANSPOSE(STOCKHISTORY(A2, TODAY()-368, TODAY(),0,0,0,1)),IFNA(HLOOKUP(B$1#,sh,2,0),""))
Dynamic array formulas.

 

[Data123]

For the future, check out the link I gave you.
Here are two suggestions I think are better than adding spaces or verifying dates (though what I propose really is verifying dates).

This first example has a formula to list all dates for a year across row 1 (my mini sheet only shows part). The formula in column B is just copied down. This matches up the STOCKHISTORY date with the row 1 date.
As you can see, weekends are blank and if you scroll across to column AJ you see an example of where there must have been a week day where there was no trading in London but there was in the US.
In what I have shown there are no values for TLST but if you replicate this in a worksheet and scroll across you will see TLST values appear from 19 Oct 23.

Thanks again Peter! I am very interested in understanding your solutions. May I ask some questions below?

Since my issue was that I had two formulas one that showed the date for price data and the other did not, does your second formula solve this issue and if so may I ask how?

Since I need daily price data is it important that the dates be daily?

Why do you think TLST shows up in a worksheet, but not on the mini sheet upload?

Is it possible to verify what date a cells price data came from without a date header?