Blog
home > blog > Alternative a ImportXML e GoogleLookup per Excel

Tag: Excel, lab, scraping, ideativi

Alternative a ImportXML e GoogleLookup per Excel

ImportXML e GoogleLookup sono due funzioni per acquisire informazioni da web di Google Spreadsheet. Abbiamo sviluppato funzioni equivalenti per Excel. E qualcos'altro.
di Maurizio Ceravolo
ImportXML è una comoda funzione di Google Spreadsheet tramite la quale è possibile effettuare il parsing di file xml e pagine web per estrarre informazioni.
GoogleLookup è una altrettanto comoda funzione sempre di Google Spreadsheet tramite la quale è possibile interrogare Google per avere la risposta a semplici quesiti, come ad esempio: l'altezza di una montagna, la popolazione di una nazione o la formula chimica di un composto.

GoogleLookup function
GoogleLookup

Ci sono due problemi però che ne limitano l'utilizzo. ImportXML può essere usata solo 50 volte all'interno di un foglio. Mentre per GoogleLookup la limitazione è ancora più stringente. La funzione è stata deprecata e non è più possibile usarla.

A seguito di una interessante discussione avuta su Google+ ho percepito l'interesse verso queste funzioni ed ho pensato di provare a replicarle su Excel, cercando di superare le limitazioni appena descritte.

Nel fare questo ho sfruttato l'esperienza che abbiamo noi di ideativi nello scraping di informazioni ed un po' di ricordi di VBScript che mi sono rimasti. :-) Combinando con qualche ora di lavoro abbiamo creato importxml.xls, un file excel dove sono definite le nostre funzioni alternative pronte all'uso. Ed oltre a quelle ne abbiamo create alcune altre.

Importante per poterlo usare una volta scaricato, dovete acconsentire ad usare le macro. Per poter dare delle nuove funzioni, ovviamente siamo stati costretti a scrivere un po' di codice.

Attualmente sono disponibili le seguenti funzioni:
  • GetHTMLFromUrl(Url, Truncate)
  • GetHTMLAndApplyRegEx(Url, Regex, NumberOfOccurences, Separator)
  • GetHTMLAndApplyGroupRegEx(Url, Regex, NumberOfOccurences, Separator)
  • GoogleLookup(TextToQuery)
  • BingLookup(TextToQuery)
  • GoogleFinanceLookup(TextToQuery)
  • GetTitleFromUrl(Url)
  • GetMetaDescriptionFromUrl(Url)
  • GetBodyTextFromUrl(Url)
Vediamole in dettaglio.


GetHTMLFromUrl(Url, Truncate)

Questa funzione permette di scaricare il contenuto di una url all'interno di una cella.
Parametri:
  • Url: String. Una qualsiasi url di cui si vuole scaricare il contenuto. Es: http://www.ideativi.it
  • Truncate (opzionale): Boolean. Tronca il testo scaricato a 32.767 caratteri. Questo campo è opzionale ed ha come valore di default true.
La funzione va usata senza il parametro opzionale, quindi troncando il testo a 32.767. Questa è una limitazione imposta da Excel. Infatti, controllando la documentazione, possiamo vedere che questo valore è il massimo numero di caratteri memorizzabili in una cella. Quindi per superare la limitazione (e valorizzare a false il parametro Truncate) e poter manipolare pagine superiori a 32 KB, occorre usare la funzione esclusivamente all'interno di una funzione VBA.

Eccezioni. Nel caso ci sia un problema di rete o la URL non sia raggiungibile, la funzione restituisce #VALORE.


GetHTMLAndApplyRegEx(Url, Regex, NumberOfOccurencesSeparator)

