SharePoint lijstjes (en de werkboek weergave van lijstjes) bieden helaas niet zoveel mogelijkheden. Excel services (de online versie van Excel, in het kort gezegd) biedt meer mogelijkheden, maar daarvoor zijn technische instellingen nodig en het vergt inspanning van de infrastructuur.
Excel Web Queries in het kort
Gemakkelijker en laagdrempeliger is het gebruik van Excel Web Queries. Hiermee kan informatie vanaf websites worden downloaded (ook automatisch, volgens een bepaalde frequentie) in een Excel workbook en wordt deze informatie in Excel cellen beschikbaar gesteld. De informatie kan vervolgens, als gebruikelijk, worden verwerkt in formules, grafieken, et cetera.
Wat je hiermee nog meer kunt bereiken is onder anderen:
- Het maken van een management dashboard waarin informatie vanuit verschillende bronnen wordt weergegeven. Een gevuld management dashboard kan dan ook offline worden gebruikt.
- Vanuit Excel kan deze informatie natuurlijk ook weer worden gebruikt in andere, b.v. Office, applicaties.
Excel Web Queries maken
Web queries kunnen op verschillende manieren worden aangemaakt:
- In Excel: via het menu Data | From web
- Vanuit SharePoint lijsten en bibliotheken: via het lijst - of bibliotheek menu Actions | Export to Spreadsheet
- Vanuit .iqy bestanden (Connection bestanden)
Wanneer een export naar Excel vanuit een SharePoint lijst of bibliotheek wordt gemaakt wordt een .iqy bestand ter download aangeboden. Dit bestand kan zowel opgeslagen worden om later te openen of meteen geopend worden in Excel (dubbelklikken). Na openen van dit bestand (Excel) wordt de Web query uitgevoerd en wordt het huidige werkblad in het geopende spreadsheet gevuld met de gegevens die met de Web query zijn opgehaald. Een opgeslagen .iqy bestand kan direct in een specifiek werkblad worden gesleept zodat een Excel werkboek meerdere Web queries kan bevatten; per werkblad kunnen de gegevens vanuit een Web query worden opgehaald.
Bij het importeren van een Web query in een Excel werkblad kan worden gekozen voor:
- Weergave van de opgehaalde gegevens als tabel
- Weergave in een PivotTable rapport
- Weergave in een PivotChart en PivotTable rapport
Daarnaast kan gekozen worden voor de precieze lokatie waar de geimporteerde gegevens worden opgeslagen:
- In het reeds geopende werkblad, vanaf een specifieke lokatie (celnaam, b.v. $A$1)
- In een nieuw werkblad
- In een nieuw werkboek
Ook kan, onder anderen, worden aangegeven:
- Of de gegevens automatisch ververst worden bij opening van het Excel werkboek of na een bepaald aantal minuten.
- Of de opgehaalde gegevens in Excel werkboek bij het bewaren van het betreffende werkboek ook moeten worden opgeslagen of moeten worden verwijderd (en alleen de Query zelf in het werkboek wordt opgeslagen).
- Welk connection bestand gebrukt wordt om de gegevens op te halen.
Automatisch verversen van gegevens
Gegevens vanuit een Web query kan automatisch worden ververst door dit aan te geven (properties van de betreffende Web query in Excel). Let wel: het downloaden van verse data kan enige tijd in beslag nemen, dus te snel verversen van data is niet handig.
Voor verdere uitleg over data connections en data bronnen, zie Create, edit, and manage connections to external data. De beschikbare data sources staan in de map "\My Documents\My Data Sources".
Handmatig aanmaken van Web query (.iqy) bestanden
Web queries kunnen ook als tekstbestand (.iqy) in een teksteditor handmatig worden aangemaakt.
Een .iqy bestand de volgende opzet als in het onderstaande voorbeeld: MSN MoneyCentral Belangrijke indices voor investeerders (standaard meegeleverd met bijvoorbeeld Excel 2007):
WEB
1
http://moneycentral.msn.com/investor/external/excel/quotes.asp?symbol=$INDU,$COMP,$TRAN,$UTIL,$DAX,$FTSE,$HSI,$IIX.X,$COMPX,$NI225,$CAC,$SOX.X,$IUX,$OEX,$INX,$STI,$AOI,$CA:OSPTX
Selection=EntirePage
Formatting=All
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
Web queries op SharePoint lijsten en bibliotheken
Een .iqy bestand met een Web query naar een SharePoint lijst of bibliotheek heeft een invulling als in het onderstaande voorbeeld.
WEB
1
http://portal.demo.intra/NieuwsProject/_vti_bin/owssvr.dll?XMLDATA=1&List={4CE0FF47-3559-47E3-8158-5820814FCBA8}&View={E13E619E-37E7-4FA0-8C82-8C83D3B76BE8}&RowLimit=0&RootFolder=%2fNieuwsProject%2fPages
Selection={4CE0FF47-3559-47E3-8158-5820814FCBA8}-{E13E619E-37E7-4FA0-8C82-8C83D3B76BE8}
EditWebPage=
Formatting=None
PreFormattedTextToColumns=True
ConsecutiveDelimitersAsOne=True
SingleBlockTextImport=False
DisableDateRecognition=False
DisableRedirections=False
SharePointApplication=http://portal.demo.intra/NieuwsProject/_vti_bin
SharePointListView={E13E619E-37E7-4FA0-8C82-8C83D3B76BE8}
SharePointListName={4CE0FF47-3559-47E3-8158-5820814FCBA8}
RootFolder=/NieuwsProject/Pages
Hierin zijn onder anderen gedefinieerd:
- De unieke identifier (GUID, zie http://nl.wikipedia.org/wiki/Globally_Unique_Identifier) van de lijst of bibliotheek waaruit de gegevens worden opgehaald.
- De unieke identifier van de weergave van lijst of bibliotheek waaruit de gegevens worden opgehaald. Het is dus mogelijk om gegevens van aparte views op lijsten en bibliotheken op te halen.
Aandachtspunten
- Om gebruik te maken van (connecties naar) externe gegevens is het van belang dat dit toegestaan wordt vanaf de computer van de eindgebruiker. Zie hiervoor het "Trust Center" en de ingestelde "trusted locations".
Wordt vervolgd
In een komende update van deze blogpost zal ik verder ingaan op de mogelijkheden en wat praktische voorbeelden geven.
Verder lezen
- Use Excel's Web queries to easily view web data
- Get external data from a Web page: uitleg over alle mogelijkheden / parameters van Web queries.
- Integrate Far-Flung Data into Your Spreadsheets with the Help of Web Services
- Excel Web Query Secrets Revealed
- Hacking Excel Web Queries
Uitleg en voorbeelden van het gebruik van externe beleggings informatie in Excel, via Web queries:
- Stock quote and chart from Yahoo! in C#. Hierin wordt ook beschreven hoe Yahoo stockquotes kunnen worden gebruikt.
Free Stock Quotes in Excel.
