vba - html file download from web with URLDownloadToFile creates empty file -


i have problem has been discussed in forum in past, although solutions specific cases had been proposed, none has been useful me. want analyze data table containing recent stock quotes. precisely yahoo portfolio. url "https://finance.yahoo.com/portfolio/pf_5/view/view_0". if try import portfolio worksheet via web connection, there no content seen in importation window. work fine until time ago, seems yahoo has changed code, content cannot imported anymore. cannot use website in excel connection import portfolio anymore.

but can download file chrome without entering credentials (they stored in chrome or in cookie, don't know) download folder html-file , when open in browser not show original can analyze downloaded file excel. file length of file downloaded directly browser 256 kb. seems server recognizes how file being used , allows storing it, not analyzing online.

now trying write vba sub opens website, downloads file , analyzes stored version. analysis part works fine, not able include working download in code. when use urldownloadtofile(0, url1, url2, 0, 0) method (url1 https address , url2 file name , path), downloaded file has 75kb , contains java code, there no data seen on screen when watch browser , when try import content excel, nothing imported. while urldownloadtofile may work in cases, not work yahoo portfolio web pages. question is: 1) changing parameters of function (parameter 1 = pcaller?). how? 2) there other known method available in vba save web page without reading line line (tried this, , doesn't work either) ? these 2 methods tried:

  option explicit  'declarations private declare function urldownloadtofile lib "urlmon" _ alias "urldownloadtofilea" (byval pcaller long, _ byval szurl string, byval szfilename string, _ byval dwreserved long, byval lpfncb long) long   'download code sub download()  dim done dim url1 string dim url2 string  url1 = "https://finance.yahoo.com/portfolio/pf_5/view/v1" url2 = "c:\users\xxx\downloads\pf1 - yahoo finance portfolios.html"   'this provide return value test.  'note  (   )  around args done = urldownloadtofile(0, url1, url2, 0, 0)   'test. if done = 0     msgbox "file has been downloaded!" else     msgbox "file not found!" end if  end sub 
sub savewebfile()   'this creates "empty" file! dim url1 string dim url2 string  url1 = "https://finance.yahoo.com/portfolio/pf_5/view/v1" url2 = "c:\users\xxxx\downloads\pf1 - yahoo finance portfolios.html"  set fso = createobject("scripting.filesystemobject")    createobject("msxml2.xmlhttp")    .open "get", url1, false    .send    text = .responsetext    end  set objoutputfile = fso.createtextfile(url2, true)  objoutputfile.write text  objoutputfile.close  end sub

while waiting answer continued search other solutions , found 1 works in case. not answer had been looking resolves problem. instead of using yahoo portfolio page using yahoo finance api (see [alternative google finance api (closed)). url

http://finance.yahoo.com/d/quotes.csv?s=symbol1[+symbol2+symbol3...]&f=format_code

creates downloadable comma delimited text file (.csv) can stored or evaluated directly in vba. [symbol1 ...] ticker symbols of stocks want analyze , {format code} series of letters describe type of data want see (full list in http://www.jarloo.com/yahoo_finance/)

as need stock symbol , last price without time, format code "sl1". there catch, though, or two. first (imposed yahoo) maximum number of symbols allowed 200 , ip might blocked if make many calls in short period of time. real time streaming data can not obtained way, although format list includes code real time data.

the second given querytables.add method use in code below, limits url 255 characters. if url string longer, runtime error occur. means mean second limitation occur before 200 symbols reached.

the following code resolves problem loop structure creating many calls necessary fetch data symbols, each call uses url less 256 characters long. in test use workbook test.xlsm 2 worksheets test , pf1. pf1 contains list of symbols fetch in column starting @ row 3. first row in sheet "test" has starting row of these data in d1 (=3) , row of last symbol in e1.

my sub has outer loop repeats inner loop necessary fetch symbols.

the inner loop creates url1 call, adding many symbols possible base part of url, condition must stay below 256 characters.once url complete, actual pointer symbol list saved "first" , data fetched. new url calculated next batch of data list.

after having fetched data, row height , column length in result sheet reset, because noticed changed during operation (don't know why).

i have noticed of price values, come in decimal format (with decimal "dot") may lose dot during query. not sure if due number format (european, "comma") or problem query itself. ideally number format should not have influence downloaded data should text. anyway makes necessary correct eventual outliers using list of approximate price values symbols. correction not included in sub.

sub import_csv_file_from_url()  dim url1 string dim url string dim ws worksheet dim first long dim last long dim long dim urllen long dim nxtlen long dim destcell range dim qt querytable   set ws = activesheet  url = "http://finance.yahoo.com/d/quotes.csv?s=" first = ws.range("d1") last = ws.range("e1") = first  while < last                           'loop through symbols      ws.range("a" & first & ":z1000").clear  'clear cells otherwise query inserts new columns.     set destcell = worksheets("test").range("a" & first)      url1 = url     = first last         if > first             url1 = url1 & "+"         end if         url1 = url1 & worksheets("pf1").range("a" & i)                      'add 200 symbols         if len(url1) > 249 - len(worksheets("pf1").range("a" & + 1)) 'len(url1) cannot >255!!             first = + 1       'save index next batch of symbols             exit         end if     next      url1 = url1 & "&f=sl1"         'format "sl1": symbol & last trade these tickers      destcell.parent.querytables.add(connection:="text;" & url1, destination:=destcell)         .textfilestartrow = 1         .textfileparsetype = xldelimited         .textfilecommadelimiter = true         .preserveformatting = true         .refresh backgroundquery:=false     end     each qt in activesheet.querytables         if qt.refreshing qt.cancelrefresh         qt.delete                                       'delete internal query tables     next      loop        'add next batch of symbols  ws.range("a:b").columnwidth = 8 = 3 last     ws.rows(i).rowheight = 15 next end sub

Comments

Post a Comment

Popular posts from this blog

c# - Update a combobox from a presenter (MVP) -

i using mvp in project, new in mvp. have 2 comboboxes. when select option in combobox, combobox should filled new data. this action in presenter. view 'view1' in presenter, , introduced combobox1 , combobox2 properties in 'view1', because need 'datasource', 'displaymember', 'valuemember' , 'refresh()' in method below. but, when using pattern, enough send property like public string combobox2 { { return combobox1.selectedvalue.tosstring(); } } into presenter not whole combobox. how can solve problem? public void onselectedindexchangedcombobox1() { if (view1.combobox1.selectedindex == -1) { return; } datatable dt = tools.getdatatable("a path"); var query = (from o in dt.asenumerable() o.field<string>("afield") == farmerview.combobox1.selectedvalue.tostring() orderby o.field<string>("anotherfield") select...
Read more

How to understand 2 main() functions after using uftrace to profile the C++ program? -

i trying uftrace profile following simple c++ program: #include <iostream> class { public: a() {std::cout << "a created" << std::endl;} ~a() {std::cout << "a destroyed" << std::endl;} }; int main() { a; return 0; } the profile result this: # uftrace a.out created destroyed # duration tid function 2.026 [ 4828] | __cxa_atexit(); [ 4828] | main() { [ 4828] | __static_initialization_and_destruction_0() { 89.397 [ 4828] | std::ios_base::init::init(); 0.768 [ 4828] | __cxa_atexit(); 93.029 [ 4828] | } /* __static_initialization_and_destruction_0 */ 94.425 [ 4828] | } /* main */ [ 4828] | main() { [ 4828] | a::a() { 11.104 [ 4828] | std::operator <<(); 10.825 [ 4828] | std::basic_ostream::operator <<(); 24.514 [ 4828] | } /* a::a */ [ 4828] | a::~a() { 0.978 [ 4828] | ...
Read more

How to put a lock and transaction on table using spring 4 or above using jdbcTemplate and annotations like @Transactional? -

i trying put lock on table while writing on table , if happened in between roll-back . trying convert below code lock table test_g1 read; lock table test_g write; -- begin; start transaction; insert test_g1 values(143); insert test_g values(145); select * test_g1; select * test_g; rollback; select * test_g; unlock tables; how convert above code @transactional spring jdbctemplate code? @transactional(rollbackfor={dataaccessexception.class}) public void test(){ jdbctemplate.execute("insert test1 (id, nam) values (4, 'a')"); throw new dataaccessexception("error") { }; } here trying throw error, insert statement should rollback it's not happening . thanks edit-1 attaching code , doing in jdbcdaoimpl.java , mentioned problem comment above test() . app.java package com.cgiri.javabrains.spring4; import org.springframework.beans.factory.annotation.autowired; import org.springframework.context.applic...
Read more