Ceci est une ancienne révision du document !
Linking To The Web Spreadsheets are a great place to collect data. The web is a great resource for data and much of that data is dynamic. You may even want to collect this data into a spreadsheet and make the spreadsheet mirror the dynamic nature of the web data. Calc allows us to link to external data sources, including web pages, and it will surprise you how easy it is. Many people trade stocks. Trading requires diligently tracking the stocks. In the past, I have shown this done manually, but today, I am going to show you a way to use a Calc spreadsheet to track the top 100 stocks as compiled by the web site barchart.com. Like so many things, there is more than one way to do this. I'm going to show the method I feel is the easiest for discovering and getting exactly the information you want. You will import the web page using the Web Page Query filter. Once imported, you can examine the elements of the page to find the data you want to extract. You will then create your sheet of 100 stocks by pulling from your imported source file.
Se connecter au Web
Les feuilles de calcul sont un bon endroit pour collecter des données. Le web est une grande source de données et beaucoup de ces données sont dynamiques. Vous pouvez même envisager de collecter les données dans une feuille et lui faire refléter la nature dynamique des données du web. Calc vous permet les liaisons avec des sources de données externes, y compris des pages web, et avec une facilité qui vous surprendra.
Beaucoup de gens commercent en bourse. Ce commerce nécessite de suivre les valeurs assidûment. Dans le passé, j'avais montre ceci, fait à la main, mais maintenant, je vais vous montrer un manière d'utiliser une feuille Excel pour suivre les actions du top100 telles que regroupées par le site web barchart.com.
Comme tant de choses, il y a plus d'une façon de le faire. Je vais vous montrer la façon qui, à mon sens, est la plus facile pour découvrir et obtenir exactement l'information que vous voulez. Vous allez importer la page web en utilisant le filtre Web Page Query .Une fois importée, vous pouvez examiner les éléments de la page pour trouver les données que vous voulez extraire. Ensuite, vous créerez votre feuille des 100 actions par tirage depuis votre fichier source importé.
Setting Up the Source Sheet When you import the source using the Web Page Query filter, you are not actually working with a local copy, but a link to the page on the Internet. You will not save this import (but you could). Instead, you are using it as a reference to the actual page on the barchart.com web site. From the menus, File > Open. In the Open dialog, you will find the file type filter drop-down list at the bottom of the dialog. All the different file types recognized by LibreOffice are listed here. Scroll through and find the filter named “Web Page Query (Calc)”. This will help you create your link to the web page. In the “File name” text box, enter (or copy and paste) the following URL: http://www.barchart.com/stocks/signals/top100 Click the Open button. Calc will take a few seconds then come up with the Import Options dialog. The Automatic option will import the web page “as is.” Custom allows you to select another language for importing the page. I recommend using Automatic as it has the greatest chance for proper interpretation. The “Detect special numbers (such as dates)” checkbox does what you think it does. When checked, it will detect dates, times, etc, and format them appropriately in the sheet. We do not have special numbers we are concerned about, so we don't need it. Click OK to accept the options. Depending on your Internet connection, computer speed and memory, Calc will take a few seconds to import the web page. Notice that it imports the entire web page minus the images. NOTE: This is a link to the actual web page. Nothing has been saved on your local computer at this point.
Configurer la feuille source
Quand vous importez la source en utilisant le filtre Requête de page web,vous n'êtes pas vraiment en train de travailler sur une page locale mais sur un lien vers la page internet. Vous ne sauverez pas cette importation (mais vous le pourriez). A la place, nous allons l'utiliser comme référence vers la vraie page sur le site web de barchart.com.
Dans le menu, Fichier > Ouvrir. Dans le boîte de dialogue Ouvrir, vous trouverez en bas une liste déroulante par type de fichier. Tous les différents types de fichiers reconnus par Calc sont listés là. Parcourez la liste et trouvez le filtre appelé « Requête de page Web (Calc) ». Ceci vous aidera à créer le lien vers la page web. Dans le champ « Nom du fichier », entrez (ou copiez-collez) l'URL suivante : http://www.barchart.com/stocks/signals/top100.
Cliquez sur le bouton Ouvrir. Calc prendra quelques secondes avant d'ouvrir la boîte de dialogue des Options d'import. L'option Automatique importera la page web « telle quelle ». Personnalisé vous permet de sélectionner une autre langue pour l'importation. Je vous recommande d'utiliser Automatique qui vous offrira les meilleures chances d'une interprétation correcte.
The Navigator We will use the Navigator to examine the imported page and link the data to our new document. The Navigator shows you the elements and structure of a document and changes depending on the type of document you have open. Not only is it good for our purposes here, but for navigating and manipulating a document, especially a large document. Currently, there are four ways to open the Navigator: • Press F5 on your keyboard • View > Navigator • Navigator tab on the sidebar • Navigator button on the standard toolbar
Examine the Source When a web page is imported, several range-names are created, as well as importing named tables on the web page. The prefix HTML_ is added to any names imported from the page. The filter creates two special ranges, HTML_all and HTML_tables. HTML_all allows you to select the entire document. HTML_tables allows you to select all the tables. The problem you run up against is the creator of the web page probably wasn't thinking about you importing his page into Calc. Imported table names were for the creator's reference not yours. If you open the Navigator using one of the methods listed above, in our source document, you will discover a list of names under the “Range-names” item in the Navigator. Double-click on a range-name to highlight it in the document. As you go through the list, you soon discover that the names HTML_4 and HTML_dt1 both contain the table with our stock data. You will use one of these to create your stocks document.
Import Table to New Sheet Now that you know what range-name to import for the stocks data, it's time to create the sheet you want to save and keep. You will import from the source document into a new document. Remember that your source document is just a link to the actual web page, nothing has been saved to your local computer yet. From the menus, File > New > Spreadsheet. Use any of the four methods discussed above to open the Navigator. From the documents list at the bottom of the Navigator window/panel, select the source document, top100. Click on the drag-mode icon in the Navigator toolbar and change the setting to “Insert as link.” Expand the entries under the “Range names.” Select either HTML_4 or HTML_dt1, drag it to cell A1 in the new document, and release. After a few seconds, depending on your internet and computer speeds, the stocks data will appear in your new sheet. Save the new document. You can now close the source document. There is no need to save it. Your new document is actually linked to the page on the web site and not the source document. You can close your new document, and when you open it, you are prompted about whether to update the links in the file. If you answer Yes, Calc will retrieve a fresh version of the page and update the data in your sheet. You can set the file to update periodically when it is opened. In the menus, Edit > Links brings up the Edit Links dialog. With the link selected, click on the Modify button to bring up the External Data dialog. Check the “Update every…” check box, and fill in the number of seconds between every update. For example, every five minutes is 300 seconds, ten minutes is 600 seconds, and an hour is 3,600 seconds. Click OK to save your changes and Close to close the Edit Link dialog. Importing dynamic data from a web page in Calc is pretty simple. Use the Web Page Query filter to link the page to a sheet. With the Navigator, you can examine the page to determine which range-name contains the data you need. Once you know the range you need to use, you use the Navigator to drag the range-name into a new sheet and save the new document. The new document will prompt you to update every time you open the document or periodically, should you assign an update period to it.