Conversione di valute in tempo reale tramite acquisizione dati via web

[androi...@gmail.com]

Ciao a tutti, spero di trovare qui una risposta al mio quesito.

Sto cercando di creare una mia applicazione con excel (2010) / LibreOffice 3.5.2 nella quale ho 2 fogli che prelevano dati da due fonti web.

I dati prelevati vengono disposti nei rispettivi fogli che si chiamano TRADING e EXCHANGE RATES.

I dati sono disposti in diverse colonne (Esempio : Piazza, Valore medio, Valore vendita, valore acquisto, etc...).

I valori acquisiti da entrambi i fogli si aggiornano ogni 2 minuti ed i valori cambiano di posizione, sono dinamici (nella cella A1 potrebbe essere riportato il nome di una piazza mercato piuttosto che un'altra ... non ho potere di fissare la loro posizione in quanto i dati provengono da una fonte esterna che non posso manovrare).

Ogni valore in "Piazza" è composto da un nome+sigla valuta (esempio : ForexUSD, BitFloorEUR, BitSEK, etc...)


Sempre nella stessa cartella di excel ho un altro foglio che preleva i valori di cambio della mastercard, valori basati sul cambio EURO contro le varie valute.

In sostanza ho una colonna dove viene riportata la sigla della valuta (es. JPY, USD, SEK, etc...) e nella colonna accanto il valore di cambio di quella valuta contro l'euro.

Io vorrei fare in modo che il primo foglio che importa i dati dal web relativo alle piazze ed ai valori di vendite e acquisto vada a controllare la sigla della rispettiva valuta di ogni cella della prima colonna e vada a leggere nell'altro foglio il corrispettivo valore di cambio contro l'euro e mi metta il valore in EURO in una nuova colonna della stessa tabella. Ciò mi permette di sapere su quale piazza posso acquistare o vendere al valore minore o maggiore basandomi sul cambio in euro. Nelle colonne EURO BID e EURO ASK vorrei il mio valore calcolato con la conversione valuta e vorrei che ogni volta che il foglio si aggiorna, vengano aggiornati i corrispettivi valori in EURO. Il foglio TRADING ha valori che cambiano ogni minuto, mentre i valori del foglio EXCHANGE RATES ha valori che credo cambino una volta al giorno.


Esempio del foglio TRADING

MARKET LAST VOLUME BID ASK EURO BID EURO ASK
nome1USD 10 10000 13,4 15,0
nome2JPY 10,70 45000 10,5 19,0
nome3SEK 88,00 67000 84,4 81,2



Esempio del foglio EXCHANGE RATES

USD US Dollar 1.3274
JPY Japanese Yen 118.18
SEK Swedish Korona 8.62


Non sono un grande esperto, qualcosa di VBA la so, credevo servisse un ARRAY nel quale mettere i valori dei cambi (ma excel stesso è già un array), credo serva una macro che ogni tot secondi vada ad eseguire i controlli partendo dal foglio EXCHANGE RATES , leggendo quanti valori ci sono e partendo dal prima va a leggere il simbolo valuta e rispettivo valore di cambio, poi deve passare al foglio TRADING e conteggiare quante righe ci sono, partire dalla prima e cercare se nel nome della piazza c'è per caso la sigla della valuta che ha letto dal foglio EXCHANGE ed in caso positivo deve spostare nella cella del valore calcolato ed applicare la formula di conversione (quella la so fare) , fatto quello deve passare a controllare gli altri valori e via dicendo per tutte le valute aggiornando il corrispettivo valore in EURO.

Questo è come credo che debba essere fatta la cosa, ma sono tutto orecchie sia su idee migliorative, sia per suggerimenti operativi (codice per eseguire le parti che occorrono).

Ringrazio fin d'ora.

PS; è una mia curiosità riuscire a fare questa cosa, non è per lavoro anche se mi faciliterebbe in alcune operazione che svolgo nel privato.

[r]

I due link alle fonti web?

[androi...@gmail.com]

Il giorno sabato 12 gennaio 2013 15:14:24 UTC+1, r ha scritto:
> I due link alle fonti web?

http://bitcoinwatch.com/ (HTML3)
http://www.ecb.int/stats/exchange/eurofxref/html/index.en.html (HTML1)

[r]

è abbastanza facile ... però ci sono dei problemi ... prima di tutto le tabelle non sono collegabili con query web direttamente ma bisogna tirare dentro nel foglio tutta la pagina ... in questi casi spesso la posizione dei dati può cambiare ... se adesso hai l'inizio della tabella alla riga 44 ... questa posizione potrebbe cambiare qualora la pagina web subisce una piccola modifica

secondo problema la tabella che importiamo nella prima connessione ha i delimitatori inglesi quindi la virgola come separatore delle migliaia e il punto come separatore decimale ... quindi devi invertire i separatori nelle opzioni di excel ->
opzioni di excel
impostazioni avanzate
opzioni di modifica e deflegga Utilizza separatori di sistema
poi imposta la virgola come separatore delle migliaia e il punto come decimale ...

vai nella proprietà delle connessioni e imposta ogni 2 minuti ...

alcune sigle tipo SLL non le trova nella seconda tabella ...

per quelle che usano EUR ho impostato il cambio a 1

questo è un primo file ... vediamo se è quello che avevi in mente:
http://goo.gl/1g2FP

saluti
r

[androi...@gmail.com]

Beh, innanzitutto complimenti per l'interesse ed impegno dimostrato. Grazie :-)
Ho visto il tuo foglio e sono meravigliato che tu non abbia usato delle macro.

Si, ho scoperto poi come fare con i separatori decimali e relativa impostazione :-)

