=Webservice & FilterXML() function vs Macro Excel

Pixiesurfer · por **Pixiesurfer** » 28/2/2017 11:54

=webservice têm algumas limitações de refresh, deu erro de excesso de acesso a Yahoo Finances, tive que cortar algumas colunas, focus no volume e valor e deixar indicadores fundamentais para um refresh semanal... Work in progress, passado 24h já funciona novamente

Pixiesurfer · por **Pixiesurfer** » 27/2/2017 10:18

ricardmag Escreveu:Não sei bem se é isto que procuras mas eu uso esta macro em VB...

Obrigado, assim que possível vou aplicar e testar o teu código, neste momento a função =Webservice directamente em excel está a funcionar, mas a performance podia ser melhorada, está demorar uns 5m actualizar 11.600 entrados, a versão anterior era imediata

intraday base update:
Symbol n v p o g h l1 t1 x e r d y s7 p6 r5 j1 j4 r1 p2
Symbol Name Volume Previous Close Open Low High Last Trade (Price Only) Last Trade Time Stock Exchange EPS PER Dividend Share Dividend Yield Short Racio Price to Book PEG Ratio Market Cap EBITDA Divident Pay Date Change in Percent

ricardmag · por **ricardmag** » 26/2/2017 22:01

Não sei bem se é isto que procuras mas eu uzo esta macro em VB

