Log In
Register
Home
April 2, 2021
Offline
Dear Mynda,
Thank you for your continued support.
As suggested, attaching the sample file with the source file in itself as against the data from .csv file. Hope this is what you meant :).
Source sheet contains all the data for all the symbols for the market hours (India) in 15 minute time intervals starting from 9:15 to 15:30 hours.
What I need is to extract values for the specific time units, specified in the OHLC sheet, from the Source data for the previous day and the current day using power query, which later can be populated under OHLC tab.
Let me know if the file is in order.
Warm Regards
GK
See Answer
July 16, 2010
Offline
Hi GK,
Thanks for sharing your file. It's perfect.
You can add a conditional column that tags the data with "Yesterday&time" and "Today&Time" which you can then use to filter out the data you don't want. From there you can create a PivotTable to extract the data in the layout you want. See file attached.
Note: I've hard coded the dates in the conditional column with #date(... ) but you can replace the hard coded date for today with Date.From(DateTime.LocalNow()) and for yesterday's date use Date.From(DateTime.LocalNow())-1 like so:
=if [Date] = Date.From(DateTime.LocalNow()) then "Today"&Time.ToText([Time]) else if [Date] = Date.From(DateTime.LocalNow())-1 then "Yesterday"&Time.ToText([Time]) else "Prior"
Mynda
Answers Post
April 2, 2021
Offline
Hello Mynda,
That saved a lot of time Mynda and not to mention the headache that came with it. So a big thank you for not only resolving but giving it your priority even on a week end, for which I am much obliged.
But I have a query (as always) :).
In the query, you had hardcoded the date as "each if [Date] = #date(2021, 4, 1) then "Today"&Time.ToText([Time]) else if [Date] = #date(2021, 3, 31).....". Since my source will be dynamically updated as and when a new day starts, can we change the above code to check only data for the current day and the previous day, as against specifically adding dates.
Thank you Mynda for all your help.
Cheers
GK
July 16, 2010
Offline
Hi GK,
I answered this question in the 'note' in my reply.
Mynda
April 2, 2021
Offline
Damn, my bad, Mynda, I was so focused on the excel sheet missed this dearly.
My apologies, Mynda.
That clears the air, so all is well.
Thank you dearly,
~GK
April 2, 2021
Offline
Hi Mynda,
The pivot that you created requires auto-refresh. While we have auto-refresh for the query at any specified intervals, to refresh a pivot, from what I understand from the internet, certain lines of codes needs to be added as a VB Module.
Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Pivot").PivotTables("PivotTable1").PivotCache.Refresh
End Sub
I did try a few other VB Codes as well, that doesnt seem to refresh and always requires manual refresh.
FYI - I have moved the pivot to a separate sheet now (Pivot), thought that could have been the problem, but still no go.
So I am back to the expert for assistance.
Cheers
GK
July 16, 2010
Offline
Hi GK,
You can set PivotTables to refresh at set intervals via the Connection Properties:
Data tab > Queries & Connections > Connections pane > right-click > Properties > Usage tab.
If you require VBA, please start a new question specific to the VBA you require in the VBA forum group.
Mynda
1 Guest(s)