Questa funzione permette di scaricare il contenuto di una url e di applicare l'espressione regolare all'html scaricato.
Parametri:
  • Url: String. Una qualsiasi url di cui si vuole scaricare il contenuto. Es: http://www.ideativi.it
  • Regex: String. Una qualsiasi espressione regolare. Es: http[^"]*
  • NumberOfOccurences (opzionale): integer. Il numero massimo di occorrenze che la regex può ritornare. Questo campo è opzionale ed ha come valore di default -1. -1 consente di non avere un numero massimo di risultati
  • Separator (opzionale): booleano. Specifica il separatore da applicare per separare i risultati della regex nel caso siano maggiori di uno. Questo campo è opzionale ed ha come valore di default vbCrLf. vbCrLf è l'equivalente di \r\n e corrisponde ad un a capo.
Questa funzione è, negli obiettivi, la nostra alternativa a ImportXML, malgrado sia formalmente molto diversa. La nostra usa come meccanismo di parsing le espressioni regolari, mentre la funzione di Google Spreadsheet usa XPath.
La differenza ha due motivazioni:
  • Excel ha una funzione nativa per l'applicazione di XPath, FILTRO.XML, che è disponibile dal solo Excel 2013 ed ha la limitazione di accettare solo XML valido e ben formato in input. Cosa che non è l'HTML della maggior parte delle pagine che si trova su web.
  • Troviamo più versatile l'uso delle espressioni regolari rispetto a XPath.
In ogni caso stiamo pensando ad una alternativa per usare anche XPath in una versione futura di questo progetto.

Eccezioni. Nel caso ci sia un problema di rete o la URL non sia raggiungibile, la funzione restituisce #VALORE.


GetHTMLAndApplyGroupRegEx(Url, Regex, NumberOfOccurencesSeparator)

Questa funzione permette di scaricare il contenuto di una url e di applicare un'espressione regolare contenente dei gruppi all'html scaricato.
Parametri:
  • Url: String. Una qualsiasi url di cui si vuole scaricare il contenuto. Es: http://www.ideativi.it
  • Regex: String. Una qualsiasi espressione regolare. Es: href=["']([^"^']*)["']
  • NumberOfOccurences (opzionale): integer. Il numero massimo di occorrenze che la regex può ritornare. Questo campo è opzionale ed ha come valore di default -1. -1 consente di non avere un numero massimo di risultati
  • Separator (opzionale): booleano. Specifica il separatore da applicare per separare i risultati della regex nel caso siano maggiori di uno. Questo campo è opzionale ed ha come valore di default vbCrLf. vbCrLf è l'equivalente di \r\n e corrisponde ad un a capo.
Questa funzione è simile alla precedente GetHTMLAndApplyRegEx e ne condivide scopi ed obiettivi. L'unica differenza è che, invece di estrarre tutta l'espressione regolare, estrae solo i gruppi in essa contenuti.

Eccezioni. Nel caso ci sia un problema di rete o la URL non sia raggiungibile, la funzione restituisce #VALORE.


GoogleLookup(TextToQuery)

Questa funzione permette di interrogare Google e prelevare il contenuto della One Box, se presente per la query.
Parametri:
  • TextToQuery: String. Un qualsiasi testo di cui Google presenta una One Box in risposta. Es: Italy Population
Google One Box
Google OneBox

Tutte le query, che generano un box di questo genere, vengono analizzate e il dato presente scaricato e restituito dalla funzione. Non ho un elenco completo di quello che si può chiedere a Google, però nel file che potete scaricare a fondo pagina potete trovare parecchi esempi funzionanti. Altri esempi li potete trovare nella pagina dove annunciava GoogleLookup, ora dismessa.

Eccezioni. Nel caso ci sia un problema di rete o Google non sia raggiungibile, la funzione restituisce #VALORE.


BingLookup(TextToQuery)

Questa funzione permette di interrogare Bing prelevare il contenuto della One Box, se presente per la query.
Parametri:
  • TextToQuery: String. Un qualsiasi testo di cui Bing presenta una One Box in risposta. Es: Italy Population

Bing OneBox
Bing OneBox


La funzione è analoga alla precedente GoogleLookup, con la differenza che usa come fornitore di informazioni Bing. Tutte le query, che generano un box come quello della foto, vengono analizzate e il dato presente scaricato e restituito dalla funzione. Anche in questo caso trovate degli esempi nel file excel che potete scaricare a fondo pagina. Per adesso sembrerebbe che Bing restituisca meno risultati di Google, però è plausibile che aumentino visto che il fornitore delle informazioni è Freebase.

Eccezioni. Nel caso ci sia un problema di rete o Bing non sia raggiungibile, la funzione restituisce #VALORE.


GoogleFinanceLookup(TextToQuery)

Questa funzione permette di interrogare Google Finance e prelevare il valore delle azioni dell'azienda cercata.
Parametri:
  • TextToQuery: String. Il nome di una qualsiasi azienda o del ticker di una azione. Es: Microsoft
La funzione si appoggia al sito di Google Finance, e restituisce il valore delle azioni di una azienda. Questa funzione è in parte ridondante in quanto è possibile avere le stesse informazioni tramite la GoogleLookup. Il problema della GoogleLookup è che non restituisce il valore di ogni azione, mentre se si usa il ticker dell'azione (es: BIT:F, per la Fiat) si ha un ventaglio più ampio di azioni da interrogare.

Eccezioni. Nel caso ci sia un problema di rete o Google Finance non sia raggiungibile, la funzione restituisce #VALORE.


GetTitleFromUrl(Url)

Questa funzione permette di interrogare una qualsiasi Url ed estrarre il tag Title se presente.
Parametri:
  • Url: String. Una qualsiasi url di cui si vuole scaricare il contenuto. Es: http://www.ideativi.it

La funzione è una semplice applicazione di GetHTMLAndApplyRegEx con una espressione regolare che estrae il contenuto del tag Title, se presente.

Eccezioni. Nel caso ci sia un problema di rete o la URL non sia raggiungibile, la funzione restituisce #VALORE.


GetMetaDescriptionFromUrl(Url)

Questa funzione permette di interrogare una qualsiasi Url ed estrarre il contenuto del META Description se presente.
Parametri:
  • Url: String. Una qualsiasi url di cui si vuole scaricare il contenuto. Es: http://www.ideativi.it

La funzione è una semplice applicazione di GetHTMLAndApplyRegEx con una espressione regolare che estrae il contenuto del META Description, se presente.

Eccezioni. Nel caso ci sia un problema di rete o la URL non sia raggiungibile, la funzione restituisce #VALORE.


GetBodyTextFromUrl(Url)

Questa funzione permette di interrogare una qualsiasi Url ed estrarre il testo presente all'interno del body.
Parametri:
  • Url: String. Una qualsiasi url di cui si vuole scaricare il contenuto. Es: http://www.ideativi.it

La funzione scarica l'HTML della URL passata per parametro e cerca all'interno del Body tutto il testo presente, eliminando qualsiasi tag presente.

Eccezioni. Nel caso ci sia un problema di rete o la URL non sia raggiungibile, la funzione restituisce #VALORE.


Limitazioni

Dopo tante belle notizie, dobbiamo però parlare delle limitazioni che sono presenti nell'uso di queste funzioni, che dipendono da limitazioni dello stesso Excel e da dove acquisiamo le informazioni.

Quasi tutte le funzioni che abbiamo creato partono con lo scaricamento di una pagina a partire da una Url. Il VBA, il linguaggio con cui si possono estendere le funzionalità di Excel, ha la caratteristica di essere single threaded. Il che significa che può fare una sola cosa per volta, e cosa peggiore, condivide lo stesso thread fra la programmazione in VBA e la gestione dell'interfaccia. Il che vuol dire che, mentre è in esecuzione, una delle funzioni custom l'interfaccia è bloccata.

Abbiamo cercato di minimizzare al massimo questo problema, però è comunque decisamente apprezzabile. Il che vuol dire che se lanciate alcune centinaia di funzioni contemporaneamente potreste avere l'interfaccia bloccata per alcuni minuti, e la velocità dell'elaborazione dipende anche dalla velocità dei siti che state scaricando. Il mio consiglio è una volta scaricate le informazioni che vi servono di copiarle su un differente file excel. Il motivo è che Excel potrebbe decidere in alcuni momenti mentre lavorate di rieseguire tutte le funzioni, e quando avviene quindi vi trovate il foglio bloccato. Molto meglio prendere i dati e copiarli su un foglio su cui lavorarli.

Un altro utile consiglio è di non applicare sul foglio più di 100 funzioni per volta, per due motivi:
  • vi trovate il foglio bloccato per poco
  • se state usando la GoogleLookup o la BingLookup, sappiate che queste non si appoggiano ad alcuna API, ma richiamano la stessa pagina che richiamereste voi dal browser. Il che vuol dire che se ci sono troppe chiamate, venite bloccati dal server e non scaricate più nulla. Google, quando ci sono troppe richieste, mostra un captcha per assicurarsi che arrivino da un operatore umano e non da un software.
Se volete sapere quanto vi potete spingere, io ho chiamato fino a 2000 funzioni in una volta, ha funzionato, però ho dovuto aspettare parecchi minuti prima di avere il controllo di nuovo del foglio excel. Comunque potete avere una idea dello stato del calcolo, perchè quando applicate parecchie funzioni pesanti, Excel fornisce, nella barra di stato, una informazione con lo stato di completamento.


Dalla barra di stato sappiamo che stiamo usando 8 processori, che il calcolo è al 2% ed abbiamo applicato 1850 funzioni

Abbiamo anche un'altra limitazione riguardo alle GoogleLookup e BingLookup. Dato che le informazioni che vengono restituite provengono unicamente dalle versioni americane di Google e Bing, le interrogazioni ed il loro risultati sono unicamente in inglese.

Queste funzioni, inoltre, funzionano solo su Excel per Windows. Non funzionano su Windows RT e su Mac Os.

Ringraziamenti

Un ringraziamento speciale va ad Enrico Altavilla (http://www.lowlevel.it/). Questo progetto è nato a partire da un suo post. Ed ha raggiunto questa forma grazie alle sue idee, i suoi consigli ed il tempo che ha dedicato al beta-testing.
Grazie Enrico. :-)


Download

All'interno del file che contiene le funzioni descritte in questo documento è presente un foglio con parecchi esempi di utilizzo.
Post correlati
2013
17
Settembre
ImportXML, GoogleLookup alternatives for Excel
Maurizio Ceravolo
ImportXML and GoogleLookup are usefull function in Google Spreadsheet to scrape information from internet. We developed an alternative for these and other functions in Excel.
2011
13
Gennaio
Eliminare la restrizione ip per gli ftp di Aruba
Maurizio Ceravolo
Da un mese circa sul principale hoster italiano sono state introdotte restrizioni sugli ip che accedono agli ftp dei domini ospitati. Ecco la guida per rimuoverle.
2011
3
Gennaio
I post più letti del 2010
Maurizio Ceravolo
Gli articoli più letti del blog di e-minds del 2010.
2011
3
Novembre
Microsoft Codename "Social Analytics"
Maurizio Ceravolo
I laboratori di SQL Azure, rilasciano un servizio cloud sperimentale per analizzare gli andamenti social su social network, blog e forum. Con le API per gli sviluppatori.
2011
23
Maggio
Diecimila (10.000) volte grazie!
Maurizio Ceravolo
Un ringraziamento di cuore a tutti i visitatori di questo sito che ci hanno consentito di raggiungere il traguardo delle 10.000 visite in un mese.


Archivio Mensile Blog
Tag Cloud
+1 A grande richiesta Apple Azure Facebook geek Geolocalizzazione Google Google+ humor infografica innovazione iPad Lego Microsoft RDP social network video videogiochi viral
Ultimi Post
I migliori di oggi
I più popolari del mese
Top post