xml - ImportXML into Google Sheets -

-

i trying stock prices nasdaq using importxml in google sheets. here importxml code:

=importxml(f13,f14) -- in cell c4. , =importxml(f13,f11) -- in cell b4.

f13 has url: http://www.nasdaq.com/symbol/ko/historical f14 has x-path: //[@id="quotes_content_left_pnlajax"]/table/tbody/tr[2]/td[5] f11 has x-path date cell: //[@id="quotes_content_left_pnlajax"]/table/tbody/tr[2]/td[1]/text()

the x-path fed loop increments row number (tr[2] in above) 20 each iteration, each x-path presented sucessively approximately 1 month earlier dates.

this works fine first 4 loops ( ie. row 2, 22, 42, , 62), after fails, , returns n/a.

the nasdaq page shows 3 months of data, unless manually select, say, 2 years data, displays, there no change of url or in form of x-path when 2 years data selected. example, x-path mar. 17, (about 6 mos. back), is: //*[@id="quotes_content_left_pnlajax"]/table/tbody/tr[125]/td[5], differing row number.

my code gets date in same way , date returned n/a @ same time price.

here code:

function prices() { var ss = spreadsheetapp.getactivespreadsheet(); var historicalprices = []; var len = historicalprices.length; var results = ss.getsheetbyname("results"); var ticker = "ko"; var num = 13; var = 0; var interval = 20; var startrow = 2; (i=0 ;i<num ; i++) { var strurl = "http://www.nasdaq.com/symbol/ko/historical"; var strdatexml1 = "//*[@id='quotes_content_left_pnlajax']/table/tbody/tr[";   var offset = startrow + (i * 20); var stroffset = offset.tostring(); var strdatexml3 = "]/td[5]"; var strpricexml1 = '//*[@id="quotes_content_left_pnlajax"]/table/tbody/tr['; var strpricexml3 = "]/td[1]/text()"; var xpath_date = strdatexml1.concat(stroffset,strdatexml3); var xpath_price = strpricexml1.concat(stroffset,strpricexml3);     ss.getsheetbyname("main").getrange("f14").setvalue(xpath_date);     ss.getsheetbyname("main").getrange("f11").setvalue(xpath_price);      ss.getsheetbyname("main").getrange("f13").setvalue(strurl);  var date =  ss.getsheetbyname("main").getrange("b4").getvalue();   var price =  ss.getsheetbyname("main").getrange("c4").getvalue();  var results = ss.getsheetbyname("results"); var ticker_sheet = ss.getsheetbyname("tickers");  if (i == 0) {   results.getrange(1, + 2).setvalue(date).setnumberformat("mm/dd/yyyy");   results.getrange(2, + 1).setvalue(ticker);   results.getrange(2, + 2).setvalue(price).setnumberformat("#,###.##");   } else {   results.getrange(1, + 2).setvalue(date).setnumberformat("mm/dd/yyyy");   results.getrange(2, + 2).setvalue(price).setnumberformat("#,###.##");   } } }

any appreciated!


Comments

Post a Comment

Popular posts from this blog

ios - MKAnnotationView layer is not of expected type: MKLayer -

-
Image
so code works fine logger riddled message. there way rid of or suppress it? postannotation.swift class postannotation: mkpointannotation { //mark: properties let post: post //mark: initialization init(post: post) { self.post = post super.init() self.coordinate = cllocationcoordinate2d(latitude: post.latitude, longitude: post.longitude) self.title = post.title self.subtitle = post.timestring() } } adding annotation let annotation = postannotation(post: post) self.map.addannotation(annotation) func mapview func mapview(_ mapview: mkmapview, viewfor annotation: mkannotation) -> mkannotationview? { if annotation mkuserlocation { return nil } var annotationview = mapview.dequeuereusableannotationview(withidentifier: "pin") as? mkpinannotationview if annotationview == nil { annotationview = mkpinannotationview(annotation: annotation, reuseidentifier: "pin")
Read more

ZeroMQ on Windows, with Qt Creator -

-
i have installed zmq link given below on windows 7. http://zeromq.org/distro:microsoft-windows 1) have c++ project in qt-creator , i'm using zmq c++ binding (zhelpers.hpp etc). 2) have added .lib installed zmq folder qtcreator 3) while compiling, compiler looks _imp__zmq_verion, in .dll files present in .lib have __imp_zmq_version. 4) causing undefined reference zmq_version. anyone else faced problem , has solution ?
Read more

unity3d - Unity SceneManager.LoadScene quits application -

-
i have 2 different scenes 1 named 'main' , other 'game' both added scenes in load. when use scenemanager.loadscene("game"); works no problem when tried return main scene code if (input.getkeydown (keycode.escape)) { scenemanager.loadscene("main"); } strangely game quits. thanks. sorry interruption. somewhere on other script found code. if (input.getkeydown (keycode.escape)) { application.quit (); } that's bad, know...
Read more