• バージョン
• ダウンロード 1
• ファイルサイズ 1.44 KB
• ファイル数 1
• 投稿日 2026年5月8日
• 最終更新日時 2026年5月8日

yahoo_japanを用いてGNUCASH DBに登録

#!/usr/bin/perl
use strict;
use warnings;
use DBI;
use Finance::Quote;
use POSIX qw(strftime);

my $DB_HOST = 'localhost'; # Ubuntu が DB サーバー本体の場合
my $DB_NAME = 'gnucash';
my $DB_USER = 'postgres';
my $DB_PASS = '';
my $ALPHAVANTAGE_API_KEY = '**********';

$ENV{FQ_CURRENCY} = 'ECB';
$ENV{ALPHAVANTAGE_API_KEY} = $ALPHAVANTAGE_API_KEY;

my $dbh = DBI->connect("dbi:Pg:dbname=$DB_NAME;host=$DB_HOST", $DB_USER, $DB_PASS,
{ PrintError => 1, AutoCommit => 1 }) or die "DB接続失敗: $DBI::errstr";

my $sth = $dbh->prepare(
"SELECT guid, mnemonic, namespace, quote_source FROM commodities WHERE quote_flag=1"
);
$sth->execute();
my @commodities;
while (my $row = $sth->fetchrow_hashref()) {
push @commodities, $row;
}

my %groups;
for my $c (@commodities) {
my $src = $c->{quote_source} // '';
next unless $src && $src ne 'currency';
push @{$groups{$src}}, $c;
}

my $q = Finance::Quote->new('YahooJapan', 'AlphaVantage');
my $now = strftime('%Y-%m-%d %H:%M:%S', localtime);

for my $src (keys %groups) {
my @symbols = map { $_->{mnemonic} } @{$groups{$src}};
print "取得中: $src => @symbols
";

my %data = $q->fetch($src, @symbols);

for my $c (@{$groups{$src}}) {
my $sym = $c->{mnemonic};
unless ($data{$sym, 'success'}) {
print " SKIP $sym: ", ($data{$sym, 'errormsg'} // 'no data'), "
";
next;
}

my $price = $data{$sym, 'price'} // $data{$sym, 'last'};
my $date = $data{$sym, 'isodate'} // substr($now, 0, 10);
my $curr = $data{$sym, 'currency'} // 'JPY';

unless ($price) { print " SKIP $sym: price なし
"; next; }

my ($curr_guid) = $dbh->selectrow_array(
"SELECT guid FROM commodities WHERE mnemonic=? AND namespace='CURRENCY'", undef, $curr
);
unless ($curr_guid) { print " SKIP $sym: 通貨 $curr なし
"; next; }

my ($exists) = $dbh->selectrow_array(
"SELECT count(*) FROM prices WHERE commodity_guid=? AND date::date=?",
undef, $c->{guid}, $date
);
if ($exists) { print " SKIP $sym: $date 既存
"; next; }

my $denom = 10000;
my $num = int($price * $denom + 0.5);
my ($new_guid) = $dbh->selectrow_array(
"SELECT replace(gen_random_uuid()::text, '-', '')"
);

$dbh->do(
"INSERT INTO prices (guid, commodity_guid, currency_guid, date, source, type, value_num, value_denom)
VALUES (?, ?, ?, ?, 'Finance::Quote', 'last', ?, ?)",
undef, $new_guid, $c->{guid}, $curr_guid, "$date 00:00:00", $num, $denom
);
print " OK $sym: $price $curr ($date)
";
}
}

$dbh->disconnect();
print "完了
";