Código: Selecionar todos: Function YahooFinance(ticker As String, item As String) As Double Dim strURL As String, strCSV As Double, itemFound As Integer, tag As String itemFound = 0 If item = "ask" Then tag = "a" itemFound = 1 ElseIf item = "bid" Then tag = "b" itemFound = 1 ElseIf item = "bookvalue" Then tag = "b4" itemFound = 1 ElseIf item = "change" Then tag = "c1" itemFound = 1 ElseIf item = "afterhourschangerealtime" Then tag = "c8" itemFound = 1 ElseIf item = "tradedate" Then tag = "d2" itemFound = 1 ElseIf item = "epsestimatecurrentyear" Then tag = "e7" itemFound = 1 ElseIf item = "floatshares" Then tag = "f6" itemFound = 1 ElseIf item = "52weeklow" Then tag = "j" itemFound = 1 ElseIf item = "annualizedgain" Then tag = "g3" itemFound = 1 ElseIf item = "holdingsgainrealtime" Then tag = "g3" itemFound = 1 ElseIf item = "marketcapitalization" Then tag = "j1" itemFound = 1 ElseIf item = "percentchangefrom52weekhigh" Then tag = "k5" itemFound = 1 ElseIf item = "daysrangerealtime" Then tag = "m2" itemFound = 1 ElseIf item = "changefrom200daymovingaverage" Then tag = "m5" itemFound = 1 ElseIf item = "percentchangefrom50daymovingaverage" Then tag = "m8" itemFound = 1 ElseIf item = "open" Then tag = "o" itemFound = 1 ElseIf item = "changeinpercent" Then tag = "p2" itemFound = 1 ElseIf item = "exdividenddate" Then tag = " q" itemFound = 1 ElseIf item = "peratiorealtime" Then tag = "r2" itemFound = 1 ElseIf item = "priceepsestimatenextyear" Then tag = "r7" itemFound = 1 ElseIf item = "shortratio" Then tag = "s7" itemFound = 1 ElseIf item = "tickertrend" Then tag = "t7" itemFound = 1 ElseIf item = "holdingsvalue" Then tag = "v1" itemFound = 1 ElseIf item = "daysvaluechange" Then tag = "w1" itemFound = 1 ElseIf item = "dividendyield" Then tag = "y" itemFound = 1 ElseIf item = "averagedailyvolume" Then tag = "a2" itemFound = 1 ElseIf item = "askrealtime" Then tag = "b2" itemFound = 1 ElseIf item = "bidsize" Then tag = "b6" itemFound = 1 ElseIf item = "commision" Then tag = "c3" itemFound = 1 ElseIf item = "dividendshare" Then tag = "d" itemFound = 1 ElseIf item = "earningspershare" Then tag = "e" itemFound = 1 ElseIf item = "epsestimatenextyear" Then tag = "e8" itemFound = 1 ElseIf item = "days low" Then tag = "g" itemFound = 1 ElseIf item = "52weekhigh" Then tag = "k" itemFound = 1 ElseIf item = "holdsingain" Then tag = "g4" itemFound = 1 ElseIf item = "moreinfo" Then tag = "i" itemFound = 1 ElseIf item = "marketcaprealtime" Then tag = "j3" itemFound = 1 ElseIf item = "percentchangefrom52weeklow" Then tag = "j6" itemFound = 1 ElseIf item = "lasttradesize" Then tag = "k3" itemFound = 1 ElseIf item = "lasttradewithtime" Then tag = "l" itemFound = 1 ElseIf item = "lowlimit" Then tag = "l3" itemFound = 1 ElseIf item = "50movingaverage" Then tag = "m3" itemFound = 1 ElseIf item = "percentchangefrom200daymovingaverage" Then tag = "m6" itemFound = 1 ElseIf item = "name" Then tag = "n" itemFound = 1 ElseIf item = "previousclose" Then tag = "p" itemFound = 1 ElseIf item = "pricesales" Then tag = "p5" itemFound = 1 ElseIf item = "peratio" Then tag = "r" itemFound = 1 ElseIf item = "pegratio" Then tag = "r5" itemFound = 1 ElseIf item = "symbol" Then tag = "s" itemFound = 1 ElseIf item = "lasttradetime" Then tag = "t1" itemFound = 1 ElseIf item = "1yeartargetprice" Then tag = "t8" itemFound = 1 ElseIf item = "holdingsvaluerealtime" Then tag = "v7" itemFound = 1 ElseIf item = "daysvaluechangerealtime" Then tag = "w4" itemFound = 1 ElseIf item = "asksize" Then tag = "a5" itemFound = 1 ElseIf item = "bidrealtime" Then tag = "b3" itemFound = 1 ElseIf item = "change&percentchange" Then tag = "c" itemFound = 1 ElseIf item = "changerealtime" Then tag = "c6" itemFound = 1 ElseIf item = "lasttradedate" Then tag = "d1" itemFound = 1 ElseIf item = "errorindication" Then tag = "e1" itemFound = 1 ElseIf item = "epsestimatenextquarter" Then tag = "e9" itemFound = 1 ElseIf item = "dayshigh" Then tag = "h" itemFound = 1 ElseIf item = "holdingsgainpercent" Then tag = "g1" itemFound = 1 ElseIf item = "holdsingsgainpercentrealtime" Then tag = "g5" itemFound = 1 ElseIf item = "orderbookrealtime" Then tag = "i5" itemFound = 1 ElseIf item = "ebitda" Then tag = "j4" itemFound = 1 ElseIf item = "lasttraderealtimewithtime" Then tag = "k1" itemFound = 1 ElseIf item = "changefrom52weekhigh" Then tag = "k4" itemFound = 1 ElseIf item = "lasttradepriceonly" Then tag = "l1" itemFound = 1 ElseIf item = "daysrange" Then tag = "m" itemFound = 1 ElseIf item = "200daymovingaverage" Then tag = "m4" itemFound = 1 ElseIf item = "notes" Then tag = "n4" itemFound = 1 ElseIf item = "pricepaid" Then tag = "p1" itemFound = 1 ElseIf item = "pricebook" Then tag = "p6" itemFound = 1 ElseIf item = "dividendpaydate" Then tag = "r1" itemFound = 1 ElseIf item = "priceepsestimatecurrentyear" Then tag = "r6" itemFound = 1 ElseIf item = "sharesowned" Then tag = "s1" itemFound = 1 ElseIf item = "tradelinks" Then tag = "t6" itemFound = 1 ElseIf item = "volume" Then tag = "v" itemFound = 1 ElseIf item = "52weekrange" Then tag = "w" itemFound = 1 ElseIf item = "stockexchange" Then tag = "x" itemFound = 1 ElseIf item = "changepercentrealtime" Then tag = "k2" itemFound = 1 ElseIf item = "changefrom52weeklow" Then tag = "j5" itemFound = 1 End If If itemFound = 1 Then strURL = "http://download.finance.yahoo.com/d/quotes.csv?s=" & ticker & "&f=" & tag Set XMLHTTP = CreateObject("MSXML2.XMLHTTP") XMLHTTP.Open "GET", strURL, False XMLHTTP.send YahooFinance = CDbl(Strings.Replace(Strings.Replace(XMLHTTP.responseText, vbLf, ""), ".", ",")) Set XMLHTTP = Nothing Else YahooFinance = "Item Not Found" End If End Function

Para ler o valor atual da corticeira amorim

Código: Selecionar todos: Range("B11").Value = YahooFinance("COR.LS", "lasttradepriceonly")

Pixiesurfer · por **Pixiesurfer** » 26/2/2017 11:15

Boa tarde Caldeirões,

Nos ultimos dois anos tenho utilizado umas macros em excel que ligam a Yahoo Finances para obter historicos e valores ao minuto para cerca de 1000 acções/ETF/Fundos, com isso construi uma análise com ~250KPI´s, RSI MACD Fibo EMA MM ROC.... no entanto com um novo pc veio bloqueio/erro proxy, dns, devido a segurança interna.. e lá se foi o file update ao minuto, na procura de alternativas encontrei "webservice" / FilterXML() function

Alguêm utiliza? Que ferramentas/métodos utilizam para controlar em excel valores de acções ao minuto

exemplo webservice:
=WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s=" &$B9& "&f=" &C$7& "")
=NUMBERVALUE(WEBSERVICE("http://finance.yahoo.com/d/quotes.csv?s=" &$B9& "&f=" &D$7& "");".")

Code n v
Symbol Name Volume
ALTR.LS " ""ALTRI SGPS"" " 412.946
...

