indexデータの取得
Option Explicit
Private Sub testParse()
Dim url As String
url = "http://www.morningstar.co.jp/FundData/common/chart/xml/all/MSCI011010.xml"
Application.StatusBar = "Loading: " & url
Dim xdoc As New DOMDocument60
xdoc.async = False
xdoc.Load url
Application.StatusBar = ""
If (xdoc.parseError.ErrorCode <> 0) Then 'ロード失敗
Debug.Print xdoc.parseError.reason 'エラー内容を出力
Exit Sub
End If
Dim code As IXMLDOMNode
Set code = xdoc.SelectSingleNode("//morningstarXML/fund/@code")
Debug.Print code.Text
Dim name As IXMLDOMNode
Set name = xdoc.SelectSingleNode("//morningstarXML/fund/@name")
Debug.Print name.Text
Dim period_end As IXMLDOMNode
Set period_end = xdoc.SelectSingleNode("//morningstarXML/fund/@period_end")
Debug.Print period_end.Text
'//morningstarXML/fund/@name //morningstarXML/fund/@period_end
Dim days As IXMLDOMNodeList
Set days = xdoc.SelectNodes("//day")
Debug.Print days.Length
Dim day As IXMLDOMNode
For Each day In days
Dim attr As IXMLDOMNamedNodeMap
Set attr = day.Attributes
Dim year As String
year = attr.getNamedItem("year").Text
Dim month As String
month = attr.getNamedItem("month").Text
Dim value As String
value = attr.getNamedItem("value").Text
Dim price As String
price = attr.getNamedItem("price").Text
Dim volume As String
volume = attr.getNamedItem("volume").Text
Dim return_value As String
return_value = attr.getNamedItem("return_value").Text
Dim indication As String
indication = attr.getNamedItem("indication").Text
Dim work_end As String
work_end = ""
Dim node As IXMLDOMNode
Set node = attr.getNamedItem("work_end")
If Not node Is Nothing Then
work_end = node.Text
End If
Next
End Sub
Private Sub testLoadIndex()
Dim bk As Workbook
Dim sht As Worksheet
Set bk = ThisWorkbook
LoadIndex "NYGL00100", bk.Sheets("Sheet48") 'bk.Sheets.Add
End Sub
Public Sub LoadAllIndex()
Dim shts As Sheets
Set shts = ThisWorkbook.Sheets
Dim sht As Worksheet
Set sht = shts("index_list")
Dim area As Range
Set area = sht.Range("B:B").SpecialCells(xlCellTypeConstants)
Dim cur As Range
For Each cur In area
Debug.Print cur.value
LoadIndex cur.value, shts.Add(after:=shts(shts.Count))
Next
End Sub
Sub LoadIndex(indexCode As String, sht As Worksheet)
Dim url As String
url = "http://www.morningstar.co.jp/FundData/common/chart/xml/all/" & indexCode & ".xml"
Application.StatusBar = "Loading: " & url
Dim xdoc As New DOMDocument60
xdoc.async = False
xdoc.Load url
Debug.Print url
Application.StatusBar = ""
If (xdoc.parseError.ErrorCode <> 0) Then 'ロード失敗
MsgBox xdoc.parseError.reason 'エラー内容を出力
Exit Sub
End If
Dim code As IXMLDOMNode
Set code = xdoc.SelectSingleNode("//morningstarXML/fund/@code")
Debug.Print code.Text
Dim name As IXMLDOMNode
Set name = xdoc.SelectSingleNode("//morningstarXML/fund/@name")
Debug.Print name.Text
Dim indexName As String
indexName = name.Text
Dim period_end As IXMLDOMNode
Set period_end = xdoc.SelectSingleNode("//morningstarXML/fund/@period_end")
Debug.Print period_end.Text
Dim days As IXMLDOMNodeList
Set days = xdoc.SelectNodes("//day")
Debug.Print days.Length
Application.ScreenUpdating = False
Dim Calculation As XlCalculation
Calculation = Application.Calculation
Application.Calculation = xlCalculationManual
sht.UsedRange.Delete xlToLeft
Dim cur As Range
Set cur = sht.Range("B2")
cur.Offset(, 0).value = "Code"
With cur.Offset(, 1).Resize(, 5)
.HorizontalAlignment = xlLeft
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.MergeCells = True
End With
cur.Offset(, 1).value = code.Text
Set cur = cur.Offset(1)
cur.Offset(, 0).value = "Name"
With cur.Offset(, 1).Resize(, 5)
.HorizontalAlignment = xlLeft
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.MergeCells = True
End With
cur.Offset(, 1).value = name.Text
Set cur = cur.Offset(1)
cur.Offset(, 0).value = "Period End"
With cur.Offset(, 1).Resize(, 5)
.HorizontalAlignment = xlLeft
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.MergeCells = True
End With
cur.Offset(, 1).value = period_end.Text
Set cur = cur.Offset(2)
cur.Offset(, 0).value = "Date"
cur.Offset(, 1).value = "Price"
cur.Offset(, 2).value = "Volume"
cur.Offset(, 3).value = "Return Value"
cur.Offset(, 4).value = "Indication"
cur.Offset(, 5).value = "Work End"
Dim header As Range
Set header = sht.Range(cur, cur.Offset(, 255).End(xlToLeft))
Dim i As Long
Dim l As Long
l = days.Length - 1
For i = 0 To l
Application.StatusBar = indexName & "; " & i & " / " & l
Dim day As IXMLDOMNode
Set day = days(i)
Dim attr As IXMLDOMNamedNodeMap
Set attr = day.Attributes
Dim year As String
year = attr.getNamedItem("year").Text
Dim month As String
month = attr.getNamedItem("month").Text
Dim value As String
value = attr.getNamedItem("value").Text
Dim price As String
price = attr.getNamedItem("price").Text
Dim volume As String
volume = attr.getNamedItem("volume").Text
Dim return_value As String
return_value = attr.getNamedItem("return_value").Text
Dim indication As String
indication = attr.getNamedItem("indication").Text
Dim work_end As String
work_end = ""
Dim node As IXMLDOMNode
Set node = attr.getNamedItem("work_end")
If Not node Is Nothing Then
work_end = node.Text
End If
Set cur = cur.Offset(1)
cur.Offset(, 0).value = year & "/" & month & "/" & value
cur.Offset(, 1).value = price
cur.Offset(, 2).value = volume
cur.Offset(, 3).value = return_value
cur.Offset(, 4).value = indication
cur.Offset(, 5).value = work_end
Next
header.Font.Bold = True
With header
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With header.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 16777164
.TintAndShade = 0
.PatternTintAndShade = 0
End With
header.AutoFilter
header.EntireColumn.AutoFit
Application.StatusBar = False
Application.ScreenUpdating = True
Application.Calculation = Calculation
Dim s As Worksheet
On Error Resume Next
Set s = sht.Parent.Sheets(Left(indexName, 31))
On Error GoTo 0
If s Is Nothing Then
sht.name = Left(indexName, 31)
Else
If vbOK = MsgBox("シートが重複しています。削除しますか？", vbOKCancel) Then
s.Delete
sht.name = Left(indexName, 31)
End If
End If
End Sub
Private Sub showSheetNames()
Dim sht As Worksheet
For Each sht In ThisWorkbook.Sheets
Debug.Print sht.name
Next
End Sub
indexCheckの一覧を取得
var downloadAsTextFile = function(fileName, content) { var bom = new Uint8Array([0xEF, 0xBB, 0xBF]); var blob = new Blob([bom, content]); var url = window.URL || window.webkitURL; var blobURL = url.createObjectURL(blob); var a = document.createElement('a'); a.download = fileName; a.href = blobURL; a.click(); }; var downloadAsCSVFile = function(fileName, rows) { var content = ""; for( var i in rows) { var ary = rows[i]; for (var j = 0, m = ary.length; j < m; ++j) { content += '"' + ("" + ary[j]).replace('"', '""') + '"'; if (j !== m) { content += ','; } } content += '\n'; } downloadAsTextFile(fileName, content); }; downloadAsCSVFile("index_list.csv", $("input[name='indexCheck']").toArray().map((e)=>[e.parentElement.innerText, e.value]));
http://www.morningstar.co.jp/FundData/common/chart/xml/all/<indexCheck>.xml
で，あたりが取り放題？
JB
使っていないiPhone5があったので，なんとなくJBしてみたが，なかなかすんなりいかないね。。。
http://tools4hack.santalab.me/howto-ios10-ios1033-jailbreak-h3lix-32bit-device.html
はじめパスワード思い出すまでハマって，やっと入力できたと思ったら，
Sign in with the app-specific password you generated. If you forgot the app-specific password or need to create a new one, go to appleid.apple.com
なんか出た。app-specificのことを知らず，下記のサイトにたどり着く。
plist.hpp:201 not PLIST_STRING <dict> <key>CFBundleIdentifier</key> <string>org.tihmstar.h3lix</string> </dict>
とエラー。またエラーかと思っていたら，調べてみるとPhoneをチェックせいと。
https://twitter.com/wgarlin3/status/945456552437264384
なんか入っている。開こうとしたら信頼されていないとか言われ，
どこに設定があるねんと思っていたら，下記にあると。
https://qiita.com/ktysne/items/8e3afc029754dd50f758
無事できたみたい。
積立設定取得
var Queue = function() { this.promise = Promise.resolve(true); this.addAsync = (action) => this.promise = this.promise.then(() => new Promise((resolve) => action(resolve))); this.addSync = (action) => this.addAsync((resolve) => { action(); resolve(); }); this.delay = (delay) => this.addAsync((resolve) => setTimeout(resolve, delay)); }; var downloadAsTextFile = function(fileName, content) { var bom = new Uint8Array([0xEF, 0xBB, 0xBF]); var blob = new Blob([bom, content]); var url = window.URL || window.webkitURL; var blobURL = url.createObjectURL(blob); var a = document.createElement('a'); a.download = fileName; a.href = blobURL; a.click(); }; var downloadAsCSVFile = function(fileName, rows) { var content = ""; for( var i in rows) { var ary = rows[i]; for (var j = 0, m = ary.length; j < m; ++j) { content += '"' + ("" + ary[j]).replace('"', '""') + '"'; if (j !== m) { content += ','; } } content += '\n'; } downloadAsTextFile(fileName, content); }; var q = new Queue(); var rows = [["ID", "ファンド名", "コース", "預り区分", "積立金額", "申込設定日", "信託報酬", "信託財産留保額"]]; $("tr.md-l-tr-03 td.vaM > a").filter((i, e) => e.href.match(/param6=(\w+)/i)).each((i,e) => { var td = $("td", $(e).parent().parent()); var name = $("a", td[0]).text(); var course = $(td[1]).text().trim(); var kind = $(td[2]).text().trim(); var amount = $(td[3]).html().replace(/\s/g, "").split("<br>"); q.addAsync((resolve) => { //console.log("open", name); var w = window.open(e.href, "dummy", 'width=100,height=100'); $(w).load(function() { //console.log("loaded", name); var t = $(w.document).find("#CONTENTSAREA01"); rows.push([ w.location.href.match(/param6=(\w+)/i)[1], name, course, kind, amount[0].match(/[\d,]+/)[0], amount[1].match(/\((.*)\)/)[1], t.find("p:contains('信託報酬')").closest("tr").next("tr").text().trim().match(/([\d\.]+)[%％]/)[1]+"%", t.find("p:contains('信託財産留保額')").closest("tr").next("tr").text().trim(), ]); //console.log("close", name); w.close(); resolve(); }) }); }) q.addSync(() => downloadAsCSVFile("hoge.csv", rows));
カテゴリIDのダウンロード
どこに定義があるかと思ったら，変数に読み込んで持っているとは
var downloadAsTextFile = function(fileName, content) { var a = document.createElement('a'); a.download = fileName; a.href = (window.URL || window.webkitURL).createObjectURL(new Blob([new Uint8Array([0xEF, 0xBB, 0xBF]), content])); document.body.appendChild(a); a.click(); document.body.removeChild(a); }; var downloadAsCSVFile = function(fileName, rows) { var content = ""; for(var i in rows) { for (var j = 0, m = rows[i].length; j < m; ++j) content += '"' + ("" + rows[i][j]).replace(/"/g, '""') + '"' + (j !== m ? ',' : ''); content += '\n'; } downloadAsTextFile(fileName, content); }; downloadAsCSVFile("minus_large_category_list.csv", category_list_minus.find(".l_c_name").toArray().map((e) => [e.id, e.innerHTML])); downloadAsCSVFile("minus_middle_category_list.csv", category_list_minus.find(".m_c_name").toArray().map((e) => [e.parentNode.parentNode.id, e.id, e.innerHTML])); downloadAsCSVFile("plus_large_category_list.csv", category_list_plus.find(".l_c_name").toArray().map((e) => [e.id, e.innerHTML])); downloadAsCSVFile("plus_middle_category_list.csv", category_list_plus.find(".m_c_name").toArray().map((e) => [e.parentNode.parentNode.id, e.id, e.innerHTML]));
家計簿を一括ダウンロード，その２
FireFoxのコンソールで実行すると，ページをパースして，管理IDごとCSVに保存。
MoneyforwardのWeb上でカテゴリを変更するのが面倒だから，
Excelで編集して反映したいけど，CSVのアップロードではできない。
なので，一括変更のScriptを作成中。
とりあえず，管理IDがダウンロードできたから，
次はアップデートの方かな。
var downloadAsTextFile = function(fileName, content) {
var a = document.createElement('a');
a.download = fileName;
a.href = (window.URL || window.webkitURL).createObjectURL(new Blob([new Uint8Array([0xEF, 0xBB, 0xBF]), content]));
document.body.appendChild(a);
a.click();
document.body.removeChild(a);
};
var downloadAsCSVFile = function(fileName, rows) {
var content = "";
for(var i in rows) {
for (var j = 0, m = rows[i].length; j < m; ++j) content += '"' + ("" + rows[i][j]).replace(/"/g, '""') + '"' + (j !== m ? ',' : '');
content += '\n';
}
downloadAsTextFile(fileName, content);
};
var parseListBody = function(text, rows) {
var dom = eval("$" + text.match(/list_body.*?(\(.*)/)[1]);
$(dom.filter("tr").get().reverse()).each((i, tr) => {
/*
$("td", tr).each((j, td) => {
rows.push(["tbl", i, j, td.className, $(td).text().trim(), $(td).prop('outerHTML').replace(/[\n\r]/g,"").trim()]);
});
*/
//var form = $("form", tr).serializeArray();
var id = $("input[name*='\[id\]']", tr).val();
var lctg_id = $("input[name*='\[large_category_id\]']", tr).val();
var mctg_id = $("input[name*='\[middle_category_id\]']", tr).val();
var hash = $("input[name*='\[sub_account_id_hash\]']", tr).val();
var income = $("input[name*='\[is_income\]']", tr).val();
var target = $("input[name*='\[is_target\]']", tr).val()||"";
var td_date = $("td.date", tr);
var date_id = td_date.attr("data-table-sortable-value");
var date_str = td_date.text().trim();
var content = $("td.content div span", tr).text().trim();
var amount = $("td.amount span.offset, td.amount div.noform", tr).text().trim();
var amount_memo = $("td.amount div.offset", tr).text().trim();
var pull_right = $("td.amount div.pull-right", tr).text().trim();
var note = $("td.note, td.sub_account_id_hash span", tr).text().trim();
var explain = $("td.calc[title]", tr).attr("title");
var transfer_from = $($("td.calc div.transfer_account_box", tr).parent().prop('outerHTML')).children().empty().parent().text().trim();
var transfer_to = $("td.calc div.transfer_account_box", tr).text().trim();
var lctg = $("td.lctg", tr).text().trim();
var mctg = $("td.mctg", tr).text().trim();
var memo = $("td.memo", tr).text().trim();
rows.push([id, income, target, date_id.substr(0, 10), date_id, date_str, content, amount, amount_memo, pull_right, note, transfer_from, transfer_to, explain, lctg_id, lctg, mctg_id, mctg, memo, hash]);
//rows.push([]);
});
};
var Queue = function() {
this.promise = Promise.resolve(true);
this.addAsync = (action) => this.promise = this.promise.then(() => new Promise((resolve) => action(resolve)));
this.addSync = (action) => this.addAsync((resolve) => { action(); resolve(); });
this.delay = (delay) => this.addAsync((resolve) => setTimeout(resolve, delay));
};
var getList = (q, rows, year, month) => {
q.addAsync((resolve) => {
$.ajax({
dataType: "text",
type: "POST",
url: "https://moneyforward.com/cf/fetch",
data: {
account_id_hash: "",
from: year + "/" + month + "/1",
service_id: ""
},
headers: {
"X-CSRF-Token": $("meta[name=csrf-token]").attr("content")
}
}).done((text) => { console.log(year + "/" + month + "/1"); parseListBody(text, rows); resolve(); });
});
};
var q = new Queue();
var rows = [["id", "income", "target", "date", "date_id", "date_str", "content", "amount", "amount_memo", "pull_right", "note", "transfer_from", "transfer_to", "explain", "lctg_id", "lctg", "mctg_id", "mctg", "memo", "hash"]];
for(var y = 2010; y <= 2018; ++y) {
for(var m = 1; m <= 12; ++m) {
getList(q, rows, y, m);
}
}
q.addSync(() => downloadAsCSVFile("hoge.csv", rows));
q.addSync(() => alert("完了"));
(()=>{ var Queue = function() { this.promise = Promise.resolve(true); this.addAsync = (action) => this.promise = this.promise.then(() => new Promise((resolve) => action(resolve))); this.addSync = (action) => this.addAsync((resolve) => { action(); resolve(); }); this.delay = (delay) => this.addAsync((resolve) => setTimeout(resolve, delay)); }; var q = new Queue(); var jqxhr, jqxhr2; var sub_win_option = "top=0,left=0,width=500,height=500,scrollbars=1,location=0,menubar=0,toolbar=0,status=1,directories=0,resizable=1"; var sv_win = window.open(window.location, "show_view_window", sub_win_option); $(sv_win.document).ready(() => { var w = $(window); var st = w.scrollTop(), sb = st + w.height(); var sub; q.delay(2000); q.addSync(() => { sub = $(sv_win.document.body); sub.empty(); }); $('a._work, a.gtm-thumbnail-link').each((i,v) => { var a = $(v); var t = a.offset().top; if(st < t && t < sb) { var href = a.attr('href'); var is_manga = $('.page-count, ._1VJYUl1', a ).length > 0; var fetch_manga = (resolve) => { var url = href.replace(/mode=medium/, "mode=manga"); console.log("fetch_manga", url); if(jqxhr2) { jqxhr2.abort(); } jqxhr2 = $.ajax({ url: url }).done( (data) => { $('section.manga img', data).each((i, e) => { $('<img/>').attr("src", $(e, data).attr('data-src')).css({ "max-width": "-moz-available" }).appendTo(sub); }); resolve(); }).fail((jqXHR, textStatus, errorThrown) => { console.log('fail', jqXHR.status); resolve(); }); }; var fetch_image = (resolve) => { console.log("fetch_image", href); if(jqxhr) { jqxhr.abort(); } jqxhr = $.ajax({ url: href }).done( (data) => { var img = $("img.original-image", data); if(img.length > 0) { $('<img/>').attr("src", img.attr('data-src')).css({ "max-width": "-moz-available" }).appendTo(sub); resolve(); } else { fetch_manga(resolve); } }); }; q.addAsync((resolve) => { //console.log(is_manga, href); if( is_manga ) { fetch_manga(resolve); } else { fetch_image(resolve); } }); } }); }); })();
非同期処理をPromise でシーケンシャルに処理
まあ，車輪の再開発。ただ，Promiseとか難しいこと考えずに，再利用できないとね。。。
rejectは使わないだろうから省略。
resolve もなくしたいけど，非同期ができないからね。。。
var Queue = function() { this.promise = Promise.resolve(true); this.addAsync = (action) => this.promise = this.promise.then(() => new Promise((resolve) => action(resolve))); this.addSync = (action) => this.addAsync((resolve) => { action(); resolve(); }); this.delay = (delay) => this.addAsync((resolve) => setTimeout(resolve, delay)); }; var list = [ { id: 1, name: 'taro' }, { id: 2, name: 'jiro' }, { id: 3, name: 'saburo' } ]; var q = new Queue(); list.forEach((args) => { q.addAsync((resolve) => { console.log(args); setTimeout(resolve, 1000); }); }); list.forEach((args) => { q.delay(1000); q.addSync(() => console.log(args)); });
参考：
http://kitak.hatenablog.jp/entry/2014/12/01/234759
https://qiita.com/AkihiroTakamura/items/1fdf6dfcb5cb8d5cfc4c
