A Challenge Hourly Report

  • Hi Logit
    Here is the problem I have that you may help me to create the report I need... My knowledge of VBA is as a beginner and think this report requires much more experience and knowledge of programming. Here is the situation: I have a list of Plant Generators and a list of data info regarding the energy (KWh) delivered to the system. The info, each lecture, is sent every 15 minutes everyday. It means that in a month, let's say January, the monthly report for every generator needs to be 2,976 records (31 days x 24 hrs/day x 4 times/hr), there are 40 generators, so the list is huge (around 119k records), but I have just attached the file with a little portion as sample.


    The report for every generator needs to be minimized in an hourly version adding all the kwh delivered every 15 minutes of the hour. It will be an easy task IF the records stay perfect (4 records every hour) since it would only be necessary to have a loop that adds the records every 4 times; however, sometimes a record is missing and it is when it gets complicated because you cannot add the first record of the next hour but instead to add only the 3 records within that hour and so for. At the end, a report of exception (missing records) need to be created.


    I am attaching the file where I have done the programing but as a total version (not hourly), IF possible, could you help me on this? Any kind of help either programming or idea of how to do it, will be appreciated. Thanks

  • Re: A Challenge Hourly Report


    Hello,


    In order to spot a missing record ...


    In cell E3 ... =IF(ROUND((C3-C2),8)=ROUND(15/1440,8),"","oops")


    HTH

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: A Challenge Hourly Report


    Hi Claim
    I worked the macro to do the report. However, it is without considering the missing records. I intentionally deleted 1 record to check if the macro considers it but it is fixed to read and summarize every 4 records independently of the time period. Could you please help me on fixing the macro to read 4 only the records within the time of 1 hr as I explained in the first post? Also, my macro is not completing the last 2 records at the end of the report. Thanks

  • Re: A Challenge Hourly Report


    Hello Edmundo,


    Congratulations on your macro ... !!!


    You are an Expert ... !!! :wink:


    As a starting point ... attached is a small modification ...


    Hope this will help


    :smile:

  • Re: A Challenge Hourly Report



    Thanks Carim, it is fantastic! your are fast!. Just one thing... the data for the missing record is not included in the report. For example, if there needs to be 4 records withing 1 hr (15 min each) and one record is missing, the macro need to only account for the rest records, in this case 3 records instead of 4 and add the answer in the report. In your code, it is not reported at all. The report is missing the 15,110.61 KWh missing for the 3 records from 4:00 to 4:45. In other words, it always need to read, add the KWh and include it in the report no matter how many records (1,2,3, or 4 maximum). It will only be not reported in the case the 4 records are missing.


    Thanks

  • Re: A Challenge Hourly Report


    Hello,


    You are welcome ... :wink: Glad it is helping you out ...


    My understanding was that, in case of one record missing ..., you needed the "whole hour" to be skipped ...


    In fact with the exact same code, according to your needs, you can manipulate the variable x to get exactly what you want as an end result ...


    Cheers

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: A Challenge Hourly Report


    Hello Edmundo,


    Could you precisely describe what you would like to achieve when records are missing ...

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: A Challenge Hourly Report


    Hello Carim


    The report is based on an hourly segments. Each segment has to have (ideally) 4 records (every 15 min). If 1 or more records are missing, I need to get the total of the remaining records. Example: if 2 records are missing, get the total of the others 2 records and continue with the next segment, BUT do not take the missing records out of the next segment. Hope this illustrate better my needs. Thanks for your help.

  • Re: A Challenge Hourly Report


    Hello Carim


    I found a way to overcome the missing records!!. It is creating another macro (subroutine) to first go over all records and find the missing records (if difference in time is 15 min) and inserting lines for every missing record, and then we can run the report for summarizing the hourly data for every 4 records since now we will be sure there will always be 4 records for every segment of 1 hr.


    The only little problem I have now is when the plant (column B "idclient") changes to a new Plant (see row # 2974) because the previous Plant already got their 31 days and new Plant will start on day 1 where the conditions of 15 min will not longer be valid. How to do this? maybe using a While...end loop? Can you help me to add it to the macro that insert lines?


    Thanks .

  • Re: A Challenge Hourly Report


    Hello again,


    Have not yet checked your latest macro ...


    Meanwhile, attached is Version 3 for you to review ...(NOT skipping missing records)


    HTH

  • Re: A Challenge Hourly Report


    Hello
    Thanks, that was a Smart approach and I think you are getting very close to the final solution :wink: I said this because the macro is taking the records of next segment of hrs instead of the missing records. I think the variable D you added will not always test x+3 because of the missing record. I think your latest versión would be wonderful if adding the row of the missing record, maybe a combination of your latest versión and mine. I am glad for all your help!

  • Re: A Challenge Hourly Report


    Hello Edmundo,


    During my tests ... I did check "Energia" y todo esta perfecto ... :wink:


    Can you tweak your latest version with the variable D ... ???

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: A Challenge Hourly Report


    Hola Carim


    Energia looks perfecto but if manually you add up the total energía of the segment of missing records (ejemplo: only 3 records instead of 4) you will notice it is not what comes out in the report. You can intentionally erase some records (either 1, 2, or 3 of the segment of 4 record/hr) you will notice the total reported if not the sum of the remaining records of the same segment, it is taking the records of next segment. My latest versión can be tweaked, but if I do not resolve the problema of the change of "idClient", it will not work (will probably give me and endless loop). But I will keep trying.


    Saludes, y gracias por su ayuda..

  • Re: A Challenge Hourly Report


    Hello,


    If you need further assistance ...do not hesitate to come back to the Forum ....:wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: A Challenge Hourly Report


    Hi Carim
    Thank, I am done with my project, I finally found the way to fix all of my programing problems. I used a sample of the data to run it and it ran perfect. However, now that I want to run it with the entire set of data (104,226 records) the subroutine "InsertarLineas" is giving me an "overflow" error. I even declared the lastrow variable to "Long" but I am still getting the error. Is there any other variable that is affecting the overflow? I even test it declaring the variable "X" to "Long" too and still the same overflow error. Why the subroutine "HourlyReport", if desactivating calling the "InsertarLineas" subroutine, is not giving the same error if going thru the same amount of data set?


    I cannot load the file with all set of data because of size limit, so I am attaching it with the sample size. Thanks

  • Re: A Challenge Hourly Report


    Hello Edmundo,


    Congratulations on your solution ...:wink:


    To avoid "overflow" error and be on the safe side, for the variables which will reach huge numbers, you should declare them As Double ...


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

  • Re: A Challenge Hourly Report


    Thanks
    Yes, it now runs but I got an endless loop. Can you check the code I sent you yesterday? It is strange becuase with the sample data set (6,000 records or more) it runs perfect, but with the entire data set (104K records) it gets stuck. Thanks

  • Re: A Challenge Hourly Report


    Hello,


    Since I cannot replicate the problem you are facing ... have added a condition to exit loop and sub ...


    To be tested with your own database ...


    Hope this will help

Participate now!

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