KPI´s list:
# Symbol Name Volume Previous Close Open Low High Last Trade (Price Only) Last Trade Time Stock Exchange EPS PER Dividend Share Dividend Yield Short Racio Price to Book PEG Ratio Market Cap EBITDA Divident Pay Date Change in Percent Follow 20% Notas Buy Price Sell Price D Pre-Market D vs Low D vs High D Max vs D-1 D Min vs D-1 D Max vs Min Volume to Value Type Sector Stock Type Bear 0 1 2 3 4 5 Bull Posição Posição Risco %Cresc [P] Tier Volume #60 Volume #5 vs 60/30/10 Volume & Gain #5 Volume & Gain D D+1(+) %Sucesso D+1 Linear #60 D+1 Linear vs D %Saturação #5 %Saturação #15 %Saturação #30 RSI D vs Média RSI D vs X% RSI D vs #5 Avr RSI Média RSI X% RSI #5 Avr RSI D RSI D-1 RSI D-2 RSI D-3 RSI D-4 RSI D-5 RSI D-6 RSI D-7 RSI D-8 RSI D-9 Rate of Change #15 Rate of Change #15 Rate of Change15 Média Rate of Change15 X% Rate of Change #30 Rate of Change #30 Rate of Change30 Média Rate of Change30 X% Perfect Trend #90 > #180 ? Gold II #60 > #180 ? Gold #20 > #60 ? Silver #5 > #30 Bronze #120 > #180 #90 > #120 #60 > #90 #30 > #60 #20 > #30 #5 > #20 Bull 5>20>30 Bull 20>30>60 D SIGNAL vs MACD D Turn MACD vs SIGNAL D Turn #20 > #60 ? Silver D Turn #5 > #30 Bronze D Turn #120 > #180 D Turn #90 > #120 D Turn #60 > #90 D Turn #30 > #60 D Turn #20 > #30 D Turn #5 > #20 D turn #120 D Turn #90 D Turn #60 D Turn #30 D Turn #20 D Turn #5 D SIGNAL vs MACD D Turn MACD vs SIGNAL MACD SIGNAL D D-1 D-2 D-3 D-4 D-5 D-6 D-7 D-8 D-9 #1 #2 #3 #4 #5 #10 #15 #30 #60 #90 #180 %Cresc P #1 #2 #3 #4 #5 #10 #15 #30 #60 #90 #180 Max vs Min Max vs Min D vs Max #5 D vs Max #15 D vs Max #30 D vs Max D vs Min #5 D vs Min #15 D vs Min #30 D vs Min #5 Min-Max #15 Min-Max #30 Min-Max D Max D Min D Max D Min %Var Day If(+) %Var Day If(-) %Var Day If(+) %Var Day If(-) D D-1 D-2 D-3 D-4 D-5 D-6 D-7 D-8 D-9 D-10 D-11 D-12 D-13 D-14 -1 -2 -3 -4 -5 Serie (-) 1-2 Serie (-) 1-3 Serie (-) 1-4 Serie (-) 1-5 Serie (-) 1-6 #180 (+) #90 (+) #30 (+) #15 (+) #10 (+) Sample Variance (%) Sample Variance (%) Desvio-Padrão Volatilidade = Coef.Var Volatilidade = Coef.Var Volatilidade 50s Volatilidade 50s Risco #5 Avg Volume #10 Avg Volume #30 Avg Volume #60 Avg Volume D vs #5 Volume Trend D vs #10 Volume Trend D vs #30 Volume Trend D vs #60 Volume Trend D vs #5 Volume Trend D vs #10 Volume Trend D vs #30 Volume Trend D vs #60 Volume Trend #5 vs #10 Volume #5 vs #30 Volume #5 vs #60 Volume Volume #5 vs 60/30/10 #5 vs #10 Volume #5 vs #30 Volume #5 vs #60 Volume D Volume & Gain #5D Volume & Gain #180 Média vs D #120 Média vs D #90 Média vs D #60 Média vs D #30 Média vs D #20 Média vs D #5 Média vs D EMA #12 vs #26 #180 Média vs D #120 Média vs D #90 Média vs D #60 Média vs D #30 Média vs D #20 Média vs D #5 Média vs D MM 5/20/30/60 D EMA #12 vs #26 #180 Média #120 Média #90 Média #60 Média #30 Média #20 Média #5 Média EMA #12 EMA #26 23,6% 38,2% 50,0% 61,8% Max #90 Max #60 Max #30 Max #15 Max #5 Max 0,95 0,9 75% 66% Moda 50% Media 33% 0,25 10% 5% #5 Min #15 Min #30 Min #60 Min #90 Min Min

Dividendo do BCP escala no “ranking” dos mais atrativos da banca europeia

Três cortes nas taxas em 24 horas mostram os desafios das tarifas para a Europa

Trump vai decidir "dentro de duas semanas" se os EUA atacam o Irão

=Webservice & FilterXML() function vs Macro Excel

Re: =Webservice & FilterXML() function vs Macro Excel

Re: =Webservice & FilterXML() function vs Macro Excel

Re: =Webservice & FilterXML() function vs Macro Excel