Purtroppo non so come acquisire dati in formato JSON (http://bitcoincharts.com/t/markets.json ) e quindi sono costretto con l'importazione pagina web ... scoccia pure a me.

Ho trovato un sito migliore da cui prelevare i dati dei cambi di valuta, in quanto l'altro riportava informazioni difficili da gestire.
La fonte nuova è questa : http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml

Per quanto riguarda alcune valute come appunto SLL (valuta di Second Life) io le escludevo con un filtro sulla colonna.

Inoltre applicavo un filtro anche sulla colonna VOLUME24 evitando di vedere quelle con valore inferiore a 1 (sono piazze statiche o morte)

Anche nel foglio dei valori delle varie piazze mi occorrono solo alcune colonne (nomepiazza, last, volume24, bid, ask)

Non ho capito cos'hai fatto esattamente nel foglio results ... vedo delle formule strane ma non ho mai gestito così tanti comandi nidificati ... potresti spiegarmi?

Io il foglio results che hai fatto lo interpreto come un foglio in cui tu hai fissato le varie piazze nella stessa posizione ed acquisisci i dati dal primo foglio che si aggiorna in tempo reale, applicando poi la conversione valuta su quelle che non sono euro.

Correggimi se sbaglio.

Si potrebbe fare che al posto del valore 1 nel caso di euro mi metta il suo valore euro già riportato nel foglio.

Questo perchè poi vorrei applicare una formattazione condizionale alle due colonne BID EURO e ASK EURO e farmi visualizzare i valori min e max per entrambe.

Prova a vedere, se hai tempo e voglia, le modifiche fatte al tuo foglio ... http://goo.gl/eWWni

Ti ringrazio infinitamente per il tuo tempo e disponibilità.

[androi...@gmail.com]

Stavo analizzando un po' meglio le tue formule :

=SE(DESTRA(A2;3)="EUR";1; significa imposta 1 se gli ultimi 3 caratteri a destra nella cella A2 sono uguali a EUR

--SOSTITUISCI(SOSTITUISCI(SOSTITUISCI(CERCA.VERT(DESTRA(A2;3);Foglio2!$A$31:$C$100;3;0);"down";0);"up";0);"eq";0))

significa eliminare dal range a31-c100 le scritte down up eq .... ma dove?

Non capisco come fai ad applicare il tasso di cambio prelevandone il valore da foglio2


Mi sento molto ignorante in materia ...ma sono solo un tecnico di laboratorio

[r]

Il giorno lunedì 14 gennaio 2013 00:31:47 UTC+1, androi...@gmail.com ha scritto:
=SE(DESTRA(A2;3)="EUR";1;--SOSTITUISCI(SOSTITUISCI(SOSTITUISCI(CERCA.VERT(DESTRA(A2;3);Foglio2!$A$31:$C$100;3;0);"down";0);"up";0);"eq";0))

la formula è così traducibile:
se i primi 3 caratteri di destra del valore in cella a2 sono ="EUR" allora 1 se no cerca esattamente quei tre caratteri nella prima colonna di questo intervallo Foglio2!$A$31:$C$100 e restituiscine il valore della terza colonna ... a tale valore vai a fare le seguenti sostituzioni:
sostituisci
down con 0
up con 0
eq con 0

fatte le sostituzioni converti la stringa di testo in numero (il doppio meno serve a questo)

guarda la guida in linea a proposito di cerca.vert

saluti
r

[r]

Il giorno lunedì 14 gennaio 2013 00:13:27 UTC+1, androi...@gmail.com ha scritto:
Purtroppo non so come acquisire dati in formato JSON (http://bitcoincharts.com/t/markets.json ) e quindi sono costretto con l'importazione pagina web

Bruce sta facendo un grandissimo lavoro:
http://ramblings.mcpher.com/Home/excelquirks/json/excel-json-conversion

in ogni caso creare qualcosa così al volo non sarebbe tanto difficile ...
ad esempio questa funzione utilizza Internet Exlporer per recuperare il testo:
Function TextFromURL(myURL As String)
Dim myIE As Object
Const READYSTATE_COMPLETE As Long = 4
Set myIE = CreateObject("InternetExplorer.Application")
'myIE.Visible = True
myIE.navigate myURL
Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE
DoEvents
Loop
TextFromURL = myIE.document.body.innerHTML
myIE.Quit
Set myIE = Nothing
End Function

dovrei studiare meglio il formato json ... ma così ad occhio mi sembra facile da interpretare ... secondo me con una regexp mi servono poche righe di codice per farlo ... adesso ci provo :-)

intanto tu guardati le classi scritte da Bruce eh :-)

