Monitor
November 2007

-------------------------Monitor------------------------
The Newsletter for PC-Based Data Acquisition and Control
Issue 112          www.windmill.co.uk      November 2007
--------------------ISSN 1472-0221----------------------

Welcome to November's issue of Monitor.  This month find 
out how to connect instruments with RS232 ports to an 
Ethernet network.  Plus, how to get live data from a web 
site into Excel.

I hope you enjoy the newsletter but should you wish to 
cancel your subscription you can do so at 
https://www.windmill.co.uk/newsletter.html

CONTENTS
========  
* Windmill Notes: 
  Connecting Serial Devices to Ethernet Networks
* Excel Corner: Getting Live Data from a Web Site
* DAQ News Roundup
________________________________________________________
________________________________________________________

Windmill Notes: 
Connecting Serial Devices to Ethernet Networks
________________________________________________________

We often talk about RS232 and RS485 serial instruments 
in Monitor. These are instruments which you can plug into 
the PC's COM port. Many older measurement devices fall 
into this category: balances, titrators, digital 
voltmeters and so on.  You may, however, prefer to 
connect these instruments to an Ethernet network.  
Perhaps to extend the connection over the longer 
distances that Ethernet provides.

To do this you will need a Serial to Ethernet adaptor, 
also known as a serial or device server.  The adaptor 
connects to the Ethernet network and provides one or 
more serial ports for your instruments.

The communication settings on the adaptor must match 
those of your instrument: baud rate, parity, etc.

When you buy an adaptor it generally comes with some 
Virtual COM port mapping software.  This lets your 
existing data acquisition software, like Windmill, 
work with your instruments as though they were 
connected to the PC's COM ports as normal.

The Virtual COM port software lets you transparently 
acquire data from your serial instrument across the 
Ethernet network using TCP/IP.  It presents the data 
to Windmill as if it came from a local serial connection.

How you configure your Virtual COM port software 
depends on the hardware and software that you have.  
As an example, here are the settings to use for the 
Lantronix Redirector program with Windmill software 
and Microlink hardware. 

Click the Start button in the Windows Taskbar, point 
to Programs, point to Lantronix Redirector and click 
Configuration. The Com Port Redirector Configuration 
window appears 

Com Setup button: Select a Virtual Com Port 
(The physical COM ports on the computer are shown 
in grey and cannot be chosen.)

Advanced button 
      Tick: Run as service 

Port Settings button 
      Tick: Raw Mode 
      Connection Timeout = 20 

Select the IP address of the adaptor (serial server) 
in the window - 

Web Configuration button 
      Channel 1 
      Serial Settings 
            Pack Control 
                  Tick: Enable Packing 
                  Select: Send Frame Immediate (Yes) 
            Click: OK 
      Click: Apply Settings

Reboot the computer in order to start the 
Virtual Com Port service.

When you have installed and configured your 
Virtual COM port software, you can use Windmill as 
normal without any changes to its settings.  Be aware, 
though, that the response times of your instrument 
may be slower.

For more information on the Windmill serial data 
acquisition software, see
https://www.windmill.co.uk/https://www.windmillsoft.com/daqshop/rs232-modbus.html
________________________________________________________
________________________________________________________

Excel Corner: Getting Live Data from a Web Site
________________________________________________________

You can easily import a table of data from a web page 
into Excel, and regularly update the table with 
live data. Read on or see How to feed live data from 
a web page into Excel

1. Open a worksheet in Excel then from the Data menu 
   select either Import External Data or 
   Get External Data.
2. Select New Web Query.
3. Enter the URL of the web page from which you want 
   to import the data and click Go.
4. Choose the table of data you wish to import, then 
   click the Import button.
5. Choose how often you want to refresh the data.  From 
   the Data menu select Import External Data again, 
   then choose Data Range Properties.

You can capture data which is held in a table or as 
preformatted text (inside the html <pre> tag). 

This method is also useful for capturing static data 
from a web site. The data will be reliably imported into 
correct columns and rows in Excel. If you merely copy and 
paste data from a web page to Excel, you sometimes find 
the entire row of data is in one cell. (Using the 
Paste Special command will often also achieve this, 
but not always.)

                                 *
Creating a Web Query File
=========================

For more flexibility in importing data use a 
web query file.  This is a text file with the 
extension .iqy. Its contents looks like this:

http://weather.msn.com/local.aspx?wealocations=wc:UKXX0092

Selection=2
Formatting=None

- The first line is the URL of the page holding the data. 
- The second line is blank.
- The third line sets which table of data to import, 
  in this case the second table on the page.
- The fourth line specifies that any formatting on the 
  web page should be ignored.

To use this text file, 
1. In Excel, from the Data menu select 
   Import External Data.
2. Select Import Data and choose your .iqy file.
3. Choose where to put the data and click OK.  The 
   live data appears in your spreadsheet.

