STOCKHISTORY FUNCTION

Question

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?&nbsp;&nbsp;

sergeibaklan · Answer

_Parjam_&nbsp;
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]
&nbsp;
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.
&nbsp;
Rest is cosmetic - add column names in spill and include them in named range, or rename columns in query; format columns; etc.

sergeibaklan · Answer

_Parjam_&nbsp;
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.

_parjam_ · Answer

SergeiBaklan&nbsp;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?&nbsp;

sergeibaklan · Answer

_Parjam_&nbsp;
Let me clarify the situation:
- let say STOCHHISTORY() returns data in D4
- you add named range as&nbsp;=Sheet1!$D$4# and Power Query it as&nbsp;= Excel.CurrentWorkbook(){[Name="MyName"]}[Content]
&nbsp;
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?
&nbsp;
Next, "it does not update the query with the new data loaded" - which error do you see in Power Query in this case?
&nbsp;
In general it's much better to have small sample file to check.

_parjam_ · Answer

SergeiBaklan&nbsp;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.&nbsp;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.&nbsp;&nbsp;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.&nbsp;&nbsp;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?&nbsp;&nbsp;&nbsp;Thanks

Forum Discussion

STOCKHISTORY FUNCTION

10 Replies

Resources