STOCKHISTORY FUNCTION

[_Parjam_]

Is there any way to create a stable query with the data spill from the STOCKHISTORY function. I spilled the data to a spreadsheet and created a dynamic named range which captures the data spill, I added it to a query, with the create blank query option and the Excel.currentworkbook() command, this works if the dynamic range is populated, but when the dynamic range is not populated it messes up the query. Is there a work around or a stable solution to this matter?  

[#2]

@_Parjam_ 

What do you mean under "not populated", STOCKHISTORY() returns CALC error or N/A for some records? If the latest in Power Query you may remove rows with errors or replace errors on something.

[#3]

@Sergei Baklan STOCKHISTORY loads the data to the sheet, from there I use an Offset formula in a named range to select the data, then I create a query where I merge the named range with a table. The problem occurs in the query, when for example I change the dates or the ticker in the STOCKHISTORY formula, as the fresh data is loaded for a moment there is no data on the sheet, and it seems like the query looses the named range connection, and it does not update the query with the new data loaded. I suspect that the problem is that for an instant the named range goes from 4 columns to 1 cell to then go back to 4 columns. Can you suggest a better way to include a data spill which is not possible to structure as a table in a power query or am I doing something wrong? 

[#4]

@_Parjam_ 

Let me clarify the situation:

- let say STOCHHISTORY() returns data in D4

- you add named range as =Sheet1!$D$4# and Power Query it as = Excel.CurrentWorkbook(){[Name="MyName"]}[Content]

Here is the question did you use first row as header, or use Column1, Column2, etc? Changing the spill do you change the structure of the columns (their names, number, etc) and if yes how do you handle that in query?

Next, "it does not update the query with the new data loaded" - which error do you see in Power Query in this case?

In general it's much better to have small sample file to check.

[#5]

@Sergei Baklan 

I have recreated the problems in 3 simplified workbooks, with a series of problems that I am encountering. The first issue is the "Named range problem" if you go to power query editor you can see that the Named range is not available to include in the query, when the range that the Offset formula covers is not populated with data, I don't know if this is a standard functionality of Excel, to avoid this problem I populate the area with data before creating the query, but the problem appears when I change date or ticker as the Offset range goes from having data to not having it seems like the named range is sometimes lost from the query.

The second issue can be found in the document named "Date Problem". Here I have created the query and merged the date column, once I try to load the query back to the sheet I get a DataForma.Error, it seems like I am doing something wrong when I merge the date column. 

The third issue can be found in the "Query Duplicates" document, here I have gone around the problems by populating the Offset range before creating the query and loading the query to the sheet before changing the data format to the Date column in power query, but now the query keeps duplicating the data for each refresh. This has something to do with the name change to the Date column in power query, when I merge the columns I named it Date but automatically its named Date1, so I change it back to Date when this is done it starts duplicating for each refresh. 

I need a flawless and solid workbook, can it be done this way? It feels like I am turning tricks to achieve what I need and I am not sure that the result will be stable. Should I go about it differently?  

Thanks

[#6]

@_Parjam_ 

1) Named Range

Create spill with any data and create named range for it as

after that right click on selected spill and

After query is created return to name manager (F2 to edit in formula bar) and change named range on

If you change spill parameters query will be updated on Refresh.

That could be done shorter if initially create dynamic named range on spill, create blank query and change on

Source = Excel.CurrentWorkbook(){[Name="History"]}[Content]

2) Data Problem

Sorry, by I didn't catch which result you'd like to receive on this stage. In any case create another query as above by right click on BUYSELL table. Next Merge Queries, Merge as new

and here depends on what you'd like to receive. Assuming as above Stock History for the each date of BuySell, result is

Please note, stockhistory doesn't return data for not trading dates, thus two rows in above are empty.

Rest is cosmetic - add column names in spill and include them in named range, or rename columns in query; format columns; etc.

[#7]

@Sergei Baklan 

Thank you for your reply.

So according to your instructions I should do 2 separate queries which I later merge:

Source = Excel.CurrentWorkbook(){[Name="HISTPRICE"]}[Content],

Instead of adding all the columns I need from the sheet in just one query: 

#"Filtered Rows2" = Table.SelectRows(Source, each ([Name] <> "BuySellTable1" and [Name] <> "PriceFeed1")),

As the book has various repeated sheets, I would have preferred to just have 1 query for each sheet. Is it possible without compromising the stable behaviour of the workbook?

[#8]

@_Parjam_ 

Yes, that's possible, even if that's not very good practice. Please see OneQuery query in attached file.

[#9]

@Sergei Baklan 

I have tested the different variations, when I use: =OFFSET(Sheet1!$R$2,0,0,(COUNT(STOCKHISTORY(Sheet1!$L$3,Sheet1!$L$4,Sheet1!$L$5,Sheet1!$L$6,0,0,1,5))/3),3)

for the named range the refresh works even when there is no data within the range, but when I use:  =Sheet1!$X$2# the query refresh returns a Invalid cell value "#REF!" when the range is not populated. So I have decided to proceed with the Offset() formula. However there is another problem which occurs when I change the column name from DATE.1 to DATE, on each refresh the table duplicates the data, I have included both samples in the attachment, I would be grateful if you could have a look at the attachment and se if you can figure out why this happens.  

[#10]

@_Parjam_ 

1) Previous file - we could replace errors on nothing and tables will be refreshed correctly

2) OFFSET - with an error in stockhistory query is not evaluated, thus table in excel sheet is not updated and you see what was returned on previous successful refresh

3) Duplications - you don't expand separate tables and merge them after that, but expand all tables and named ranges are in Excel at once, includes already returned by queries on previous refresh tables.

After that you expand all columns but DATE.1, CLOSE, VOLUME

If to rename DATE.1 to DATE it expands one more time.

In general such approach gives unpredictable results.

[#11]

@Sergei Baklan 

I worked on the solutions you proposed, but the Table.NestedJoin(), removes the dates which does not coincide in the merged column. So I tried Table.Combine() instead, which seems to append instead of merging thus maintaining all the dates from both columns which is what I need, the problem I am encountering now, is with Column2 and Column3 as they expand and retract depending on if there is any data in the Stockhistory(). If I apply any steps to any of those columns, the query refresh returns error stating that those columns can`t be found, Can you suggest a way to include those columns dynamically, so that when there is data they appear and when there is no data they don not interrupt the refresh.