per quanto riguarda l'altra scelta quella del file xml ... beh puoi creare una connessione l'unico problema mi sembra l'aggiornamento ogni 2 minuti che in questo caso devi delegare ad una macro (credo eh ...), certo i dati sono molto più facili da usare :-)

ciao
r

[r]

divertente ... vediamo se funziona :-)

Sub test()
Write_JSON_on_Range TextFromURL( _
"http://bitcoincharts.com/t/markets.json"), _
ActiveWorkbook.Worksheets.Add.[a1]
End Sub

Function TextFromURL(myURL As String)
Dim myIE As Object
Const READYSTATE_COMPLETE As Long = 4
Set myIE = CreateObject("InternetExplorer.Application")
'myIE.Visible = True
myIE.navigate myURL
Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE
DoEvents
Loop
TextFromURL = myIE.document.body.innerHTML
myIE.Quit
Set myIE = Nothing
End Function

Sub Write_JSON_on_Range(sJSON As String, rng As Excel.Range)
Dim M As Object, RE As Object, SM As Object
Dim bFirstRow As Boolean, r As Long
Set RE = CreateObject("vbscript.regexp")
RE.ignorecase = True
RE.Global = True

bFirstRow = True

RE.Pattern = "\{[^}]+}"
If RE.test(sJSON) Then
Set M = RE.Execute(sJSON)
For Each SM In M
If bFirstRow Then
Write_JSON_Row SM.Value, rng.Offset(r), bFirstRow
bFirstRow = False
r = r + 1
Else
Write_JSON_Row SM.Value, rng.Offset(r), bFirstRow
End If
r = r + 1
Next
End If
End Sub