Other selection options are available: 
Selection=AllTables, Selection=EntirePage and 
Selection="name of table".  To find the name of a table 
you will have to look at the source code of the page. 
For more than one table, separate the table names or 
numbers with commas.

Using a query file gives you great flexibility.  For 
instance, you can code the file so Excel will ask you 
which data items you want. 


Requesting Different Items of Data
==================================
As an example I'll use a Google search for 
"Windmill Software".

If you searched on Google for "Windmill Software", the 
URL of the search results might look like this 
http://www.google.co.uk/search?q=windmill+software 
If you used this as your Web Query URL, the search 
results for "Windmill Software" would be placed in 
your spreadsheet. 

However, if you use a line like this as your Web Query
http://www.google.com/search?q=["q","Enter the Search Term:"]
you will be asked for the search term Google is to use.

Of course, you won't often need to display search 
results in your spreadsheet, but it illustrates the 
method.  You can do this for any web site handling 
data in this way. That is, any website returning 
data with a ? in the URL followed by a name and 
value pair.

You can also nominate a cell in the spreadsheet to 
hold the query term. Whenever the contents of this 
cell change, the data will be updated in Excel. 

To do this: 
1. In Excel, from the Data menu select Import External Data.
2. Choose an .iqy file then click the Parameters button.
3. Choose to "Get the value from the following cell" and 
   select the cell.

Using a query file in this way provides an easy and 
quick way to get live data from the web into your 
spreadsheets. You can choose how often to refresh 
the data, or simply to keep it as it is.

For more on getting data from web sites with Excel see
http://support.microsoft.com/kb/157482
http://tinyurl.com/ypf6h5

For more on data acquisition and control using Excel see
https://www.windmill.co.uk/excel/
https://www.windmill.co.uk/excel/excel-charting.html
________________________________________________________
________________________________________________________

DAQ News Roundup
________________________________________________________

Welcome to our roundup of the data acquisition and 
control news.  If you would like to receive more 
timely DAQ news updates then grab our RSS newsfeed 
at https://www.windmillsoft.com/monitor.xml.  Read 
https://www.windmill.co.uk/newsfeed.php for notes
on how to display the news on your own web site, 
read it via e-mail, mobile phone or a newsfeed viewer.

So Many Flavours of Industrial Ethernet
   Growth in the use of Ethernet-based communication is 
   forecast to exceed that of networking protocols in 
   general; according to a new report on the world 
   industrial Ethernet from IMS Research.  Global 
   shipments of industrial Ethernet nodes are forecast 
   to grow at 21.7% over the next 5 years.
   Source: IMS Research
   https://technology.ihs.com/

Cellphones team up to become smart CCTV swarm
   Software that turns groups of ordinary camera 
   cellphones into a "smart" surveillance network has 
   been developed by Swiss researchers.  The team says 
   it will release the software for programmers and 
   users to experiment with. The software employs 
   Bluetooth, a short-range wireless technology 
   included in many modern phones, to automatically 
   share information and let the phones collectively 
   analyse events that they record.
   Source: NewScientist.com news service
   http://tinyurl.com/343uv2

Plug in to reduce power use
   Essex scientists are developing an intelligent 
   plug which can monitor electricity use.  The plug 
   will be used like a normal plug but inside will be 
   a power meter, a microcontroller and a wireless 
   transceiver which will send information back to a 
   central point. People will be able to see how much 
   power every single device uses.
   Source: University of Essex
   http://www.essex.ac.uk/

Mini magnetic sensor demonstrated
   A tiny sensor that can detect magnetic field changes 
   as small as 70 femtoteslas-equivalent to the brain 
   waves of a person daydreaming-has been demonstrated 
   at the National Institute of Standards and Technology 
   (NIST).  The sensor could be used in screening 
   for explosives.
   Source: NIST
   https://www.nist.gov/
________________________________________________________
________________________________________________________

* Copyright Windmill Software Ltd
* Reprinting permitted with this notice included
* For more articles see https://www.windmill.co.uk

We are happy for you to copy and distribute this 
newsletter, and use extracts from it on your own web site 
or publication, providing the above notice is 
included and a link back to our website is in place.

An archive of previous issues is at 
https://www.windmill.co.uk/newsletter.html
and an index of articles at 
https://www.windmill.co.uk/newsletter.html

Windmill Software Ltd, PO Box 58, North District Office,
Manchester, M8 8QR, UK
Telephone: +44 (0)161 834 6688
Facsimile: +44 (0)161 833 2190
E-mail: monitor@windmillsoft.com
https://www.windmill.co.uk/
https://www.windmillsoft.com/



Subscribing

To receive Monitor every month please fill in your e-mail address below. We will not pass your address to any third parties, nor send you any unsolicited e-mail.

Subscribe Monitor

You will receive an e-mail confirming your subscription, with details of how to download the free software. If you don't receive this then your spam filter may be blocking our message. Make sure it is set to accept messages from monitor@windmillsoft.com. If you have problems contact the Editor.

Previous Issue Next Issue