Create a data set by extracting data from another data set? Probably complicated...

Hi all,

I have access to daily data in a spreadsheet. Stock market data. The first challenge is that not every week starts on a Monday and not every week ends on a Friday. I'm sure 90 % starts on a Monday and ends on a Friday; but certainly not all.

Each day is a row in my data set.

From this, I want to summarize each week also in a row in a similar fashion.

The Open value will always be from the first day of the week.

The High value will be the maximum of the High column for each week.

The Low value will be the minimum of the Low column for each week.

The Close value will always be from the last day in the week.

Additionally, I'm interested in knowing on which day of the week the High and Low is found.

Is this possible without too much trouble?

If not, I may have to see if my data provider could provide this data. Actually, I already have access to that. The problem is that with weekly data, I won't know on which day of the week the High/Low is found.

Thanks in advance for any pointers! Attaching screenshot below and also workbook.

Best regards,

Elijah

1.png

Wow! Thank you so much, CK76!

This is highly appreciated.

However, I noticed that there are several duplicate records using your logic.
There are 2 other week that has similar issue. How should this be handled?

I'm suprised there are only 2.

In my mind, the first occurence of each should be the valid one. In your example - that would be Monday.

Maybe I missed it not having gone through your example yet myself, but were you able to extract which day in the week have the high/low value?

Thanks again.

Elijah

Wow, CK76,

This is simply amazing. I'm humbled by both your helpfulness and expertise on the subject. Thank you so much!

I was only able to take a quick look at the sheet you provided, but it looks great to me. I will try to re-create it from scratch following your detailed instructions later tonight and report back.

- - -

I have an additional related question:


Would it be possible to use the same dataset to create additional datasets using 'partial weeks/rows'?

A partial row/week would simply be a row which consists of data from only parts of the week, but always starting on Monday.

Dataset 1 - Monday: This row would only contain Monday and consists of one day only, so I have this one already in my original dataset.

Dataset 2 - Monday - Tuesday

Dataset 3 - Monday - Wednesday

Dataset 4 - Monday - Thursday

Dataset 5 - Monday - Friday: This would be the one you already helped me create spanning the entire week.


The purpose of this is that I would want to consult for example dataset 3 at the end of Wednesday, filter similar days and then match those days/dates with dataset 5 and see if I can notice any predictive qualities by week end.

If this is not possible with Power Query, I may be able to extract this information from my data provider. But Power Query is of course better if possible.

Thanks in advance for your input.

Best,

Elijah

Originally Posted by CK76

5. Using "Week", "Year" & "Low" from pasted query (select in sequence while holding CTRL key), select same columns in original table. Making sure to select columns in same sequence.
Attachment 633830
6. Expand the "Date" column.
7. Select all but the "Date" column and Group By. Use "LowDate" as column name, Operation = "Min", on "Date" column. This will eliminate duplicated row and return min date for low.

Hi, again,

I'm slowly working through your example and trying to understand your steps, but I got stuck on step 7.

If I select all but the "Date" column and group by - I'm not able to find the column "Date" to perform "Min" on.

Did I misread you somewhere...?

2.png

Beyond this - most of the steps seems understood, although some of it is above my head...

Best regards and thanks again,

Elijah

Originally Posted by CK76

Dataset 2 to 4, should it be in exactly same format as the sample I provided?
I'd imagine it's possible. You'll need to add bit of dynamic calculation and variable or tow in advanced editor for query.
Edit: Oh and it's more suited for Data Model and DAX to perform this sort of dynamic analysis on data set.

I think so, yes. But it depends on what's possible I suppose.

Originally Posted by CK76

But I'm having trouble envisioning partial week data giving predictive/forecasting trend of any value.

If you have trouble with that part. I'd recommend starting another thread. And use descriptive thread title. Referencing this thread in the post.
That way, it's easier for others using the forum to search for solutions and doesn't clutter this one.

It's hard to say if it can have any predictive value. I guess it's one of those things you never know before you try.

I'm at least interested in testing it out and see what I can find.

I will need help for sure. I'm already having trouble following your steps here on this task which probably is more basic.

Originally Posted by CK76

Select first column in your data. Press and hold Shift, use mouse to select the last column before "Date" column.

Alternately, go to advanced option, and add all the columns except "Date" column as Group column.

I probably botched something up along the way. I don't have a column named "Date" in the pasted query. Probably need to retrace my steps.

4.png

Originally Posted by CK76

Ah should have specified that I unchecked option to use original as prefix when expanding.
You can use SeriesTable.Date in your image.

Okay!

Once again, thank you so much for this, CK76!

I managed to retrace your steps just fine now. But, it would take me quite some time to be able to do so on my own.

Many thanks.

Elijah