Extracting Weekly and Monthly price points from Daily price data.

B

Brunks

New Member
Joined
Sep 2, 2013
Messages
9
Hi all,

I have listed a simple stock price table that shows daily prices. I want to find a filter (or some other way) to just show the WEEKLY prices and the MONTHLY prices. I have tried filtering the date column in the table but there is no option for "show weekly points" or "filter for weekly data". How would I go about doing this?

Thanks for any help you can offer.




[TABLE="width: 459"]
<colgroup><col><col span="4"><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Open[/TD]
[TD]High[/TD]
[TD]Low[/TD]
[TD]Close[/TD]
[TD]Volume[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4-Sep-07[/TD]
[TD="align: right"]68.1[/TD]
[TD="align: right"]71.27[/TD]
[TD="align: right"]67.97[/TD]
[TD="align: right"]70.6[/TD]
[TD="align: right"]7150000[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5-Sep-07[/TD]
[TD="align: right"]70.12[/TD]
[TD="align: right"]71.22[/TD]
[TD="align: right"]70.06[/TD]
[TD="align: right"]70.98[/TD]
[TD="align: right"]6435900[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6-Sep-07[/TD]
[TD="align: right"]70.98[/TD]
[TD="align: right"]72.3[/TD]
[TD="align: right"]69.6[/TD]
[TD="align: right"]70.39[/TD]
[TD="align: right"]8968766[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7-Sep-07[/TD]
[TD="align: right"]69.7[/TD]
[TD="align: right"]69.93[/TD]
[TD="align: right"]68.28[/TD]
[TD="align: right"]69.36[/TD]
[TD="align: right"]8229878[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10-Sep-07[/TD]
[TD="align: right"]69.19[/TD]
[TD="align: right"]69.28[/TD]
[TD="align: right"]66.9[/TD]
[TD="align: right"]68.03[/TD]
[TD="align: right"]6889300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11-Sep-07[/TD]
[TD="align: right"]68.03[/TD]
[TD="align: right"]68.31[/TD]
[TD="align: right"]66.51[/TD]
[TD="align: right"]67.9[/TD]
[TD="align: right"]8427350[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12-Sep-07[/TD]
[TD="align: right"]67.8[/TD]
[TD="align: right"]68.31[/TD]
[TD="align: right"]66.38[/TD]
[TD="align: right"]66.75[/TD]
[TD="align: right"]13278700[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]13-Sep-07[/TD]
[TD="align: right"]66.97[/TD]
[TD="align: right"]69.45[/TD]
[TD="align: right"]66.97[/TD]
[TD="align: right"]68.72[/TD]
[TD="align: right"]9377165[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14-Sep-07[/TD]
[TD="align: right"]68.72[/TD]
[TD="align: right"]69.77[/TD]
[TD="align: right"]68.21[/TD]
[TD="align: right"]69.29[/TD]
[TD="align: right"]4874910[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17-Sep-07[/TD]
[TD="align: right"]69.12[/TD]
[TD="align: right"]69.21[/TD]
[TD="align: right"]67.41[/TD]
[TD="align: right"]68.58[/TD]
[TD="align: right"]5959900[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]18-Sep-07[/TD]
[TD="align: right"]68.71[/TD]
[TD="align: right"]70.5[/TD]
[TD="align: right"]68.04[/TD]
[TD="align: right"]70.2[/TD]
[TD="align: right"]6642610[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19-Sep-07[/TD]
[TD="align: right"]70.64[/TD]
[TD="align: right"]71.55[/TD]
[TD="align: right"]68.91[/TD]
[TD="align: right"]69.57[/TD]
[TD="align: right"]8513831[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20-Sep-07[/TD]
[TD="align: right"]69.78[/TD]
[TD="align: right"]70.55[/TD]
[TD="align: right"]69.15[/TD]
[TD="align: right"]70.09[/TD]
[TD="align: right"]4929600[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]21-Sep-07[/TD]
[TD="align: right"]70.74[/TD]
[TD="align: right"]71.39[/TD]
[TD="align: right"]70.09[/TD]
[TD="align: right"]71.05[/TD]
[TD="align: right"]6780900[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24-Sep-07[/TD]
[TD="align: right"]71.23[/TD]
[TD="align: right"]71.57[/TD]
[TD="align: right"]70.58[/TD]
[TD="align: right"]70.87[/TD]
[TD="align: right"]7074972[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25-Sep-07[/TD]
[TD="align: right"]70.02[/TD]
[TD="align: right"]70.17[/TD]
[TD="align: right"]68.61[/TD]
[TD="align: right"]68.85[/TD]
[TD="align: right"]8313346[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]26-Sep-07[/TD]
[TD="align: right"]69.7[/TD]
[TD="align: right"]69.75[/TD]
[TD="align: right"]67.16[/TD]
[TD="align: right"]68.26[/TD]
[TD="align: right"]9753975[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]27-Sep-07[/TD]
[TD="align: right"]69.08[/TD]
[TD="align: right"]69.08[/TD]
[TD="align: right"]67.32[/TD]
[TD="align: right"]67.56[/TD]
[TD="align: right"]5991700[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28-Sep-07[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]68.49[/TD]
[TD="align: right"]66.92[/TD]
[TD="align: right"]67.18[/TD]
[TD="align: right"]6360726[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1-Oct-07[/TD]
[TD="align: right"]66.92[/TD]
[TD="align: right"]67.57[/TD]
[TD="align: right"]66.39[/TD]
[TD="align: right"]67.44[/TD]
[TD="align: right"]6598826[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2-Oct-07[/TD]
[TD="align: right"]67.09[/TD]
[TD="align: right"]68.2[/TD]
[TD="align: right"]66.6[/TD]
[TD="align: right"]67.9[/TD]
[TD="align: right"]7199500[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]3-Oct-07[/TD]
[TD="align: right"]67.36[/TD]
[TD="align: right"]68[/TD]
[TD="align: right"]66.5[/TD]
[TD="align: right"]67.09[/TD]
[TD="align: right"]6946900[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4-Oct-07[/TD]
[TD="align: right"]66.65[/TD]
[TD="align: right"]68.04[/TD]
[TD="align: right"]65.97[/TD]
[TD="align: right"]67.86[/TD]
[TD="align: right"]5399100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]5-Oct-07[/TD]
[TD="align: right"]68.2[/TD]
[TD="align: right"]68.87[/TD]
[TD="align: right"]67.13[/TD]
[TD="align: right"]68.35[/TD]
[TD="align: right"]6283300[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8-Oct-07[/TD]
[TD="align: right"]68.85[/TD]
[TD="align: right"]71.28[/TD]
[TD="align: right"]68.81[/TD]
[TD="align: right"]70.77[/TD]
[TD="align: right"]12027134[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9-Oct-07[/TD]
[TD="align: right"]71.25[/TD]
[TD="align: right"]72.49[/TD]
[TD="align: right"]70.56[/TD]
[TD="align: right"]72.19[/TD]
[TD="align: right"]10616425[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10-Oct-07[/TD]
[TD="align: right"]70.06[/TD]
[TD="align: right"]75.22[/TD]
[TD="align: right"]69.95[/TD]
[TD="align: right"]74.25[/TD]
[TD="align: right"]18330800[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11-Oct-07[/TD]
[TD="align: right"]74.45[/TD]
[TD="align: right"]75.66[/TD]
[TD="align: right"]71.83[/TD]
[TD="align: right"]73.25[/TD]
[TD="align: right"]11596450[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12-Oct-07[/TD]
[TD="align: right"]72.75[/TD]
[TD="align: right"]74.05[/TD]
[TD="align: right"]72.42[/TD]
[TD="align: right"]73.49[/TD]
[TD="align: right"]6198639[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Let's say you're looking to capture every Friday... (The first Friday is on Sept. 7th) Make column G =A5, column H =B5, etc. Make your selection include row 5 (where Sept. 7 is) and the next 4 rows (just prior to the next Friday). It should look like this:

pDbXiwE.jpg


When you drag down to extend the selection (using the little mark in the lower right), it will pick up only every 5th entry (every Friday).

When you're done, you can sort Columns G:L by date, and you should have your weekly list. Monthly might be more tricky...
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question Question
lookup on calculated value not exact in list
Replies
5
Views
958
starkc69
S

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top