Forum Discussion
STOCKHISTORY FUNCTION
SergeiBaklan 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?
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.
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
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.
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?