Forum Discussion
STOCKHISTORY FUNCTION
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?
Yes, that's possible, even if that's not very good practice. Please see OneQuery query in attached file.
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.
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.