Sub Write_JSON_Row(sJSON As String, rng As Excel.Range, bFirstRow As Boolean)
Dim M As Object, RE As Object, SM As Object, SB As Object
Dim c As Long

Set RE = CreateObject("vbscript.regexp")
RE.ignorecase = True
RE.Global = True

RE.Pattern = """([^""]*)"":\s(null|true|false|\d+(?:\.\d+)?|""[^""]*"")"
If RE.test(sJSON) Then
Set M = RE.Execute(sJSON)
For Each SM In M
Set SB = SM.SubMatches
If bFirstRow Then
rng.Offset(0, c) = SB(0)
rng.Offset(1, c) = Conv_JSON_Value(SB(1))
Else
rng.Offset(0, c) = Conv_JSON_Value(SB(1))
End If
c = c + 1
Next
End If

End Sub

Function Conv_JSON_Value(sValue As String)
Dim M As Object, RE As Object, SM As Object, SB As Object
Dim c As Long

Set RE = CreateObject("vbscript.regexp")
RE.ignorecase = True
RE.Global = True

RE.Pattern = "(null)|""([^""]*)"""
If RE.test(sValue) Then
Set M = RE.Execute(sValue)
Set SB = M(0).SubMatches
If Len(SB(0)) Then

Else
Conv_JSON_Value = SB(1)
End If
Else
Conv_JSON_Value = sValue
End If

End Function




saluti
r

[r]

meglio ... non avevo considerato la possibilità di aggiungere " all'interno delle stringhe con il carattere di escape ... ecco il nuovo codice:

RE.Pattern = """((?:\\""|[^""])*)"":\s(null|true|false|\d+(?:\.\d+)?|""(?:\\""|[^""])*"")"

If RE.test(sJSON) Then
Set M = RE.Execute(sJSON)
For Each SM In M
Set SB = SM.SubMatches
If bFirstRow Then

rng.Offset(0, c) = Replace(SB(0), "\""", """")
rng.Offset(1, c) = Replace(Conv_JSON_Value(SB(1)), "\""", """")
Else
rng.Offset(0, c) = Replace(Conv_JSON_Value(SB(1)), "\""", """")

End If
c = c + 1
Next
End If

End Sub

Function Conv_JSON_Value(sValue As String)
Dim M As Object, RE As Object, SM As Object, SB As Object
Dim c As Long

Set RE = CreateObject("vbscript.regexp")
RE.ignorecase = True
RE.Global = True

RE.Pattern = "(null)|""((?:\\""|[^""])*)"""

If RE.test(sValue) Then
Set M = RE.Execute(sValue)
Set SB = M(0).SubMatches
If Len(SB(0)) Then

Else
Conv_JSON_Value = SB(1)
End If
Else
Conv_JSON_Value = sValue
End If

End Function

.... ovviamente tutto da migliorare
saluti
r

[androi...@gmail.com]

r ... sei un mostro (nel buon senso!) :-)


devo ancora provare il tuo metodo json e quello si sarebbe il passo verso la perfezione!

Senti, grazie per avermi insegnato come usare il se e gli altri comandi che hai usato e che io totalmente ignoravo.

Grazie a te , ho perfezionato il foglio e sono riuscito ad ottenere i risultati che volevo :-))

Vorrei capire meglio il discorso Json. In realtà il giorno prima di postare qui avevo trovato quel link che mi ha dato ed avevo scaricato le classi ed il file ma non ci avevo capito nulla, non sapevo che usarlo.

Ora che ho tutto , non ho capito l'ultimo tuo perfezionamento, va in sostituzione di quale parte di codice? O meglio ancora, se vuoi ripostare il codice definitivo ti sarei grato.

Ma dove le hai imparate tutte queste cose? Sei programmatore?

Beh, che dire, grazie grazie grazie grazie,...infinitamente grazie.

[r]

Il giorno sabato 12 gennaio 2013 04:34:25 UTC+1, androi...@gmail.com ha scritto:

