Posts by EnginerdUNH

    Thanks Carim! I'm not sure why my formula wasn't working even though I was pressing CTRL + SHIFT + ENTER after constructing the formula. Nevertheless, I tried your formula and for some reason excel liked your way better. Now I actually get what I'm trying to find. Thanks for the help!

    ,Hi,


    I'm working with data where I've been given a table of dates and times corresponding to different events. What I am trying to do now is group those times together to find both the percentage of events that occur during specific time range and then to also find the median event time for that range. For example, I need to find both the number of events occurring between 12:00AM - 5:59AM, 6:00AM to 11:59AM, 12:00PM - 5:59PM and 5:59PM - 11:59 PM. Finding the number of events was easy, I just used a simple COUNTIFS statement to count all times between the bounds of each bin. As for the median time calculation, I'm getting stuck. I tried using the following formula to no avail: MEDIAN(IF(AND(Table1[Time]<=X2,Table[Time]),Table1[Time])) where Table1[Time] represents the entire set of time data in Table1 and X2 and Y2 represent the first bin of time range (12:00AM - 5:59PM) but it kept throwing back what appears to be the median of the entire data set. I was thinking I'm better off trying to come up with a macro that will loop through each of the data points and using and if statement, add them to the correct array to then take the median of. Any insight and help on structure/syntax is greatly appreciated.

    If the values are numbers you can use COUNT rather than COUNTA, if the values are text use

    =LOOKUP(2,1/(E2:E10<>""),E2:E10)

    COUNT is exactly what I needed!! I realized after I posted this that I posted under the VBA section of the forum rather than the excel formulas section but thank you for providing me the answer that I needed, Fluff13.

    I'm sure I'm not the first person to have this question but I haven't been able to find anything online to help me solve my problem. I have a table of data and I need to pull the data from the last non-blank row in one of the columns in my table. Sounds simple enough but that's where you would be wrong. Normally I would be fine using =INDEX(Table1[Column X],COUNTA(Table1[Column X])) to find the last non-blank row of data. The problem with is that the column that I need to pull data from in the table that I'm working with isn't truly blank. Instead, there's a formula which is telling it to check for a certain condition, if that condition is met then spit out a value else the value in that cell is "". Even though the value in that cell may be "", since there is a formula which tells excel how to populate that cell, when using INDEX and COUNTA, excel treats the cell as if it's not blank and returns the wrong answer. What I need to figure out is how to find the last row which contains something other than "". For example, let's say I have the following table:

    DateData 1Data 2Data 3Sum
    January 20201236
    February 202054615
    March 202028313
    April 2020

    In the example table, let's say I need to pull data from the "Sum" column. In the example, the sum column would be populated with =IF(SUM(Data 1:Data 3)>0,SUM(Data 1:Data 3),""). If I were to use =INDEX(SUM,COUNTA(SUM)) then excel would return "" because April 2020 shows "" and is not truly blank because there is a formula inside the cell. What excel SHOULD be returning is March 2020's value of 13.

    After a little hunt...and an accidental mouse click...I have found where worldometers is keeping their data for the historical charts!!! For anyone who is interested, the data can be accessed by telling your macro to look through worldometers HTML for any script tags. I have used the following which gives me access to the data that I need

    All I have left to do now is just clean it up a bit more.

    Hi Alan, first of all, thank you for your reply! I looked into using a Power Query (something I had never heard of before Saturday) as it was a technique that was posted on another forum. Unfortunately, a Power Query won't work for what I am trying to do. The Power Query tool will only grab tables from the given website which is ok if all you want to do is look at the current numbers. In my case, I care about all the historical data which unfortunately is changing every couple of days it seems so the single day historical data that you may have gotten for a week ago, probably isn't correct anymore, unless all you're doing is just refreshing and looking at the current day and then you don't care about where you were on other days. What I need to be able to do is find a way to get the historical data off of the charts listed below the tables of the current day's and yesterday's data which as far as I can tell, they only provide you with a few sources of where the data comes from but not the data that makes up the charts and I have not been able to figure out how to extract the data from the charts themselves.

    Hi,


    I have been tracking all of the COVID 19 data for the US and my particular state since very early this year and especially as of recently, I have found it to be very time consuming because it seems like every couple of days, the reported number of total cases, active cases, and deaths for the country will change for days that were previously reported making it a hassle to have to go back and confirm the data for every day I've already entered. I am using two different websites to get my data, https://www.worldometers.info/coronavirus/country/us/ for my US historical data and https://covidtracking.com/data/state/connecticut for my particular state's historical data. Coming up with the code to pull up the website for my state, find the historical data table and pull the data was relatively easy because they have the data set up exactly as that, in tables.


    Worldometers on the other hand, has a single table at the top of the page which has two hyperlinks or tabs basically which show the current day and previous day's data for each state. For the historical data, the site displays the data on a series of interactive charts which allow the user to view the highlighted day's data by hovering over the data point with their cursor. Because this is not a table, I have having trouble with trying to figure out how to extract the individual data points like I was able to do from the table in my state's historical data. I have provided a screenshot which shows both how the data is displayed when a user hovers over a particular data point as well as the respective HTML code for that data point. I'm not sure if there is a way to scrape all of the data together and then I can go through and organize/analyze it how I want to or if I am better off trying to scrape each individual point separately. I am relatively new to HTML scraping so I apologize in advance if I don't say something as clear as you would like and I can gladly try and explain things differently.


    Wow Bosco thank you so much!! That is exactly what I was looking for. My last question though is the formula you gave me assumes that all I have is 10 data points and that the data never changes size. The data I'm working with is in a table that can change in size if more information is added. I modified your formula for my purposes to reference the Table[Date] and Table[Difference] instead where you mention A2:A10 and C2:C10 in your example. My question for you is how do I modify the formula to handle the A1:A9 when the table changes in size?

    Bosco, as I stated in my original post, I cannot use the formula you are proposing for column C. I must calculate the difference between the current and previous value for all data points not just the ones which occur on the same day. The question was how to arrange a formula which will allow me to calculate the daily average of those differences, excluding the first value for each day.

    Hi,


    I am working with data that has a a series of dates and then data for each date. I then take each number from the data column and take the difference between each value and the value in the row above it. What I am looking to do is now take the average of those differences based on the corresponding date but I need to exclude the first difference for each date from the average since that difference is based on the difference between data on two days. I know I could just use a formula which would skip calculating a difference if the dates don't match and then calculate an average using the AVERAGEIF function but I need to keep the rows with differences between dates for another formula later on. Below is a table of dummy data showing what I would expect for the averages column. Please note that as I have shown below, the formula needs to be dynamic meaning that each day may a different number of data rows to average compared to the previous day or next day.


    DateDataDifferenceAverage
    5/23/20205---2
    5/23/20203
    -2-2
    5/23/20201-2-2
    5/24/202010
    9-8
    5/24/20202
    -8-8
    5/25/20201816-4
    5/25/20203-15-4
    5/25/202041-4
    5/24/202062-4

    [SIZE=13px]I have since changed the code where I am entering the user information into the website input fields to the following for loop.[/SIZE]


    Code
    1. [align=left][COLOR=#000000][FONT=Arial]Set HTMLInputs = HTMLDoc.getElementsByTagName("input")[/FONT][/COLOR][/align]
    2. [align=left][COLOR=#000000][FONT=Arial] For Each HTMLInput In HTMLInputs[/FONT][/COLOR][/align]
    3. [align=left][COLOR=#000000][FONT=Arial] Debug.Print HTMLInput.className, HTMLInput.ID[/FONT][/COLOR][/align]
    4. [align=left][COLOR=#000000][FONT=Arial] If HTMLInput.className = "form-control input-lg" And HTMLInput.ID = "form-email" Then[/FONT][/COLOR][/align]
    5. [align=left][COLOR=#000000][FONT=Arial] HTMLInput.Value = "XXXXXXXX"[/FONT][/COLOR][/align]
    6. [align=left][COLOR=#000000][FONT=Arial] End If[/FONT][/COLOR][/align]
    7. [align=left][COLOR=#000000][FONT=Arial] If HTMLInput.className = "form-control input-lg" And HTMLInput.ID = "form-password" Then[/FONT][/COLOR][/align]
    8. [align=left][COLOR=#000000][FONT=Arial] HTMLInput.Value = "*****************"[/FONT][/COLOR][/align]
    9. [align=left][COLOR=#000000][FONT=Arial] End If[/FONT][/COLOR][/align]
    10. [align=left][COLOR=#000000][FONT=Arial]Next HTMLInput[/FONT][/COLOR][/align]


    The issue that I'm having now is that when I run the code fully, I get no output from the debug.print line. When I run it line by line, however, the code spits out exacly what it should and does exactly what I need it to do.

    Ok so I've figured out how to get the username and password into the correct input boxes on the site using the following code. The problem that I'm experiencing now is that the code works when i step through it using F8 and the fields are populated with the right info but when I try to run the code completely without using F8, I get a "Object variable or With block variable not set" error. Can anyone tell me why I'm getting this error?


    Code
    1. [align=left][COLOR=#000000][FONT=Arial][SIZE=13px]'Prompt user to enter in their login info and login to website[/SIZE][/FONT][/COLOR][/align]
    2. [align=left][COLOR=#000000][FONT=Arial][SIZE=13px] Set HTMLUser = HTMLDoc.getElementById("form-email")[/SIZE][/FONT][/COLOR][/align]
    3. [align=left][COLOR=#000000][FONT=Arial][SIZE=13px] HTMLUser.Value = "xxxxxxxxx"[/SIZE][/FONT][/COLOR][/align]
    4. [align=left][COLOR=#000000][FONT=Arial][SIZE=13px] Set HTMLPWord = HTMLDoc.getElementById("form-password")[/SIZE][/FONT][/COLOR][/align]
    5. [align=left][COLOR=#000000][FONT=Arial][SIZE=13px] HTMLPWord.Value = "**********"[/SIZE][/FONT][/COLOR][/align]

    Hi,


    I am new to HTML web scraping but let me try to summarize what I'm trying to do real quick before I get into my problem. I need to be able to perform the following steps on a website called marketingscents.com using VBA:
    1. Load the webpage
    2. Click the login button
    3. Enter username and password
    4. Click login button
    5. Access needed information


    I have been able to perform the first 2 steps using the following code


    Where I am getting stuck is on Step 3. I have combed through the HTML provided below and determined that the username and password fields that I need are input boxes.



    However, I can't even get my VBA to print the name of all the input fields on the page using Debug.Print. I am using the following code.


    Code
    1. 'Prompt user to enter in their login info and login to website
    2. Set HTMLInputs = HTMLDoc.getElementsByTagName("input")
    3. For Each HTMLInput In HTMLInputs
    4. Debug.Print HTMLForm.getAttribute("name")
    5. Next HTMLInput


    Can anyone tell me what am I doing wrong???

    I was able to figure out what was going wrong with my code. I fixed it to the following:

    Hi,


    I have a spreadsheet where I need to keep track of different tasks that need to be completed where I work. The list has a task name column, who has action, what the action is, date the action when active and a days active column. I need to use VBA to calculate the number of days active because as you will see the in attached test file with dummy data, sometimes the task will have no current action (i.e. listed as N/A), one action (i.e. single current action and current action start date), or multiple actions (i.e. two or more current actions and current action start dates). I have gotten the code below which is also included in the attached file to work for the N/A case since the code simply just checks to see if the date column says "N/A" and if yes, assigns "N/A" to the days active column in that row. When I attempt to calculate the number of days active for rows which have one or more dates, I get a type mismatch error and I'm not sure why.


    Files

    • Date Test.xlsm

      (10.32 kB, downloaded 45 times, last: )

    Hi Carim,


    Thanks for your quick response. When I went to get a copy ready to upload here for a test case, I found some hidden rows that total up the total payments by year so I am able to use the formula


    =INDEX(E24:E56,MATCH(E3,B24:B56,0))



    in order to find the year value in E24 to E56 which matches the current year (based on today's date) in E3 and then outputs the total payments from the same row in B24 to B56. Now I think I can make this work for what I need.

    Hi,



    I am working on a loan payment calculator and I have a cell which currently has the amount per year that needs to be paid extra on the loan in order to pay off the loan sooner. What I need this

    [SIZE=13px] cell to represent is the cumulative amount left out of this total based on extra payments already made. In other words, let's say the starting value is $3750 for extra payments needed and the total monthly payment required is $500. In one month, an additional $200 is paid on the loan for a total of $700. The extra payments needed cell should now reflect $3550. The trick is that when the next year starts, the amount extra needed to be paid should reset back to $3750. if it's any help, there is a payment date cell so the year can be extracted from that.[/SIZE]

    Hi,


    i am am looking for a way, whether formatting or coding, to display an integer and decimal pair as a fraction. For example, a whole number like 2 would display as that number over 1, i.e. 2/1. A number like 2.5 would display as 5/2 and not 2 1/2. Not sure if there is a simple way to make this work or not.


    Thanks,


    Morgan