Daily updated information placed in cells by month

  • I have a sheet in my workbook that updated web-site information (a table) when it is opened. I would like to update the information on a daily basis at a specific time (that's issue #1). After the information is downloaded, I would like to copy information from a specific cell in the downloaded table to a cell on a different worksheet in the workbook. When this information is downloaded, it will appear in the next cell (separated by months) as daily data. Example: Sheet2!U44 is the sum of Sheet31!K54 currently. I would like to fill in cell Sheet31!A53 thru G57, based on the day of the month (the next blank cell). The kicker is that tommorrow's data will be in Shee2!U44 when it is downloaded.


    Thanks for your help,

  • Re: Daily updated information placed in cells by month


    hey - can you attach your file - kinda difficult to follow what it is your trying to do ...


    sounds feasible enough...


    also in terms of issue #1 you could use the ontime method but my preference is just to use Windows built in scheduler to launch specific file - then just put a trigger in the workbook_open event to fire your routines as required before closing the file and killing the app.

  • Re: Daily updated information placed in cells by month


    I don't see the attachment. I attached it and uploaded. Is it there?

  • Re: Daily updated information placed in cells by month


    nope - can't be > 50 KBs .. ie must be small .. dumbed down version required perhaps?

  • Re: Daily updated information placed in cells by month


    ok I think i can see what you;re trying to do now but have one question...


    the sheet 12 month average - is that for just one Region (from Sheet1)


    else I can't work out where the number being added for each day comes from.


    as a pointer....


    I think you might find it easier to store the data in almost database format...


    that is 12 Month Average Sheet as follows:


    Column A: Date
    Column B: Weekday
    Column C: Month (if nec.)
    Column D: Number


    Then run a pivot table off this data to give you the nice layout you want - with average of course for each month.


    Regardless of the pivot - doing the above layout will make your life much simpler in the long run - ie if you had to you could scrap the idea of Pivot and just have another sheet which showed you the average by weekday for each month.


    Let us know your thoughts... and also explain the numbers to us on 12 month avg sheet ... that's the only bit I can't follow.

  • Re: Daily updated information placed in cells by month


    The numbers on 12 Month avg page are for the '05 month of July cell A41:G48. Below that are '04 July numbers. The format of Sheet1 is what is downloaded from web-site. I am just extracting cell U44 (for this report).
    The sheet '12 Month average' is really just a place to keep this data, it is gathered as needed by other worksheets (not in the attached file) for various reports.
    I sent a dummied up version to you earlier, unfortunately it was prepared by one also (me), I left off the '05 July data. Sorry about that.
    I am trying to get the month of July '05 to basically auto-populate as the data is recieved by the Sheet1, but without deleting the previously input data i.e., if I make '12 Month Average!'D46 = Sheet1U44, it would work but only keep the data until tommorow when new tables are imported into Sheet1. Sheet1 data is always imported in the same format, and I always only need cell U44 to go into '12 Month Average' worksheet.


    Hope this helps. Thanks,

  • Re: Daily updated information placed in cells by month


    I resent the booktest.xls file. If you get a chance would you mind looking? I really appreciate everything you guys do to help.


    Thanks,

  • Re: Daily updated information placed in cells by month


    ZMERC - I am not being critical but trying to do what you want with the layout you have on sheet "12 Month Average" is really not great... it is possible to find the appropriate cell but it's certainly not as straightforward as it should be.


    I think you need to really decide if you can amend the format of the 12 month average sheet so it is laid out in a logical fashion...


    I have attached an ex. of what I mean.


    I've taken the liberty of putting in a very simplistic piece of code you could use to update your 12 month data ... if you're updating the webdata remotely then you could put this in the sheet event for the datarefresh but we'd need to see the code you're using - or a snippet thereof.

  • Re: Daily updated information placed in cells by month


    Please don't worry about being critical. I am trying to recover/redo what has been handed to me, and I'm not that knowledgable about the spreadsheets. I could reformat the 12 Month Average sheet (might take some time but certainly doable). I like the Suggested Layout sheet you have in there and the Report Sheet also. Will the Suggested Layout sheet update daily (and retain the previous days data) based on the information downloaded into Sheet1? That is one of the steps I am trying to automate.
    I have 8 web-queries, and about 15 database queries that gather when the workbook opens. By the way, this workbook has about 45 worksheets, only eight of which are reports. I am trying to get this as automatic as possible, slowly but surely. Does it look to you like I might have to start fresh?


    Using your example, I don't see a formula for Suggested Layout sheet to get the data from Sheet1.


    Thanks again.

  • Re: Daily updated information placed in cells by month


    no there's no formula - you would have to via VBA ... if you used formulas it would be impossible for you to maintain the prior day's data (and all data prior to today) ... the only way to do what you want is to copy the value from Sheet1 to the appropriate cell in the "Suggested Layout" sheet.


    I have inserted a macro contained in Module 1 ... here is the code...


    Code
    Sub update_daily()
    Dim rw As Integer
    rw = Application.WorksheetFunction.Match(Sheets("Sheet1").Cells(6, 22), Sheets("Suggested_Layout").Range("A:A"), 0)
    Sheets("Suggested_Layout").Cells(rw, 4) = Sheets("Sheet1").Cells(44, 21)
    ActiveWorkbook.RefreshAll
    ActiveWorkbook.Save
    End Sub


    if you were to automate this file in full - ie open, get webdata, update 12 month data, update report etc... you would run the above via a change event - ie once the web data had been downloaded.


    I use a similar type event for a currency_matrix I populate daily... i may post that code up to so you can get the gist for how that kind of thing is done (by me anyways!!)


    eg....


    first a workbook_open event to fire off when the workbook is opened ... and to retrieve data from a website (via a stored webquery within the file).... this file is opened via a Windows Schedule event... and run via an automated network account whereusername = "FD2"... so when opened by the automated account it fires off the routine "DAILY_DATA" and then closes the file... if opened by anyone else it just present with a message.



    so presuming run by the FD2 account (ie is being automated) it then runs this code to actually update the data...



    here it loops until it has successfully retrieved the required data from the web query ....


    the rest you can ignore (its just loading data from various cells to a MySQL db for the current day & next)


    in your case you'd simply replace all that bumpf with the code that copies the cell you want to the next blank cell... (as per the first bit of code I put up)


    anyway - that's the general approach....


    for others reading this... I wrote the code a long time ago so undoubtedly there are tweaks... but as it takes less than about 5 seconds to run in full I am not that fussed :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!