> r ... sei un mostro (nel buon senso!) :-)

di certo non "del" buon senso :-D

> devo ancora provare il tuo metodo json e quello si sarebbe il passo verso la
> perfezione!

intanto ho trovato le specifiche anche in italiano e scritte bene! quasi un miracolo in questo campo :-)

http://json.org/json-it.html

poi ieri ho condiviso le routine con il mio amico ungherese Gabor che ha trovato molto interessante l'argomento (e non ne dubitavo :-) e ovviamente ci ha messo del suo :-) così ad esempio io non avevo considerato che i numeri potessero essere anche negativi o in formato scientifico ... poi ha giustamente fatto notare è inutile creare l'oggetto regexp tutte quelle volte ... meglio farlo una volta all'inizio e basta, così si risparmia un bel po' di tempo ... comunque la faccio breve ed ecco il nuovo codice ... manca ancora la gestione dei caratteri unicode ... appena riesco ...

ciao
r



Option Explicit
'http://json.org/json-it.html
Private RE As Object
Sub testRoberto0()
Dim sJSON As String

sJSON = TextFromURL("http://bitcoincharts.com/t/markets.json")

If Left(sJSON, 2) <> "[{" Then
Debug.Print "*** No data found"
Debug.Print sJSON
End
End If

Write_JSON_on_Range sJSON, ActiveWorkbook.Worksheets.Add.[a1]

End Sub

Public Function TextFromURL(myURL As String)

Dim myIE As Object
Const READYSTATE_COMPLETE As Long = 4
Set myIE = CreateObject("InternetExplorer.Application")
'myIE.Visible = True
myIE.navigate myURL
Do While myIE.Busy Or myIE.readyState <> READYSTATE_COMPLETE
DoEvents
Loop
TextFromURL = myIE.document.body.innerHTML
myIE.Quit
Set myIE = Nothing
End Function

Private Sub Write_JSON_on_Range(sJSON As String, rng As Excel.Range)
Dim M As Object, SM As Object ', RE As Object

Dim bFirstRow As Boolean, r As Long

If RE Is Nothing Then

Set RE = CreateObject("vbscript.regexp")

End If

RE.ignorecase = True
RE.Global = True
bFirstRow = True
RE.Pattern = "\{[^}]+}"
If RE.test(sJSON) Then
Set M = RE.Execute(sJSON)
For Each SM In M
If bFirstRow Then
Write_JSON_Row SM.Value, rng.Offset(r), bFirstRow
bFirstRow = False
r = r + 1
Else
Write_JSON_Row SM.Value, rng.Offset(r), bFirstRow
End If
r = r + 1
Next
End If
End Sub

Private Sub Write_JSON_Row(sJSON As String, rng As Excel.Range, bFirstRow As Boolean)
Dim M As Object, SM As Object, SB As Object ', RE As Object

Dim c As Long
'Set RE = CreateObject("vbscript.regexp")
'RE.ignorecase = True
'RE.Global = True

RE.Pattern = "\s*""((?:\\""|[^""])*)""\s*:\s*(null|true|false|[+-]?\d+(?:\.\d+)?(?:[eE][+-]?\d+)?|""(?:\\""|[^""])*"")\s*"

If RE.test(sJSON) Then
Set M = RE.Execute(sJSON)
For Each SM In M
Set SB = SM.SubMatches
If bFirstRow Then

rng.Offset(0, c) = Conv_JSON_String_Value(SB(0))
rng.Offset(1, c) = Conv_JSON_String_Value(Conv_JSON_Value(SB(1)))
Else
rng.Offset(0, c) = Conv_JSON_String_Value(Conv_JSON_Value(SB(1)))

End If
c = c + 1
Next
End If
End Sub

Private Function Conv_JSON_Value(sValue As String)
Dim M As Object, SM As Object, SB As Object ', RE As Object

