Simple excel based web queries

[Christopher]

Hello all. I'm interested in developing an application which will,
eventually, use the NeSS Data Exchange system. I'll pretty much
exclusively want to query census data tables by postcode.

However, right now I'm trying to build up a quick and dirty excel
application in order to demonstrate my system to potential investors.

For this I simply want to use VBA QueryTables to fill out hidden excel
worksheets which I will then use as a source for some prettiness.
Importantly, during the demonstration I need to perform live queries
on any postcode area.

Currently I'm piggy-backing on certain websites that offer a subset of
various interesting data - places like uklocalarea for example. This
is great because I can simply replace the postcode in the QueryTable
connection specification and run the query again. For example I might
do:

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.somewebsitehere.com/index.php?q=cr53aa",
Destination:=Range("Sheet1! A2")), etc..

and I can swap in a different postcode if I need the same data for a
different region. All very simple in VBA.

However I'd really like to use ideally the same simple technique to
get at proper census data. Going beyond what's already readily
available on the web should be a key part of my demonstration.

Now as you people will already know, if I query some detailed census
data 'through' the neighbourhood statistics website's manual front-end
by doing:

With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.neighbourhood.statistics.gov.uk/dissemination/
LeadKeyFigures.do?
a=7&b=276750&c=CR5+3AA&d=13&e=16&g=330361&i=1001x1003x1004&m=0&r=0&s=1286805998398&enc=1"

I can't simply swap out the postcode. If I go back to
www.neighbourhood.statistics.gov.uk/dissemination and manually find
the same table using a different postcode search I notice that the the
'b', 'g', and 's' parameters change along with the postcode reference
for parameter 'c'.

So what I really want to know is the quickest and easiest way that I
can query the same table for different postcodes in vba, as above. I
realise that the full data set is not arranged by postcode - I've read
through the documents for the NeSS Data Exchange system and while it
doesn't seem very complicated my time frame is very tight and most of
the systems I'll need to use are unfamiliar to me right now. I'm
working on this in my spare time and I really need to have everything
finished up in about 2 weeks or so - there's still lots of other
things I need to do also :(

I come from a C++ background and have only been using VBA for a few
weeks. At this point even taking the reasonably simple looking XML-
style output from the Disco and Deli queries I see from the
documentation I can perform, and extracting what I need seems beyond
the scope of my tight deadline. Maybe not though - maybe someone can
easily point out how this can be dumped into a worksheet and I can
quiz the result in a consistent way.

I suspect that I'm going to have to do something like that,
unfortunately, but really any help at all will be hugely appreciated.
I was hoping that there might be some kind of lookup table I could
generate (or someone could generate for me ;) that I could embed in my
excel workbook and quiz in a certain way to find out the correct
values for the b, g, and s parameters I notice changing in the queries
I described above. The fact that those parameters seem consistent with
the postcode search I do gives me hope on that count :) This type of
thing seems like a task for Disco

Anyway, as I said any help will be greatly appreciated. It would be a
big shame if I had to fallback to using the other web queries as the
more detailed data I've been using makes all the difference to my
demo. I only wish I had a little more time to do this properly. If the
demo goes well then that should hopefully be the case :)


Christopher

[Frank]

Christopher, while we can give generic advice advice on using NDE,
resource restrictions make it very difficult to provide specific
solutions to individual reqirements or projects.

Regarding your query, it looks like you would be best using discovery
methods to find the area for a postcode, dataset family and variable
ids, then a delivery method to find the data.

A rough example would be:

DISCO queries

QUERY:

http://neighbourhood.statistics.gov.uk/NDE2/Disco/SearchSByAByPostcode?LevelTypeId=13&Postcode=cr53aa

RETURNS:

- <ans1:SearchSByAByPostcodeResponseElement xmlns:ans1="http://
neighbourhood.statistics.gov.uk/nde/discoveryservice">
- <ns0:AreaFallsWithins xmlns:ns0="http://
neighbourhood.statistics.gov.uk/nde/v1-0/discoverystructs">
- <ns0:AreaFallsWithin>
  <ns0:FallsWithin xsi:nil="1" xmlns:xsi="http://www.w3.org/2001/
XMLSchema-instance" />
- <ns0:Area>
  <ns0:LevelTypeId>13</ns0:LevelTypeId>
  <ns0:HierarchyId>2</ns0:HierarchyId>
  <ns0:AreaId>276750</ns0:AreaId>
  <ns0:Name>Croydon</ns0:Name>
  </ns0:Area>
  </ns0:AreaFallsWithin>
  </ns0:AreaFallsWithins>
  </ans1:SearchSByAByPostcodeResponseElement>

QUERY:
http://neighbourhood.statistics.gov.uk/NDE2/Disco/GetDatasetFamilies?SubjectId=15&AreaId=276750

RETURNS: A list of dataset families

