Bridge data from Website into Excel

C

cgsamaras

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?
 
C

cgsamaras

Need to go to:
http://formularyfinder.medicare.gov/formularyfinder/selectstate.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?
 
P

Puppet_Sock

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?
Click to expand...

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
 
P

Puppet_Sock

Need to go to:
http://formularyfinder.medicare.gov/formularyfinder/selectstate.asp
Click to expand...

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?
Click to expand...

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
 
Top