Dim c As Long
'Set RE = CreateObject("vbscript.regexp")
'RE.ignorecase = True
'RE.Global = True
RE.Pattern = "(null)|""((?:\\""|[^""])*)"""
If RE.test(sValue) Then
Set M = RE.Execute(sValue)
Set SB = M(0).SubMatches
If Len(SB(0)) Then

'-> Conv_JSON_Value = Empty

Else
Conv_JSON_Value = SB(1)
End If
Else
Conv_JSON_Value = sValue
End If
End Function

' string = quotation-mark *char quotation-mark
'
' char = unescaped /
' escape (
' %x22 / ; " quotation mark U+0022
' %x5C / ; \ reverse solidus U+005C
' %x2F / ; / solidus U+002F
' %x62 / ; b backspace U+0008
' %x66 / ; f form feed U+000C
' %x6E / ; n line feed U+000A
' %x72 / ; r carriage return U+000D
' %x74 / ; t tab U+0009
' %x75 4HEXDIG ) ; uXXXX U+XXXX
'
' escape = %x5C ; \
'
' quotation-mark = %x22 ; "
'
' unescaped = %x20-21 / %x23-5B / %x5D-10FFFF



Private Function Conv_JSON_String_Value(sValue As String)
Dim c As Long

c = InStr(sValue, "\")
If c > 0 Then
sValue = Replace(sValue, "\""", """")
sValue = Replace(sValue, "\\", "\")
sValue = Replace(sValue, "\/", "/")
sValue = Replace(sValue, "\b", Chr(8))
sValue = Replace(sValue, "\f", Chr(12))
sValue = Replace(sValue, "\n", Chr(10))
sValue = Replace(sValue, "\r", Chr(13))
sValue = Replace(sValue, "\t", Chr(9))

'TODO: RegExp for Unicode
'sValue = Replace(sValue, "\u????", Chr(????))
Else
Conv_JSON_String_Value = sValue
End If
End Function

[androi...@gmail.com]

Beh, che lavoraccio che vi siete fatti ... così tanto per divertirvi :-)

Ho provato quest'ultima versione e devo dire che mi sembra funzioni correttamente.

E' molto più completo (almeno mi pare) rispetto ai risultati prelevati via web con l'importazione della pagina web.

Non ho capito quando dicevi che i numeri potrebbero essere negativi :-|

E non ho capito quando dici dei numeri in notazione scientifica (non è forse da allargare solo la colonna per vederli correttamente?)

Cmq voi siete avanti anni luce rispetto a me, la mia programmazione (non essendo il mio quotidiano) non è certo ai vostri livelli e certe cose fatico un po' a capirle e dovrei investire più tempo.

Avevo già visto la struttura del formato JSON ma poi mi sono imbattuto in altri sul web che parlavano di JSON ed avevano la struttura leggermente diversa (cosa scoraggiante in quanto non era quindi standard a mio avviso).

Una domanda, come mai aggiungi un foglio tutte le volte invece di sostituirlo o aggiornarlo? c'è un motivo?

Non so se ne capite qualcosa di BTC, ma forse anche si! Ho notato che c'è per esempio una piazza chiamata RUXUMZAR (sud africana - la mia terra d'origine) ... questa piazza è inesistente in quanto non la trovo nemmeno in internet ed infatti il suo volume d'affari è ZERO pure avendo dei valori nel BID e ASK (valori storici probabiulmente delle uniche transazioni avvenute).

Mi domandavo quindi come fare per scremare tutti quei risultati ed evitare di inserire nel foglio quelle che hanno con valore di volume ZERO?

Probabilmente dovrei modificare la routine ed inserire una condizione ! O forse si fa prima con il filtro sulle colonne escludendo i campi con valore zero.

Peccato che quando arrivo a casa dal lavoro sono così stanco mentalmente che dopo cena sono preda del sonno che mi annebbia la mente ed impedisce di progredire nella conoscenza (faccio piccoli passi purtroppo).

Ringrazio te ed il tuo amico Gabor (spero di aver scritto giusto).