Bridge data from Website into Excel

I am trying to come up with a simplified way (instead of copy and
pasting thousands of lines weekly) to pull data from a specific web
site and pull the data into an excel file.

Where I can I begin to find information on this topic? Is there a way
to implement a query through excel to scan a site to pull specific data?

Need to go to:
http://formularyfinder.medicare.gov/...electstate.asp

Have the query go through and select each state, then choose specific
drug.
List all plans from that state... then go through each plan and pull
the data from each.

The only way I can do this right now is by going through each state and
each formulary listing, then manually copy and pasting each plans
status.

There must be an easier way... Any suggestions?

[email protected] wrote:
> I am trying to come up with a simplified way (instead of copy and
> pasting thousands of lines weekly) to pull data from a specific web
> site and pull the data into an excel file.
>
> Where I can I begin to find information on this topic? Is there a way
> to implement a query through excel to scan a site to pull specific data?

Look up the topic "create a web querry" in the Excel helps.
Basically you create a hyperlink that copies data from the
web page directly onto a sheet in an Excel file.

Some caveats: You may find that you have some difficulty
with formatting, empty cells, cells with just a 0 in them,
and other intermittent things. You should do lots of
testing on this to be sure you are correctly handling any
situation that might arise.
Socks

Here is a url for a selected state,drug, etc.
http://formularyfinder.medicare.gov/...ics=true&cnt=1

You can modify by
State=TX&plan
State=" & item in list & "&plan

You would have to create a program with a looping macro to go thru a list of
states and a list of drugs within the loop. It appears that this one would
be tough.

--
Don Guillett
SalesAid Software
[email protected]
<[email protected]> wrote in message
news:[email protected]...
> Need to go to:
> http://formularyfinder.medicare.gov/...electstate.asp
>
> Have the query go through and select each state, then choose specific
> drug.
> List all plans from that state... then go through each plan and pull
> the data from each.
>
> The only way I can do this right now is by going through each state and
> each formulary listing, then manually copy and pasting each plans
> status.
>
> There must be an easier way... Any suggestions?
>

[email protected] wrote:
> Need to go to:
> http://formularyfinder.medicare.gov/...electstate.asp

When I click on that link, I get redirected to

http://www.medicare.gov/

which does not seem to have the data you are referring to.
At least, it does not seem to be on the first page that comes
up. It might be behind one of the links. That may cause you
some serious complications. You may have to put several
web queries in a row together. I don't know if that will even
work. Might be that this web page is not available through
a web query.

> Have the query go through and select each state, then choose specific
> drug.
> List all plans from that state... then go through each plan and pull
> the data from each.
>
> The only way I can do this right now is by going through each state and
> each formulary listing, then manually copy and pasting each plans
> status.
>
> There must be an easier way... Any suggestions?

What you may wind up having to do is construct the URL that shows
up in the Address window on your browser when you go through the
steps to pull out the data by hand. Do the process manually, and at
each step copy the entire address window into your text editor.
Then see if you can figure out what each step does. Then experiment
with some web query construction to see if you can pull the data
you need.

The cases where I have used a web query include data tables that
the authors of the tables wanted published. They deliberately made
their web site such that it was easy to read through a web query.
Socks