QUERY:
http://neighbourhood.statistics.gov.uk/NDE2/Disco/GetVariableFamilies?DSFamilyId=276
RETURNS: A list of variable families

DELIVERY queries

QUERY:
http://neighbourhood.statistics.gov.uk/NDE2/Deli/getTables?Areas=276750&Datasets=276&Variables=49
RETURNS: THE DATA.

The AJAX demonstration provided with NDE provides an example of how to
format XML returned from NDE into a table. Or you can use the
discovery method to create the lookup table he will use to build up
the URL with the correct parameters....

The three parameters that change are

b = areaId for the postcode - can be retrieved from the
SearchSByAByPostcode disco method
g = currentOAId - can be ommited
s = time stamp for the page - can be ommited


ONS Geography can provide Postcode directories to incorporate into any
lookup facility, see http://www.ons.gov.uk/about-statistics/geography/products/geog-products-postcode/index.html
although there may be a charge for this. In addition NeSS provides a
series of lookup tables to allow small output area level geographies
to be placed in the context of higher level geographic entities, such
as local authorities, please see
http://www.neighbourhood.statistics.gov.uk/dissemination/Info.do?page=aboutneighbourhood/geography/superoutputareas/soalookupfiles/soa-constitutions.htm

[Christopher]

:)))

I'm a very happy chappy.

Many thanks, Frank, you've given me all I need. That postcode to area
id query was the key - and the fact that I don't need to worry about
the g and s parameters.

So now I run the SearchSByAByPostcode query, drop the results in a
worksheet, pick out the correct code, replace that in my original
dataset web query and blammo, up pops my table. Perfect! and thanks to
you, literally 5 minutes work too :) For now I can hand select the
datasets I'm interested in, so just being able to specify the correect
area code will do fine for my demo.

I really appreciate your help, Frank, and I look forward to asking
slightly less naive questions sometime in the future, when I get to
have a proper play with the full NeSS system.


Christopher

On Oct 13, 9:49 am, Frank <better.i...@ons.gov.uk> wrote:
> Christopher, while we can give generic advice advice on using NDE,
> resource restrictions make it very difficult to provide specific
> solutions to individual reqirements or projects.
>
> Regarding your query, it looks like you would be best using discovery
> methods to find the area for a postcode, dataset family and variable
> ids, then a delivery method to find the data.
>
> A rough example would be:
>
> DISCO queries
>
>         QUERY:
>

>                http://neighbourhood.statistics.gov.uk/NDE2/Disco/SearchSByAByPostcod...

>
>         RETURNS:
>
>                 - <ans1:SearchSByAByPostcodeResponseElement xmlns:ans1="http://
> neighbourhood.statistics.gov.uk/nde/discoveryservice">
> - <ns0:AreaFallsWithins xmlns:ns0="http://
> neighbourhood.statistics.gov.uk/nde/v1-0/discoverystructs">
> - <ns0:AreaFallsWithin>
>   <ns0:FallsWithin xsi:nil="1" xmlns:xsi="http://www.w3.org/2001/
> XMLSchema-instance" />
> - <ns0:Area>
>   <ns0:LevelTypeId>13</ns0:LevelTypeId>
>   <ns0:HierarchyId>2</ns0:HierarchyId>
>   <ns0:AreaId>276750</ns0:AreaId>
>   <ns0:Name>Croydon</ns0:Name>
>   </ns0:Area>
>   </ns0:AreaFallsWithin>
>   </ns0:AreaFallsWithins>
>   </ans1:SearchSByAByPostcodeResponseElement>
>
>         QUERY:

>                http://neighbourhood.statistics.gov.uk/NDE2/Disco/GetDatasetFamilies?...

>
>         RETURNS: A list of dataset families
>
>         QUERY:

>                http://neighbourhood.statistics.gov.uk/NDE2/Disco/GetVariableFamilies...

>         RETURNS: A list of variable families
>
> DELIVERY queries
>
>         QUERY:

>                http://neighbourhood.statistics.gov.uk/NDE2/Deli/getTables?Areas=2767...

>         RETURNS: THE DATA.
>
> The AJAX demonstration provided with NDE provides an example of how to
> format XML returned from NDE into a table. Or you can use the
> discovery method to create the lookup table he will use to build up
> the URL with the correct parameters....
>
> The three parameters that change are
>
> b = areaId for the postcode - can be retrieved from the
> SearchSByAByPostcode disco method
> g = currentOAId - can be ommited
> s = time stamp for the page - can be ommited
>
> ONS Geography can provide Postcode directories to incorporate into any

> lookup facility, seehttp://www.ons.gov.uk/about-statistics/geography/products/geog-produc...

> although there may be a charge for this. In addition NeSS provides a
> series of lookup tables to allow small output area level geographies
> to be placed in the context of higher level geographic entities, such

> as local authorities, please seehttp://www.neighbourhood.statistics.gov.uk/dissemination/Info.do?page...