-------------------------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.
