Source
r/excel•
FirstSonofDarkness

Change Daily Stock Prices to get only Week-end Stock Prices

solved

I have a sheet containing daily stock prices of a company, but I need the data that reflects the price at the end of the week. I tried putting a DayNumber Column through =weekday formula and highlighting all the data with 6 as the output but it turns out that there are some Fridays where the stock exchange is closed so the last day of the week was a Thursday or a Wednesday. I could manually choose the highest day of the week but it's a 10 year data and it would take me an hour to do that. Is there an easier way to solve this?

Edit: Here is a screenshot of my data

https://preview.redd.it/mua0yqgc9eva1.png?width=411&format=png&auto=webp&v=enabled&s=808420510c200a6beba0bb0588b6dd7a824e5b93

Thanks in advance.

Posted Apr 22, 2023, 7:41 AM UTC · edited

1110
NHN_BI611•

You can see here, how I use two helper columns to figure out the latest given day of any week with COUNTIFS() on the WEEKNUM() and the WEEKDAY().

7
FirstSonofDarknessOP•

Solution Verified.

3
Clippy_Office_AsstMOD•

You have awarded 1 point to NHN_BI

^(I am a bot - please contact the mods with any questions. | ) ^(Keep me alive)

2
FirstSonofDarknessOP•

Thanks for the great demonstration, this worked.

2
NHN_BI611•

Great! Please reply:

Solution Verified

2
ampersandoperator33•

Can you post a screenshot of your data? It would help us help you find a solution if we don't have to imagine how it might be set up.

2
FirstSonofDarknessOP•

Thanks for the reply!

This is the general layout of the data but this goes on for 10 years. The Close Column is the closing stock price so Open, High, Low aren't really relevant.

https://preview.redd.it/g696vsnhqfva1.png?width=411&format=png&auto=webp&v=enabled&s=a1aa641eb5fbaf101ee7b38e60fdf5cd05e9fa0c

1
Decronym•

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

|Fewer Letters|More Letters| |-------|---------|---| |COUNTIFS|Excel 2007+: Counts the number of cells within a range that meet multiple criteria| |WEEKDAY|Converts a serial number to a day of the week| |WEEKNUM|Converts a serial number to a number representing where the week falls numerically with a year|

^(Beep-boop, I am a helper bot. Please do not verify me as a solution.)
^(3 acronyms in this thread; )^(the most compressed thread commented on today)^( has 20 acronyms.)
^([Thread #23437 for this sub, first seen 22nd Apr 2023, 08:15]) ^[FAQ] ^([Full list]) ^[Contact] ^([Source code])

2
ampersandoperator33•

Thanks. I'm taking a look now, but in the meantime, others might have solutions.

2
AutoModeratorMOD•

u/FirstSonofDarkness - Your post was submitted successfully.

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1