Results 1 to 14 of 14
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    daily updates to a remote cell (2000)

    Problem: I have a file that I update every day with stock prices. Worksheet No. 1 computes a value of a hypothetical portfolio based on the prices I update the file with. What I would like to do is everytime I update the file (i. e., every day) I would like the value (let's say it is at cell A10 on worksheet No. 1) to be directed to worksheet No. 2 in another cell, so that at the end of the month, I will have each day set out on worksheet No. 2.
    Any ideas?

  2. #2
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: daily updates to a remote cell (2000)

    Nobody out there has any ideas?

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: daily updates to a remote cell (2000)

    Sorry, I had read your post but the slavedrivers were upon me.

    In order to do exactly what you describe, coding would be needed, and it would be complicated by issues such as how to deal with retro adjustments, how to deal with reopens of the file on the same day, how to catch up if you were off on holiday/vacation for a bit, etc. Perhaps one of the better coders here would be interested, I'm too slow!

    Would it be better to enter your data on the daily quote history worksheet 2, and then use a formula in the hypothetical valuation worksheet 1 to look up the most recent date, or other appropriate date when required, and then use the date as a lookup for the quote? That is the way that I would set it up.
    -John ... I float in liquid gardens
    UTC -7ąDS

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: daily updates to a remote cell (2000)

    John,
    I think I like what you are saying. If you could attach a sample spreadsheet, I would much appreciate it!
    Will I be able to "tell" the worksheet to use another date from the historical date spreadsheet, or, by default, use the most recent?
    Thanks,
    Jeff

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: daily updates to a remote cell (2000)

    Have a look at the attached workbook.

    The button on the "Valuation" sheet is a command button from the Forms toolbar.
    It activates a macro UpdateHistory.
    The sheet "History" has been pre-filled with a series of dates (in dd-mm-yyyy format unless Excel converts it automatically).

    HTH,
    Hans
    Attached Files Attached Files

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: daily updates to a remote cell (2000)

    Looks like HansV has something to check out. Attached is what I was thinking of. It uses the Workbook Open event to clear the Override Date, but otherwise is pretty simple.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: daily updates to a remote cell (2000)

    John,
    I tried using yours, but couldn't get it to work-I have attached your file with data ending today (7/25). It doesn't seem to pick up the most recent trade.
    BTW, what is "Override Date"?
    Thanks,
    Jeff
    Attached Files Attached Files

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: daily updates to a remote cell (2000)

    For the workbook you attached, the fomula is picking up the most recent date, but you have not entered any data for the most recent date on Sheet 2. The last four (?) rows for Stock A and Stock B have no data; they must be completed with data.

    The "Override Date" permits you to enter another date, which must be valid in the sense that it is within the dates for which data is entered. For example you could enter April 30 , 2000 in the override date cell and it will pull up that days Quote data instead of the most recent date entered. But any date prior to 1/1/2001 would not be valid, as the data is only for 2002.

    Let me know if you don't follow me.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: daily updates to a remote cell (2000)

    I suppose that is where I am not following you-suppose I fill out Sheet 2 every day, so that the stock prices are reflected for those days. Today is the most recent date that I could enter a stock price, since the 26th-31st haven't occurred yet-understand?

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: daily updates to a remote cell (2000)

    Oops, I think I see the misunderstanding; I didn't anticipate that you would enter future dates with no data <img src=/S/blush.gif border=0 alt=blush width=15 height=15>, so you should not enter future dates on Sheet2. I can write a formula to handle future dates with no data if you wish, although it wouldn't correctly handle any Stock with zero value.
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: daily updates to a remote cell (2000)

    Better still if you could write the code so that even if there are dates (that I may set up in advance) as long as there aren't any corresponding prices, the formula would "look back" to the most recent date where there are corresponding prices!
    Thanks,
    Jeff

  12. #12
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: daily updates to a remote cell (2000)

    Jeff, see attached.
    Attached Files Attached Files
    -John ... I float in liquid gardens
    UTC -7ąDS

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: daily updates to a remote cell (2000)

    John,
    Works great!
    Thanks.
    BTW, I may have a little different twist to this issue-more later.
    Jeff

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: daily updates to a remote cell (2000)

    Hans,
    Yours works great as usual!
    Thanks,
    Jeff

Posting Permissions

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