Results 1 to 8 of 8

Thread: Generating stock price change (daily and weekly)

  1. #1

    Generating stock price change (daily and weekly)



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi - I am stuck in a little problem with excel and am hoping someone can help guide me in the right direction to generate the correct stock price change (daily and weekly).

    In the attached spreadsheet, I am experiencing problems in generating the correct stock price change (daily and weekly) by changing the date in cell A1 in the "AAA overview" worksheet.

    Listed below are some things to help you understand the attached spreadsheet.

    1. There are only 2 worksheets in the excel file:
    a. AAA overview -- this is where all the calculations occur
    b. AAA historical data -- this is where I am storing all the historical stock information


    2. If I change the date in cell A1 in the "AAA overview" spreadsheet, the daily and weekly information should generate accordingly, cell C9 and cell C10 respectively.

    3. Formula in C9 (indicates daily stock price change) works when the date in cell A1 is between Tues - Fri, and fails between Sat - Mon. This formula has an insane IF statement, and is needed, as I have to meet 2 criteria before calculating the correct daily price change (Ex - Monday the stock market is open, but Friday and Thursday it was closed due to holiday. So the daily change should be from Wednesday to Monday)

    4. Formula in C10 (indicates weekly stock price change) works when the date in cell A1 is between Mon - Fri, and fails between Sat-Sun. Also, I do not have an IF statement in cell C10, but now that I am thinking about it, I guess it should, considering if the markets were closed the prior weeks Thursday and Friday, then the current weeks change should be based from the prior weeks Wednesday to this weeks Friday.

    I believe I said that correctly.

    Well, if someone could shed some light, I would appreciate it.

    Thanks and if you have any questions, or think I should use another excel function/formula, please feel free to mention it. Also, if you can make the change in my spreadsheet and repost, I will see what you are referring to.

    Attached Files Attached Files

  2. #2
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    I'd do it like the attached, with some less complex formulas.

    For reference, in order to use the VLOOKUP formulas, I had to sort your data into ascending order of dates.

    I also added another sheet that has a calculation of the holidays and the previous work days.
    Attached Files Attached Files
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  3. #3
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    170
    Articles
    0
    Cross post here, BTW.

  4. #4
    Seeker jpr73's Avatar
    Join Date
    Apr 2011
    Location
    France
    Posts
    15
    Articles
    0
    Hi,
    Cross post here

  5. #5
    Ken Puls - I greatly appreciate you taking the time to help me.

    I reviewed your spreadsheet and it has given me some new ideas and will help me generate the results I am looking for.

    Thank you once again.

  6. #6
    I did not know this post was going to be a cross post with other sites. I tried to remove my post from the other two sites, but was unable to do so from both sites.

    Can you tell me how I can remove/delete/edit my post from ozgrid and mrexcel?

    Thanks and sorry for the dupe posts.
    Last edited by jaadu; 2011-11-15 at 04:30 PM.

  7. #7
    Administrator Ken Puls's Avatar
    Join Date
    Mar 2011
    Location
    Nanaimo, BC, Canada
    Posts
    2,195
    Articles
    57
    Blog Entries
    14
    Quote Originally Posted by jaadu View Post
    I did not know this post was going to be a cross post with other sites.
    Uhh... you posted them, right? How could you not know? The action of cross posting is not about the resolution, it's about posting the original questions to multiple sites. Here, have a read of this article. It explains the issues fairly thoroughly.

    I tried to remove my post from the other two sites, but was unable to do so from both sites. Can you tell me how I can remove/delete/edit my post from ozgrid and mrexcel?
    Nope, I can't tell you... I'm not actually sure how. I would actually be surprised if they let you.

    My suggestion would be to post a link in both forums back to this thread, letting them know that you got an answer here. It might not be a bad idea to also apologize for the cross posting too, as one of them does have a slightly harsher view on cross posting than I do.

    Thanks and sorry for the dupe posts.
    No harm done here as long as you get to it before anyone else answers. Just please do read the article.
    Ken Puls, FCPA, FCMA, MS MVP (Excel)

    Master your data with Power Query: Purchase your copy of my book M is for Data Monkey today!

    Main Site: http://www.excelguru.ca -||- Blog: http://www.excelguru.ca/blog -||- Forums: http://www.excelguru.ca/forums
    Check out the Excelguru Facebook Fan Page -||- Follow Me on Twitter

    If you've been given VBA code (a macro) for your solution, but don't know where to put it, CLICK HERE.

  8. #8
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    170
    Articles
    0
    Quote Originally Posted by Ken Puls View Post
    It might not be a bad idea to also apologize for the cross posting too, as one of them does have a slightly harsher view on cross posting than I do.
    Yeah, those MrExcel guys are real bears!
    Circumference of a circle = 2πr²



    ²the circle's radius

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •