Solved

How can I use a macro to import specific weather data into a selected Excel cell?

Posted on 2014-04-15
23
1,386 Views
Last Modified: 2014-04-17
Hi all,

New to vba and looking for a quite specific solution to a work project.  Wondering if it's possible to import a weather METAR from wunderground.com to the selected cell based on the date/time in the same row?  I've included my workbook, which includes a sample macro for pulling data from wundergound.  Basically, I'd like the macro to look at the year in "B", month in "C", day in "D" and zulu time in "O" and give me the full METAR in "U" that is closest to the time in "O".  The station will always be "KTVC".  wunderground will export all of the weather METARs for a day in a csv, which I've managed to do separately, but because of my limited vba knowledge, am stuck there.

I also have a separate macro, modified from a version found on this site for my use, to parse the imported METAR into "V:AG".  Next question would be, is it possible to do both actions in one macro?  That is selected and row in "U" and have it pull the data and parse?

Thanks!
Cancels---metar---ee.xlsm
0
Comment
Question by:iceman23
23 Comments
 
LVL 48

Expert Comment

by:Rgonzo1971
Comment Utility
Hi,

Which version of Excel do you have?

Regards
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
1. If you get the CSV result, you don't need to parse any METARS.  Why post the METAR in addition to the parsed/relevant weather data?
2. Why bother with Zulu time when weather underground will display the local time EDT?
3. Are you going to have lots of flights to correlate with weather data?
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
4. why change the order of the columns?  The CSV comes in with this order:
Dew PointF	Humidity	Sea Level PressureIn	VisibilityMPH	Wind Direction	Wind SpeedMPH	Gust SpeedMPH	PrecipitationIn	Events	Conditions	WindDirDegrees

Open in new window

0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
5. Is there a reason to get your data from weather underground and not some other source?
0
 
LVL 31

Expert Comment

by:Rob Henson
Comment Utility
If you already have the exported data for a larger time period, you can use an INDEX/MATCH formula to extract the specific day and/or time from the dataset.

Thanks
Rob H
0
 

Author Comment

by:iceman23
Comment Utility
Thanks for the responses,

I have Office 2013

1. Obviously, this is for aviation.  I'm to have the raw METAR with the parsed results.
2. Again, zulu is used in aviation.
3. Yes, I currently have over 1600 entries and the list will continue to grow.  It is a log of cancellations.
4. Becuase I want the parsed data in the order of the METAR and wunderground parses the METAR into different units.  The goal is to preserve the data in it's originial state.
5. wunderground is really the easiest I've found.  If you have another suggestion, please don't hesitate.

My data set goes back to 2003, so I was hoping to not have a separate sheet or whatever of 10+ years of METARs.

Currently, I'll go to the wunderground site and copy/paste the specific METAR into my sheet and parse the results.  Not a huge deal, was just looking for a way to automate the process to make it easier for others to use.

Thanks!
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
I think you have a couple of problems.  The first is doing a bulk update of the 1600+ rows you currently have.  The second is the update of newly added rows.  For the first one, I would look at possibly getting a bulk dump of data.  In the second one, it is probably best to get a day's worth of data.

If you want to do this efficiently, you probably should use an object like MSXML2.HTTP to get the data, rather than populate a worksheet with querytable results.  How are your parsing skills?
0
 

Author Comment

by:iceman23
Comment Utility
Yes, I know the tough part is bulk of what I have, which is why I wanted it to work based on a selection.  I know that is going to take time, was looking more for after I get the current data set, then I'd only be working with singles at a time as cancellations come in.

As for parsing, the limited knowledge I have is from looking at code, modifying it and watching the result to learn.  So I stress, limited.

Thanks
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
It appears that wunderground.com withholds the METAR data from CSV results when you use MSXML2.XMLHTTP to get to the data. :-(

I'll play with IE automation and see if that works around their restriction or if another source can be queried.
0
 

Author Comment

by:iceman23
Comment Utility
Awesome, much appreciate the effort!
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
Comment Utility
Looks like IA State will be the best source for your data.  I requested a tab-delimited format from your example date and received the following:
#DEBUG: Format Typ    -> tdf
#DEBUG: Time Zone     -> GMT
#DEBUG: Entries Found -> 24
station valid (GMT timezone)    tmpf     dwpf    relh    drct    sknt    p01i    alti    mslp    vsby    gust    skyc1   skyc2   skyc3   skyc4   skyl1   skyl2   skyl3   skyl4   metar
TVC 2003-06-16 00:53    62.06   51.08   67.24   90.00   4.00    M   30.13   1020.40 10.00   M   OVC None    None    None    22000.00    M   M   M   KTVC 160053Z 09004KT 10SM OVC220 17/11 A3013 RMK AO2 SLP204 T01670106   
TVC 2003-06-16 01:53    60.98   50.00   67.11   0.00    0.00    M   30.14   1020.60 10.00   M   FEW SCT BKN None    7000.00 14000.00    22000.00    M   KTVC 160153Z 00000KT 10SM FEW070 SCT140 BKN220 16/10 A3014 RMK AO2 SLP206 T01610100 
TVC 2003-06-16 02:53    55.94   50.00   80.40   0.00    0.00    M   30.16   1021.20 10.00   M   FEW SCT BKN None    7000.00 14000.00    22000.00    M   KTVC 160253Z 00000KT 10SM FEW070 SCT140 BKN220 13/10 A3016 RMK AO2 SLP212 T01330100 53009   
TVC 2003-06-16 03:53    55.04   51.08   86.47   0.00    0.00    M   30.16   1021.30 10.00   M   SCT None    None    None    14000.00    M   M   M   KTVC 160353Z 00000KT 10SM SCT140 13/11 A3016 RMK AO2 SLP213 T01280106   
TVC 2003-06-16 04:53    53.96   50.00   86.41   0.00    0.00    M   30.16   1021.30 10.00   M   SCT None    None    None    22000.00    M   M   M   KTVC 160453Z 00000KT 10SM SCT220 12/10 A3016 RMK AO2 SLP213 T01220100 402280083 
TVC 2003-06-16 05:53    53.96   48.92   83.00   0.00    0.00    M   30.17   1021.50 10.00   M   CLR None    None    None    M   M   M   M   KTVC 160553Z 00000KT 10SM CLR 12/09 A3017 RMK AO2 SLP215 T01220094 10200 20122 53003    
TVC 2003-06-16 06:53    53.96   48.02   80.25   0.00    0.00    M   30.16   1021.30 10.00   M   SCT BKN None    None    14000.00    22000.00    M   M   KTVC 160653Z 00000KT 10SM SCT140 BKN220 12/09 A3016 RMK AO2 SLP213 T01220089    
TVC 2003-06-16 07:53    48.92   46.94   92.84   0.00    0.00    M   30.17   1021.60 10.00   M   BKN None    None    None    22000.00    M   M   M   KTVC 160753Z 00000KT 10SM BKN220 09/08 A3017 RMK AO2 SLP216 T00940083   
TVC 2003-06-16 08:53    46.94   46.94   100.00  0.00    0.00    M   30.18   1022.20 10.00   M   SCT SCT None    None    14000.00    22000.00    M   M   KTVC 160853Z 00000KT 10SM SCT140 SCT220 08/08 A3018 RMK AO2 SLP222 T00830083 53006  
TVC 2003-06-16 09:53    46.04   46.04   100.00  0.00    0.00    M   30.19   1022.50 10.00   M   SCT BKN None    None    14000.00    22000.00    M   M   KTVC 160953Z 00000KT 10SM BCFG SCT140 BKN220 08/08 A3019 RMK AO2 SLP225 T00780078   
TVC 2003-06-16 10:53    46.94   46.04   96.66   0.00    0.00    M   30.20   1022.90 10.00   M   SCT BKN None    None    14000.00    22000.00    M   M   KTVC 161053Z 00000KT 10SM BCFG SCT140 BKN220 08/08 A3020 RMK AO2 SLP229 T00830078   
TVC 2003-06-16 11:53    53.96   46.94   77.06   0.00    0.00    M   30.22   1023.20 10.00   M   FEW SCT None    None    15000.00    25000.00    M   M   KTVC 161153Z 00000KT 10SM FEW150 SCT250 12/08 A3022 RMK AO2 SLP232 T01220083 10128 20072 53011  
TVC 2003-06-16 12:53    59.00   44.06   57.57   0.00    0.00    M   30.22   1023.40 10.00   M   FEW SCT None    None    15000.00    25000.00    M   M   KTVC 161253Z 00000KT 10SM FEW150 SCT250 15/07 A3022 RMK AO2 SLP234 T01500067    
TVC 2003-06-16 13:53    62.96   44.96   51.77   40.00   3.00    M   30.22   1023.40 10.00   M   FEW SCT None    None    15000.00    25000.00    M   M   KTVC 161353Z 04003KT 10SM FEW150 SCT250 17/07 A3022 RMK AO2 SLP234 T01720072    
TVC 2003-06-16 14:53    66.02   46.04   48.47   M   4.00    M   30.22   1023.20 10.00   M   FEW SCT None    None    15000.00    25000.00    M   M   KTVC 161453Z VRB04KT 10SM FEW150 SCT250 19/08 A3022 RMK AO2 SLP232 T01890078 50000  
TVC 2003-06-16 15:53    69.98   44.06   39.22   10.00   5.00    M   30.20   1022.70 10.00   M   FEW SCT SCT None    4500.00 15000.00    25000.00    M   KTVC 161553Z 01005KT 10SM FEW045 SCT150 SCT250 21/07 A3020 RMK AO2 SLP227 T02110067 
TVC 2003-06-16 16:53    71.96   44.96   37.95   20.00   6.00    M   30.19   1022.40 10.00   M   FEW SCT SCT None    4500.00 15000.00    25000.00    M   KTVC 161653Z 02006KT 10SM FEW045 SCT150 SCT250 22/07 A3019 RMK AO2 SLP224 T02220072 
TVC 2003-06-16 17:53    75.02   44.96   34.24   330.00  6.00    M   30.18   1022.00 10.00   M   FEW SCT SCT None    4500.00 15000.00    25000.00    M   KTVC 161753Z 33006KT 10SM FEW045 SCT150 SCT250 24/07 A3018 RMK AO2 SLP220 T02390072 10244 20122 56010   
TVC 2003-06-16 18:53    77.00   46.04   33.39   320.00  7.00    M   30.17   1021.70 10.00   M   FEW SCT SCT None    4500.00 15000.00    25000.00    M   KTVC 161853Z 32007KT 10SM FEW045 SCT150 SCT250 25/08 A3017 RMK AO2 SLP217 T02500078 
TVC 2003-06-16 19:53    71.06   51.08   49.23   50.00   7.00    M   30.16   1021.20 10.00   M   FEW SCT SCT None    4500.00 15000.00    25000.00    M   KTVC 161953Z 05007KT 10SM FEW045 SCT150 SCT250 22/11 A3016 RMK AO2 SLP212 T02170106 
TVC 2003-06-16 20:53    68.00   48.92   50.44   30.00   9.00    M   30.15   1021.10 10.00   M   FEW SCT None    None    15000.00    25000.00    M   M   KTVC 162053Z 03009KT 10SM FEW150 SCT250 20/09 A3015 RMK AO2 SLP211 T02000094 56012  
TVC 2003-06-16 21:53    66.92   48.02   50.62   30.00   6.00    M   30.14   1020.80 10.00   M   FEW SCT None    None    15000.00    25000.00    M   M   KTVC 162153Z 03006KT 10SM FEW150 SCT250 19/09 A3014 RMK AO2 SLP208 T01940089    
TVC 2003-06-16 22:53    68.00   46.04   45.26   20.00   6.00    M   30.13   1020.30 10.00   M   FEW SCT None    None    15000.00    25000.00    M   M   KTVC 162253Z 02006KT 10SM FEW150 SCT250 20/08 A3013 RMK AO2 SLP203 T02000078    
TVC 2003-06-16 23:53    69.08   44.06   40.45   50.00   5.00    M   30.12   1020.00 10.00   M   FEW SCT None    None    15000.00    25000.00    M   M   KTVC 162353Z 05005KT 10SM FEW150 SCT250 21/07 A3012 RMK AO2 SLP200 T02060067 10250 20189 56009  

Open in new window

Note: CSV is also available.

To consume this data, I created the following function.  I pass it a Zdatetime value and receive the METAR string that is associated with the nearest time stamp.
Option Explicit


Public Function GetClosestMETAR(parmZDatetime As Date) As String
    Static oXML As Object
    Static oRE As Object
    Dim oMatches As Object, oM As Object
    Dim dtCurrentZ As Date
    Dim lngMinDifference As Long, lngCurrentDifference As Long
    Dim strNearestMETAR As String
    Dim strResult As String

    If oXML Is Nothing Then
        Set oXML = CreateObject("MSXML2.XMLHTTP")
        Set oRE = CreateObject("vbscript.regexp")
        oRE.Global = True
        oRE.Pattern = "TVC\s(\d\d\d\d-\d\d-\d\d\s\d\d:\d\d)\s.*(KTVC .*)"
    End If
    
    oXML.Open "GET", "http://mesonet.agron.iastate.edu/cgi-bin/request/getData.py?station=TVC&data=all&year1=" & Year(parmZDatetime) & "&year2=" & Year(parmZDatetime) & "&month1=" & Month(parmZDatetime) & "&month2=" & Month(parmZDatetime) & "&day1=" & Day(parmZDatetime) & "&day2=" & Day(parmZDatetime) & "&tz=GMT&format=tdf&latlon=no", False
    oXML.Send
    
    If oXML.Status = 200 Then
        lngMinDifference = 86400
        strResult = oXML.responsetext
        If oRE.test(strResult) Then
            Set oMatches = oRE.Execute(strResult)
            For Each oM In oMatches
                dtCurrentZ = CDate(oM.submatches(0))
                lngCurrentDifference = Abs(DateDiff("s", dtCurrentZ, parmZDatetime))
                If lngMinDifference > lngCurrentDifference Then
                    lngMinDifference = lngCurrentDifference
                    strNearestMETAR = oM.submatches(1)
                End If
            Next
        End If
        GetClosestMETAR = strNearestMETAR
    End If
End Function

Open in new window

Note: if you are going to do time matching very early or very late in a day, then the code will probably need to be tweaked to 'window' the time stamp.

The good news is that IA State's servers are fast.

You might save yourself some coding time if the IA State data that appears before the METAR is in the right units.  The function could easily be tweaked to return the entire line instead of just the METAR string.

==========edited by aikimark to add
Dim strResult As String
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:iceman23
Comment Utility
Works great!  Only quirk is IA State's site does not archive special reports, only hourly observations.  Advantage to wunderground is they archive this data.  It is prefaced as "SPECI" instead of "METAR", as seen here:

http://www.wunderground.com/history/airport/KTVC/2011/6/21/DailyHistory.html?MR=1

Too you couldn't get it to work.

Thanks!
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
you didn't mention special reports
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
I think the special data is there in the IA State data.  I queried the 6/21/2011 data and they seem to be the same, except for the "SPECI" prefix.  The IA State data lacks any prefixes.
SPECI KTVC 210537Z AUTO 00000KT 10SM BKN028 BKN035 OVC070 19/17 A2981 RMK AO2 TSNO $
SPECI KTVC 210544Z AUTO 00000KT 10SM SCT028 SCT037 OVC075 19/17 A2981 RMK AO2 TSNO $

KTVC 210537Z AUTO 00000KT 10SM BKN028 BKN035 OVC070 19/17 A2981 RMK AO2 TSNO $
KTVC 210544Z AUTO 00000KT 10SM SCT028 SCT037 OVC075 19/17 A2981 RMK AO2 TSNO $

Open in new window

0
 

Author Comment

by:iceman23
Comment Utility
Sorry, I should have mentioned it. I falsely assumed any place that archived metar data would also include the specials. IA only archives auto reports from the sensor. Sorry, but thanks anyway.
0
 

Author Comment

by:iceman23
Comment Utility
You, of course, are right. Fixed my date formula and it works perfect. Sorry, was a long day. Thanks again!!
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
I would be interested to know how fast you can populate your current 1600 rows.
0
 

Author Comment

by:iceman23
Comment Utility
Atom @ 2.13GHz; 4GB RAM; Windows 7 64bit: 00:04:11

i7 @ 3.4GHz; 12GB RAM; Windows 7 64bit: 00:03:21

1697 Rows.  Not bad at all.  Thanks.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
Were you able to use the IA state data without having to parse the METAR string?

That's good performance.

I discovered a compile error in my posted code, caused by a missing Dim statement.  Your modules lack the Option Explicit statement that should be in all your modules' and forms' General Declarations section.
Dim strResult As String

Open in new window

I will edit my posted code to add this Dim statement for future readers.
0
 

Author Comment

by:iceman23
Comment Utility
I've updated the code, thanks.

I chose to only grab the raw METAR data, as I need it to parse a particular way.  Most crucial is sky condition.  I only want to parse BKN and OVC based on which is lower.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
Also, you should sandwich your Excel worksheet updating code in the WeatherParserTIME() routine like this:
Application.ScreenUpdating = False
'your worksheet-updating code goes here
Application.ScreenUpdating = True

============
In an earlier post, you mentioned the 'units' that were different between the METAR string and the wunderground.com values.  Did you see that same unit difference in the IA State data?
0
 

Author Comment

by:iceman23
Comment Utility
Wilco.

No, IA State allows you to select exactly what you want parsed, so that part is good.  Part that is trouble for is the sky condition and weather condition, such as snow.  I guess I could select all three "cloud coverage" and "cloud height" levels, parse them into hidden colums, then set a function in a separate cell to look for BKN or OVC and return that value.  You say it wouldn't be hard to parse the data?  I'd like to have the weather data parsed as well.
0
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
You can add a capture group to this regex pattern
oRE.Pattern = "TVC\s(\d\d\d\d-\d\d-\d\d\s\d\d:\d\d)\s(.*)\s(KTVC .*)"

Open in new window

and then use the Split() function with vbTab as the delimiter to parse that data.  If you do change the pattern, the submatches collection will change and the METAR data will be in submatchtes(2).

While it is possible to parse everything with regex, the pattern is more complex.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now