Python, Javascript and UNIX hacker, open source advocate, IRC addict, general badass and traveler
394 stories
·
8 followers

What happens when a 96 bitcoin ransom payment ends up on Bitfinex?

1 Share

"Hello, to get your data back you have to pay for the decryption tool, the price is $1,200,000... You have to make the payment in Bitcoins."

This is a snippet from a recent court case concerning ransomware that just crossed my desk. Companies that fall victim to ransom attacks fear the publicity it might attract, so the details of these attacks are usually swept under the table. But in this case, the ransom payer—a British insurer that traced the bitcoins to Bitfinex, a major bitcoin exchange—has appealed to the UK High Court for an injunction, thus providing us with a vivid peak into the inner workings of an actual attack.

Ransomware is a big issue these days. A hacker maliciously installs software on a victim's computers, encrypts various files, and then asks for a bitcoin ransom to fix the problem.

It's the bitcoin leg of this transaction that has made these attacks economical. Prior to bitcoin, running an illicit business based on ransom payments was fraught. Bank accounts leave a paper trail. Cash, though anonymous, can't be transferred remotely. And gift cards are limited to small amounts. With bitcoin, hackers finally gained access to a form of electronic cash that allowed them to not only make remote ransom demands, but large ones too.

A steady parade of ransomware has since emerged. While early types of ransomware like WannaCry, CryptoLocker, and Locky targeted personal computers for small amounts of money, the most recent strains—Maze, Sodinokobi, Nemty, and others—attack governments and enterprises for million dollar amounts. The Nunavut government, a territory in Northern Canada, was a recent victim:

One thing I've never really understood is why ransomware can be so widespread given that all bitcoin transactions are written to the public blockchain. I mean, can't a bitcoin ransom payment be easily tracked to its final destination, say a bitcoin exchange, and frozen?

The court case in question, AA v Persons Unknown & Others, Re Bitcoin, provides some insights into just that. Although the judge heard the case back on December 13, 2019, the text of the injunction was only released a few days ago.

It makes for entertaining reading. Here's a short timeline:
  • In Autumn 2019, a Canadian company was hacked. The hacker installed BitPaymer, a strain of ransomware, which encrypted the company's files
  • The hacker demanded $1.2 million in bitcoins
  • Luckily, the Canadian company had cyber crime attack insurance with a British insurer
  • The British insurance company hired an "Incident Response Company" to pay the ransom
  • The response company negotiated for a reduction in ransom to $905,000
  • The bitcoins were acquired and sent to the hacker on October 10, 2019. According to the injunction, the purchase of the 109.25 coins was conducted by "an agent of the Insurer, who was referred to as JJ."
  • Having receive the ransom, the hacker provided the fix. The files were successfully decrypted
  • The insurance company wanted its money back, so in December it hired a blockchain analytics company, Chainalysis, to trace the ransom payment
  • Chainalysis tracked 96 of bitcoins to an address linked to Bitfinex, a major bitcoin exchange
  • The insurer then went to British High Court to force Bitfinex to reveal the identity of "PERSONS UNKNOWN WHO OWN/CONTROL SPECIFIED BITCOIN" and to freeze the 96 bitcoins.

So were the 96 bitcoins returned to the insurer?

For now, we don't know the final outcome. The document only brings us up to December 13, 2019, when the judge gave Bitfinex till December 19 to provide the names of “persons unknown”, the owner of the 96 bitcoins. To prevent "persons unknown" from getting wind of the proceedings and fleeing with their coins, the hearing was held in private and the text of the case suppressed. The document having been made public, we can assume that some sort of resolution was arrived at.

It's interesting to speculate what this resolution might have been. Bitcoin is still a relatively new, and thus largely undefined, phenomenon. As bitcoin cases slowly trickle into the court system, the decisions made by judges will be important in determining the eventual legal status of cryptocurrencies.

It could be that "persons unknown" is the same individual who perpetrated the initial ransom attack, and they just haven't yet sold the 96 bitcoins yet. In which case the conclusion is simple: the guilty party will be prosecuted and Bitfinex will return the bitcoins.

But it is more interesting (and more likely) that "persons unknown" is a third-party (say an over-the-counter broker) who bought the bitcoins from the hacker, and deposited them at Bitfinex, and hasn't sold them yet.

This third-party could be entirely innocent about the origin of the coins. They might try to say to the judge: "hey—we didn't know the 96 bitcoins we bought were linked to ransom payments. We shouldn't have to give them back."

But that's not how property law works. Even if you accidentally come into possession of stolen property—and surely ransomed bitcoins qualify as stolen—then a judge can still force you to give them back to the rightful owner. This would be bad news for the innocent broker. Being obliged to cough up 96 bitcoins could easily bankrupt it.

"Persons unknown" might respond to the injunction by pleading that the 96 bitcoins are a form of money, like banknotes, and so they needn't be returned. Banknotes, coins, and other highly-liquid paper instruments have a very special legal status. If you unknowingly accept some banknotes from someone who just obtained them illegally (say via ransom or theft), the law can't compel you to give those banknotes back to the original victim. Money, as the great British jurist Lord Mansfield once declared, isn't like regular property: it "can not be recovered after it has passed into currency."

This special legal status (which I’ve written about before) was granted to banknotes centuries ago in order to ensure that these early forms of money remained highly liquid. If every merchant had to verify that the notes they were about to receive weren't stolen, the wheels of trade would have ground to a halt. Whether a modern judge would be willing to extend this sanctuary to cryptocurrency, and thus allow “persons unknown” to keep the 96 coins, remains to be seen. But I’m skeptical.


Another possibility is that the person (or company) that innocently accepted the 96 ransomed bitcoins and deposited them on Bitfinex has already sold them. If so, which party does the British insurance company have to pursue? Some entity (or group of entities) must now be in possession of the 96 bitcoins, right? Can’t the insurer just go after the next person down the chain?

I don't know the specifics about how an exchange like Bitfinex hold bitcoins for clients, but it may be very difficult to pinpoint who actually has title to those specific 96 bitcoins. When bitcoins are deposited at an exchange, they are sent to the exchange's hot wallet along with all other incoming bitcoin deposits. So the ransomed bitcoins would have been commingled with a bunch of clean bitcoins.

When the person who originally deposited the 96 bitcoins on Bitfinex put in an order to sell on the exchange's order book, the unsuspecting buyers (all of them Bitfinex customers) would now have a claim on various bitcoins held in Bitfinex's hot wallet. Are the bitcoins on which they have a claim necessarily the ransomed ones? Or do the buyers just have a general claim on any random bitcoin held on their behalf by Bitfinex? If so, would that mean that Bitfinex itself is on the hook for paying the insurer 96 bitcoins?

Anyways, you can see how this all gets complicated very fast. A lot is riding on how thoroughly the history of unspent bitcoin outputs can be traced.

Given bitcoin traceability and the ease of getting an injunction, one can imagine that it might make sense for insurers, bitcoin exchanges, and over-the-counter traders to build some sort of private "ransom registry". The moment that an insurer pays a ransom to a hacker, that insurer simultaneously announces the offending address to the registry. A verified OTC trading desk can now protect itself from potential bankruptcy by always checking the registry to make sure that any bitcoins offered to it are "good" bitcoins. Exchanges too would likewise cross-check incoming bitcoin deposits against the registry.

This would be good news for potential ransom victims. With the exits for ransom payments being choked off, these sorts of exploits would become less feasible. Extortionists may simply stop trying to run their schemes.

You could also imagine hackers coming up with strategies for dissuading victims from posting transactions to the ransom registry. "If you announce the ransom payment to the registry, we'll leak your files to the public," or something along those lines.

Or maybe extortionists will simply start to use bitcoin mixers more. Mixers are services that allow people to commingle their bitcoins in order to preserve anonymity. Astonishingly, most ransom payments don't currently go through mixing services. According to Chainalysis, the company that was hired by the British insurer, around half of the addresses to which ransom is paid redirect the bitcoins to an exchange.

But even if hackers did use mixers, bitcoin exchanges may be reticent to accept incoming deposits. Binance, for instance, recently refused to make a payout to Wasabi, a wallet that automatically mixes bitcoins. Should exchanges like Bitfinex all refuse to accept bitcoins that have been mixed, that chokes off the ability to extort people using bitcoin as ransom.

For now, we don't know how the defendant’s responded to the injunction. But in any case, it makes for interesting speculation.
Read the whole story
miohtama
30 days ago
reply
Helsinki, Finland
Share this story
Delete

PV Solar panel analysis in postgres

1 Share

Australian (and especially South Australian) electricity is expensive. That is probably part of the reason that SA is leading the world in residential rooftop solar.

But it is hard to negotiate the electricity retail market. Knowing your daily usage and feed-in enables you to determine which retailer you should choose, but only if you have the tools available to you to compare what your costs would have been with each retail plan.

And this does not even begin to address the question of if it is cost-effective to increase the size of your PV generation system: most supply companies (who, as it turns out, are different entities to the retailers) have per-phase limits of 5kW: that is, you may never feed back into the grid more than 5kW at any point in time.

Since I had my Fronius Smart Meter installed, I have been able to run reports and get my daily import and export values: these are also available through my retailer, and even through SA Power Networks, the distributor.

I have my data stored in three tables:

REATE TABLE generation (
  "timestamp" TIMESTAMP UNIQUE PRIMARY KEY,
  "energy" FLOAT
);

CREATE TABLE import_cumulative (
  "timestamp" TIMESTAMP UNIQUE PRIMARY KEY,
  "energy" INTEGER
);

CREATE TABLE export_cumulative (
  "timestamp" TIMESTAMP UNIQUE PRIMARY KEY,
  "energy" INTEGER
);

This is really only necessary because I need to take the cumulative values in the import and export tables, and turn them into absolute values for that period:

CREATE OR REPLACE VIEW import AS (
  SELECT timestamp,
         energy - LAG(energy, 1) OVER (ORDER BY timestamp) AS energy
    FROM import_cumulative
   ORDER BY timestamp
);

CREATE OR REPLACE VIEW export AS (
  SELECT timestamp,
         energy - LAG(energy, 1) OVER (ORDER BY timestamp) AS energy
    FROM export_cumulative
   ORDER BY timestamp
);

Now, from these views, it is possible to do some modelling of what different retailers would cost, but they generally need stuff per-day, so we can create a view that combines them. Since I will be doing stuff later that also needs consumption and generation, I will also include them:

CREATE OR REPLACE VIEW consumption AS (
    SELECT timestamp,
           CASE WHEN import.energy > 0 THEN import.energy + generation.energy
                ELSE generation.energy - export.energy END AS energy
      FROM generation
     INNER JOIN import USING (timestamp)
     INNER JOIN export USING (timestamp)
);

And our summary (for each interval):

CREATE OR REPLACE VIEW summary AS (
    SELECT timestamp,
           generation.energy AS generation,
           import.energy AS import,
           export.energy AS export,
           import.energy + generation.energy - export.energy AS consumption
      FROM generation
     INNER JOIN import USING (timestamp)
     INNER JOIN export USING (timestamp)    
);

Getting close to having some usable data there now. What about daily summaries?

CREATE OR REPLACE VIEW daily_summary AS (
    SELECT timestamp::DATE AS date,
           SUM(generation) AS generation,
           SUM(import) AS import,
           SUM(export) AS export,
           SUM(consumption) AS consumption
      FROM summary
     GROUP BY timestamp::DATE
);

That gives us per-day summaries of our generation, import and export.

Here are some values from my system:

SELECT * FROM daily_summary LIMIT 10;
date generation import export consumption
2019-03-07 29851.915 7587 24191 13247.915
2019-03-08 14033.2047222222 7578 9156 12455.2047222222
2019-03-09 28074.8316666667 5746 20961 12859.8316666667
2019-03-10 15239.6936111111 7669 9144 13764.6936111111
2019-03-11 21101.1441666667 6528 12657 14972.1441666667
2019-03-12 28231.4280555556 5955 22382 11804.4280555556
2019-03-13 22880.3077777778 6040 17411 11509.3077777778
2019-03-14 30440.8319444444 6658 24791 12307.8319444444
2019-03-15 30141.3888888889 6576 26227 10490.3888888889
2019-03-16 29286.4413888889 7336 22224 14398.4413888889

(10 rows)

Right. So now we want to be able to look at costs for different retailers. There is a magnificent resource for getting this data: Energy Made Easy. The rates differ for different postcodes (WAT): I’m not sure if they are the same across a whole state or not. Probably not.

Anyway, it turns out that there are some retailers who have three different rates, depending upon your usage during a day. It’s not clear to me if this value is averaged across the whole month when you are on a smart meter: I know it is when you are on one of the old spinning disk meters. I assume it would be calculated on each day, as I think that works out as more expensive for the customer. Diamond Energy appears to have per-month limits, and four different rates, so I just pro-rated them to a 30 day month. I did not bother modelling the fourth rate, as I established fairly early on that they are not going to be close to being the cheapest. I may implement that later, just for completeness.

So, to be able to calculate costs for all retailers, we need to store up to three rates, and the two kWh thresholds that trigger those limits. Note that the second

CREATE TABLE retailer (
    name TEXT PRIMARY KEY,
    supply_charge NUMERIC NOT NULL,
    kWh_rate_1 NUMERIC NOT NULL,
    kWh_limit_1 NUMERIC,
    kWh_rate_2 NUMERIC,
    kWh_limit_2 NUMERIC,
    kWh_rate_3 NUMERIC,
    pay_on_time_discount NUMERIC,
    feed_in_tariff NUMERIC NOT NULL
);

ALTER TABLE retailer 
    ADD CONSTRAINT rate_2_requires_limit_1 
        CHECK (kWh_rate_2 IS NULL OR kWh_limit_1 IS NOT NULL),
    ADD CONSTRAINT rate_3_requires_limit_2 
        CHECK (kWh_rate_3 IS NULL OR kWh_limit_2 IS NOT NULL),
    ADD CONSTRAINT rate_3_requires_rate_2 
        CHECK (kWh_rate_3 IS NULL OR kWh_rate_2 IS NOT NULL);

The three check constraints just ensure that we do not accidentally insert a rate where there is no lower tier rate, or no limit in place to apply it from.

Here is a query that inserts values for the retailers for my postcode:

INSERT INTO retailer (
  name, supply_charge, 
  kWh_rate_1, kWh_limit_1, 
  kWh_rate_2, kWh_limit_2, 
  kWh_rate_3, 
  feed_in_tariff, 
  pay_on_time_discount)
VALUES 
  ('AGL Essentials', 0.8855, 0.3575, NULL, NULL, NULL, NULL, 0.1630, NULL),
  ('Origin Energy', 0.9033, 0.4002, 4, 0.4272, NULL, NULL, 0.1800, 0.10),
  ('AGL Solar Savers', 0.8910, 0.4038, NULL, NULL, NULL, NULL, 0.1800, NULL),
  ('Click Banksia Solar', 0.9966, 0.3941, NULL, NULL, NULL, NULL, 0.1700, NULL),
  ('Power Direct Residential Saver', 0.8910, 0.4038, NULL, NULL, NULL, NULL, 0.1420, 0.13),
  ('SA Simply RAA VPP', 0.9863, 0.3600, NULL, NULL, NULL, NULL, 0.1500, NULL),
  ('Kogan', 0.9566, 0.3154, NULL, NULL, NULL, NULL, 0.0748, NULL),
  ('PowerShop Shopper Market', 1.1912, 0.3760, NULL, NULL, NULL, NULL, 0.1020, 0.15),
  ('AGL Residential Essentials', 0.7752, 0.3512, NULL, NULL, NULL, NULL, 0.1420, NULL),
  ('Diamond Energy Everyday Renewable Saver', 0.9565, 0.3659, 3.33, 0.3948, 11.1, 0.4285, 0.12, NULL),
  ('Red Energy Living Energy Saver', 0.9790, 0.3641, NULL, NULL, NULL, NULL, 0.1200, NULL),
  ('Blue NRG DMO - General Usage', 0.7810, 0.3736, 10.96, 0.5775, NULL, NULL, 0.0680, NULL),
  ('Energy Australia Total Plan Home', 0.8580, 0.4070, NULL, NULL, NULL, NULL, 0.15, NULL),
  ('Alinta Energy No Fuss (Single Rate)', 0.9130, 0.3317, 3.29, 0.3695, NULL, NULL, 0.0950, NULL),
  ('Lumo Basic', 1.0120, 0.3539, NULL, NULL, NULL, NULL, 0.15, NULL),
  ('Powerband Home Flat + Solar FIT', 1.0322, 0.3066, 10.95, 0.3139, NULL, NULL, 0.1150, NULL),
  ('Origin Energy Econnex', 0.9081, 0.4024, 10.96, 0.4294, NULL, NULL, 0.1000, 0.15)
  ON CONFLICT DO NOTHING
;

To calculate the cost for a day, we need to add the daily supply charge, and the amount of import that applies at each rate multiplied by that rate, minus the FIT (feed-in tariff) for the export amount. Because some retailers have a “pay on time discount” (which is really just a late-payment penalty, but that is another blog post waiting to happen), it’s important to note that this discount applies to the whole bill - meaning it needs to be applied after the feed-in tariff has been subtracted.

Here is a function that calculates a daily cost, given a retailer record and import and export values:

CREATE OR REPLACE FUNCTION cost(retailer RETAILER, import INTEGER, export INTEGER)
RETURNS NUMERIC AS $$

SELECT COALESCE(1 - retailer.pay_on_time_discount, 1.0) * (retailer.supply_charge +
       -- rate 3
       CASE WHEN retailer.kWh_limit_2 IS NULL THEN 0
            WHEN import <= retailer.kWh_limit_2 THEN 0
            ELSE import - retailer.kWh_limit_2
       END / 1000.0 * COALESCE(retailer.kWh_rate_3, 0) +
       -- rate 2: hours between limit 1 and limit 2
       CASE WHEN retailer.kWh_limit_1 IS NULL THEN 0
            WHEN retailer.kWh_limit_2 IS NULL THEN GREATEST(import - retailer.kWh_limit_1, 0)
            WHEN import < retailer.kWh_limit_1 THEN 0
            ELSE LEAST(import, retailer.kWh_limit_2) - retailer.kWh_limit_1
       END / 1000.0 * COALESCE(retailer.kWh_rate_2, 0) +
       -- rate 1: up to limit 1
       CASE WHEN retailer.kWh_limit_1 IS NULL THEN import
            ELSE LEAST(import, retailer.kWh_limit_1)
       END / 1000.0 * retailer.kWh_rate_1 -
       retailer.feed_in_tariff * export / 1000.0);

$$ LANGUAGE SQL STRICT;

It is a little hard to follow, and if the daily amount was negative, it decreases that value (instead of ignoring the discount), but it will do for now. I would prefer not to choose a retailer that hides a penalty behind a so-called discount anyway, so they would have to be significantly cheaper to attract my custom.

Using this function, we can now calculate the total costs for all retailers, and compare them:

SELECT retailer.name,
       '$ ' || lpad(
         TRUNC(SUM(cost(retailer, import::INTEGER, export::INTEGER)), 2)::TEXT,
         9
       ) AS cost
  FROM daily_summary, retailer
 GROUP BY retailer.name
 ORDER BY cost ASC;

There is a bunch of code in there to make the output prettier: some of that is lost by the HTML output I have used to generate this table. In an SQL view, those decimal points would all line up…

name cost
AGL Essentials $ 897.87
AGL Residential Essentials $ 916.85
Powerband Home Flat + Solar FIT $ 955.59
Lumo Basic $ 965.56
Origin Energy $ 971.44
SA Simply RAA VPP $ 978.81
Power Direct Residential Saver $ 979.84
AGL Solar Savers $ 997.31
Click Banksia Solar $ 1028.43
PowerShop Shopper Market $ 1065.24
Kogan $ 1075.74
Red Energy Living Energy Saver $ 1092.32
Energy Australia Total Plan Home $ 1100.55
Origin Energy Econnex $ 1155.60
Alinta Energy No Fuss (Single Rate) $ 1176.65
Diamond Energy Everyday Renewable Saver $ 1302.24
Blue NRG DMO - General Usage $ 1929.36

(17 rows)

Which brings me to my conclusion: my current retailer is the cheapest for my current generation and consumption patterns.


But we can go further. We can always go further.

We can look at different months, and see if it’s always the case that a given retailer is the best option. Perhaps it is worthwhile shopping around, and switching between retailers at different times of the year.

CREATE OR REPLACE VIEW monthly_cost_comparisons AS

SELECT retailer.name, 
       to_char(date, 'YYYY-MM') AS month,
       SUM(cost(retailer, import::INTEGER, export::INTEGER)) AS cost
  FROM daily_summary, retailer
  GROUP BY retailer.name, to_char(date, 'YYYY-MM');

That by itself is not that useful, but we can use the crosstab feature to view it in a more readable manner.

CREATE EXTENSION IF NOT EXISTS crosstab;

SELECT * 
  FROM crosstab(
    $$SELECT name, month, '$ ' || lpad(cost::INTEGER::TEXT, 4)
       FROM monthly_cost_comparisons ORDER BY name$$, 
    'SELECT DISTINCT month FROM monthly_summary ORDER BY month'
) AS (
    "retailer" TEXT, 
    "2019-03" TEXT,
    "2019-04" TEXT,
    "2019-05" TEXT,
    "2019-06" TEXT,
    "2019-07" TEXT,
    "2019-08" TEXT,
    "2019-09" TEXT,
    "2019-10" TEXT,
    "2019-11" TEXT,
    "2019-12" TEXT
);

Frustratingly, this function requires enumerating the columns.

retailer 2019-03 2019-04 2019-05 2019-06 2019-07 2019-08 2019-09 2019-10 2019-11 2019-12
AGL Essentials $ 14 $ 41 $ 128 $ 200 $ 209 $ 223 $ 90 $ 1 $ 1 $ -10
AGL Residential Essentials $ 19 $ 45 $ 127 $ 196 $ 204 $ 219 $ 93 $ 10 $ 9 $ -5
AGL Solar Savers $ 15 $ 45 $ 143 $ 223 $ 233 $ 249 $ 100 $ 1 $ 0 $ -11
Alinta Energy No Fuss (Single Rate) $ 46 $ 72 $ 147 $ 216 $ 225 $ 242 $ 122 $ 47 $ 46 $ 13
Blue NRG DMO - General Usage $ 91 $ 125 $ 226 $ 329 $ 341 $ 371 $ 203 $ 103 $ 102 $ 38
Click Banksia Solar $ 20 $ 50 $ 144 $ 222 $ 232 $ 248 $ 104 $ 8 $ 8 $ -7
Diamond Energy Everyday Renewable Saver $ 47 $ 76 $ 166 $ 246 $ 256 $ 276 $ 135 $ 45 $ 45 $ 11
Energy Australia Total Plan Home $ 28 $ 57 $ 149 $ 228 $ 237 $ 255 $ 112 $ 19 $ 19 $ -2
Kogan $ 47 $ 69 $ 132 $ 191 $ 198 $ 213 $ 112 $ 49 $ 49 $ 16
Lumo Basic $ 22 $ 49 $ 134 $ 203 $ 212 $ 227 $ 98 $ 12 $ 12 $ -4
Origin Energy Econnex $ 46 $ 71 $ 143 $ 211 $ 219 $ 236 $ 120 $ 48 $ 47 $ 14
Origin Energy $ 17 $ 45 $ 136 $ 213 $ 222 $ 238 $ 98 $ 5 $ 5 $ -8
Power Direct Residential Saver $ 27 $ 52 $ 131 $ 198 $ 207 $ 222 $ 100 $ 21 $ 21 $ 1
PowerShop Shopper Market $ 44 $ 67 $ 133 $ 193 $ 201 $ 215 $ 111 $ 44 $ 44 $ 13
Powerband Home Flat + Solar FIT $ 31 $ 55 $ 126 $ 187 $ 195 $ 208 $ 98 $ 26 $ 26 $ 4
Red Energy Living Energy Saver $ 36 $ 63 $ 142 $ 212 $ 221 $ 237 $ 112 $ 32 $ 32 $ 6
SA Simply RAA VPP $ 23 $ 50 $ 135 $ 206 $ 215 $ 230 $ 99 $ 13 $ 13 $ -4

From this, I can see that nothing is really going to save me more than a few bucks a month: and this does not take into account the $200 “bonus” I get from AGL if I stick with them for a year (or something like that).

I’d also like to look at actual billing periods, because then I can see that everything matches up to what my actual bills are (and then I can add in the bonuses at the times they applied).

My billing periods went to monthly on a specific date, so from then on they are regular one-month periods, but prior to that they were a little all over the place:

CREATE OR REPLACE VIEW billing_periods AS 

SELECT 'AGL Essentials'::TEXT AS retailer,
       DATERANGE(start::DATE, (start + INTERVAL '1 month')::DATE) as period
  FROM generate_series('2019-06-24'::DATE, now()::DATE, INTERVAL '1 month') start
  
 UNION ALL
 
 VALUES ('AGL Essentials'::TEXT, '[2019-03-07,2019-06-01)'::DATERANGE),
        ('AGL Essentials', '[2019-06-01,2019-06-24)')
  
  ORDER BY period DESC;

We can model the retailer bonuses in a way that only applies them when they apply, and if we are calculating the costs for that retailer:

CREATE TABLE retailer_bonus (
  retailer TEXT, 
  billing_period DATERANGE, 
  bonus NUMERIC
);

INSERT INTO retailer_bonus(retailer, billing_period, bonus)
     VALUES ('AGL Essentials', '[2019-08-24,2019-09-24)', 25);

And now some views for calculating the actual costs for each day, and then aggregating within the billing periods (and a pretty version).

CREATE OR REPLACE VIEW actual_costs AS

SELECT retailer,
       billing_periods.period,
       daily_summary.date,
       cost(_ret, import::INTEGER, export::INTEGER),
       generation
  FROM billing_periods
 INNER JOIN retailer _ret ON (_ret.name = billing_periods.retailer)
 INNER JOIN daily_summary ON (daily_summary.date <@ billing_periods.period);
 
 
CREATE OR REPLACE VIEW billing_period_costs AS
 
SELECT retailer, 
       period, 
       SUM(cost) - COALESCE((SELECT SUM(bonus) 
                               FROM retailer_bonus 
                              WHERE billing_period = period
                                AND retailer_bonus.retailer = retailer.name), 0) AS cost, 
       SUM(generation) AS generation
  FROM actual_costs
 GROUP BY retailer, period
 ORDER BY period DESC;
 
 
CREATE OR REPLACE VIEW billing_period_costs_pretty AS 
SELECT retailer, 
       period,
       '$ ' || LPAD(TRUNC(cost, 2)::TEXT, 7, ' ') AS cost,
       LPAD(TRUNC(generation::NUMERIC / 1000, 1)::TEXT, 6, ' ') || ' kWh' AS generation
  FROM billing_period_costs
 ORDER BY period DESC;
 

And now, we can look at the actual bills, with generation for that period:

 SELECT * FROM billing_period_costs_pretty;
 
retailer period cost generation
AGL Essentials [2019-11-24,2019-12-24) $ -9.50  595.4 kWh
AGL Essentials [2019-10-24,2019-11-24) $ -1.20  879.0 kWh
AGL Essentials [2019-09-24,2019-10-24) $ 12.80  757.3 kWh
AGL Essentials [2019-08-24,2019-09-24) $ 104.30  610.6 kWh
AGL Essentials [2019-07-24,2019-08-24) $ 224.27  374.2 kWh
AGL Essentials [2019-06-24,2019-07-24) $ 210.24  302.8 kWh
AGL Essentials [2019-06-01,2019-06-24) $ 148.31  244.8 kWh
AGL Essentials [2019-03-07,2019-06-01) $ 183.65 1495.9 kWh

Note that the first row is the current month: it’s only partway through, so I will end up with even more of a credit than that, with any luck.

These values are all within a dollar or two of the amounts I was actually billed, so that is nice.


But wait, there is more!

We can use this, with just a couple more functions, to make fairly good estimates of what our outputs and costs would be if we had more panels.

But that one will have to wait for another day.

Read the whole story
miohtama
75 days ago
reply
Helsinki, Finland
Share this story
Delete

New York’s problem with Tether — as set out for the appeal judges

2 Shares

The New York Attorney General is investigating the iFinex companies — who you’ll know as crypto exchange Bitfinex and dollar-substitute stablecoin Tether.

The NY AG thinks there’s been considerable funny business with the Tether reserve — and want to get to the bottom of this. iFinex have bitterly resisted even being investigated.

The investigation started in late 2018. By April 2019, iFinex had been sufficiently uncooperative that the NY AG filed to compel discovery of documents from them. The case dragged on, as iFinex tried to block discovery, and claimed the investigation itself was illegitimate and should be stopped.

In August 2019, the court finally ruled that iFinex had to cooperate — “Fundamentally, Respondents misperceive the respective roles of the Attorney General and the Court.”

iFinex appealed — and yesterday, the NY AG’s office filed its response.

The filing contains nothing really new — but the useful thing it does is to state the NY AG’s problems with Tether concisely, in one place. If you were having problems getting your head around this case, this is a useful read.

 

 

The Martin Act

The Martin Act gives New York broad powers to head off possible securities or commodities fraud — “to defeat all unsubstantial and visionary schemes in relation thereto whereby the public is fraudulently exploited.”

Specifically, the Martin Act — and considerable precedent in its past enforcement — gives the Office of the Attorney General (OAG) broad powers of investigation — even when they’re not sure there’s a solid case yet.

The OAG first sent Bitfinex and Tether a Martin Act subpoena in November 2018.

Bitfinex and Tether

Bitfinex is a cryptocurrency exchange. It’s one of the few such exchanges that lets you deposit actual money. These deposits are held with various banks and “payment processors.”

Bitfinex claims to have barred customers from the state of New York since January 2017 — but they didn’t. Bitfinex claims to have barred all US customers since August 2017 — but they didn’t do this, either. US and New York customers continue to have access — and Bitfinex knows they do.

Tether is owned and run by the same people as Bitfinex. Tether offers tethers, a cryptocurrency “stablecoin” that is worth one dollar — their main use is in crypto trading. Tether has long claimed that every tether was backed by a US dollar in a bank account.

There were 2.6 billion tethers in circulation when the OAG brought its action. Another two billion have been issued since then.

Tether claims to have stopped serving US customers in November 2017 — but the OAG has found New York investors buying tethers from Bitfinex up to December 2018.

Bitfinex’s liquidity troubles

This is the good bit — starting on p13 of the filing. This section is why the OAG is deeply concerned.

Bitfinex needs banking to handle its customer deposits and withdrawals of actual money. Unfortunately, US banks don’t want to risk dealing with unregulated offshore companies that touch cryptos.

After multiple failed financial relationships, Tether announced in November 2018 a relationship with Deltec Bank and Trust in the Bahamas. Bitfinex also hooked up with Deltec.

Tether still stated that all tethers were “fully backed by US dollars that are safely deposited in our bank accounts.”

But this wasn’t the case. Tether and Bitfinex were also entrusting funds to third-party payment processors — such as more than $1 billion by mid-2018 to Crypto Capital Corp, who were supposedly an intermediary to wire funds to and from customers.

This was despite Bitfinex and Tether having no contractual relationship with Crypto Capital. If anything happened, Bitfinex and Tether customer funds were at risk. They did not disclose this to their customers.

In mid-2018, Crypto Capital stopped processing Bitfinex’s withdrawal requests — they claimed that (unspecified) governments had seized $850 million of Bitfinex funds from banks around the world.

Despite withdrawal requests piling up … Bitfinex didn’t tell anyone. In fact, they claimed that reports of problems were “based on nothing but fiction” — “All cryptocurrency and fiat withdrawals are, and have been, processing as usual without the slightest interference.”

In November 2018, Bitfinex grabbed $625 million from a ready source of cash — Tether’s account at Deltec. In return, they transferred $625 million from Bitfinex’s account at Crypto Capital to Tether’s account at Crypto Capital.

That is, Bitfinex got money they could use, in return for money they couldn’t use — and that Tether couldn’t use either.

Which shell is the reserve under?

Bitfinex produced a small quantity of documents for the OAG. In February 2019, they finally told the OAG that Crypto Capital had $850 million of Bitfinex money they weren’t giving back.

Bitfinex didn’t mention to the OAG the $625 million transaction from November. In fact, they told the OAG they were contemplating a new transaction, to give Bitfinex a $600-700 million line of credit on the Tether reserve — the money that was supposed to be the one-to-one backing for tethers.

The OAG was concerned by this — it sounded like Bitfinex needed hundreds of millions of dollars urgently, just to keep going, and Tether would lose its backing. And this transaction would never be disclosed.

The OAG asked for more information on the loss of funds to Crypto Capital, and on the line of credit transaction. Bitfinex dragged its feet on submitting more documentation.

In March 2019, Bitfinex told the OAG the line of credit had gone through — for $900 million. Bitfinex also finally told the OAG about the earlier $625 million shuffle.

Thus, Tether’s backing now included $625 million at Crypto Capital that wasn’t accessible, and a $900 million IOU from Bitfinex — $1.5 billion had been taken from the Tether reserve to pay out Bitfinex withdrawals.

This finally drove the OAG to file its action in April 2019.

The New York Supreme Court told iFinex to produce documents, and not to make “transactions outside the ordinary course of Tether’s business that would result in Bitfinex or other affiliated parties having claims on the U.S. dollar reserves being held by Tether” — that is, no more nonsense like the $900 million line of credit.

No New York

iFinex objected (so far) on three grounds — that the OAG’s subpoenas had not been properly served; that the Martin Act didn’t apply to tethers (so the OAG had no subject matter jurisdiction); and that iFinex didn’t do business in New York (so the OAG had no personal jurisdiction).

The judge asked iFinex for documents to back up the jurisdiction claim.

The OAG says that these documents established “respondents’ purposeful direction of activities toward the State at various stages of the trading process”. Among other examples:

  • Phil Potter, then an executive at Bitfinex and Tether, lives in and works from Long Island, New York. But he didn’t just happen to live in New York while working for a foreign company that didn’t do business in New York — Bitfinex was reassuring customers in January 2018 that Potter “lives in New York as you do and will be glad to help you with anything related to your trading activity.”
  • Bitfinex’s lawyer, Stuart Hoegner, told one New York customer to work via a “UK vehicle” to avoid New York regulation.
  • In 2019, iFinex loaned tethers to “a New York-based virtual currency trading firm.”
  • iFinex entities opened bank accounts in New York.

The Supreme Court found in August 2019 that all three grounds were invalid — the order and subpoenas stood, and the investigation could proceed. iFinex appealed.

iFinex responds

Just as I was finishing this post, iFinex put in its response to the OAG’s filing.

This is mostly an extended “nuh-uh” to anything the OAG said.

The best argument I can see in iFinex’s response is that the Crypto Capital issue, the $625 million shuffle and the $900 million line of credit didn’t happen in New York — so the OAG has no business even asking about any of this.

What happens next

@lex_node suggests that the personal jurisdiction issue is the only one iFinex has any hope on — “If I had to, I would guess the court sides with NYAG and allows continuing discovery process against Bitfinex/Tether. The fraud allegations are so strong that a judge should be loathe to do otherwise. But can’t exclude that court finds insufficient basis for personal jurisdiction.”

iFinex general counsel Stuart Hoegner tweeted in response that “There’s nothing particularly new” in these allegations, which they dispute. Bitfinex CTO Paolo Ardoino replied, “But but but things were too quit [sic] lately, better raise some FUD” — which I think must be short for “Facts U Dislike.”

The appeal is to be heard in early 2020.

 



Become a Patron!

Your subscriptions keep this site going. Sign up today!

Read the whole story
miohtama
75 days ago
reply
Helsinki, Finland
Share this story
Delete

Lessons from 5 years of teaching the world to code

1 Share

Congrats to Quincy and everyone who has joined his mission with freeCodeCamp on an astounding rise:

More than 40,000 freeCodeCamp graduates are now working in tech at companies including Apple, Google, Microsoft, Amazon, and Spotify.

Millions of people watch freeCodeCamp’s YouTube channel each month.

Millions of people read freecodecamp.org/news each month.

And people ask - and answer - thousands of tech-related questions each month on freecodecamp.org/forum.

freeCodeCamp.org is now one of the most-used technology sites on the entire web.

The future is bright. Click through to read what they accomplished in 2019 and how they’re up and running on a JAMstack.

Discuss on Changelog News

Read the whole story
miohtama
123 days ago
reply
Helsinki, Finland
Share this story
Delete

Our Glorious Banana Republic

1 Share

In the early 19th century, David Ricardo developed a model to explain how countries could increase their overall wealth by exporting the goods for which they have a comparative advantage, and importing the goods for which they don’t.

Honduras, for example. The Central American republic could devote their arable land to self-sufficient agriculture, as they did in the days of Mayan civilization, but instead they specialize in banana exports and import all their other food from the US. Division of labour ftw!

Well, it was a win for us, anyway, cuz the banana plantations were owned by Americans.

The American fruit companies, to their credit, reinvested their profits in local infrastructure. They built ports, highways, railroads, and funded a private army. So boundless was their civic involvement, that they eventually overthrew the Honduran government and installed their own banana business-friendly regime.

Back to the US. Somewhere along the line, the US decided that it would be Good for the Economy if we were to specialize in soybean production and let China do all the manufacturing. Labor costs are cheaper there, and they have millions of people willing to toil on an assembly line for lack of better opportunity. That’s how comparative advantage works – Americans have high opportunity costs, and by sending manufacturing jobs to China, we free up tons of labor that can be put to work on Something More Productive.

Specialization according to comparative advantage leads to an increase in overall wealth, but the wealth is not evenly distributed. Today, soybean exports make nary a dent in our $500 billion trade deficit with China. That’s okay; Chinese people make up for the deficit by buying other American things. Things like real estate, Reddit, Starwood Hotels, Smithfield Foods, AMC, 15% of Uber, Airbnb, Goldman Sachs, Tesla… I’m not even counting the investments made through shell companies.

The good news is, we don’t have to worry about foreign interests funding mercenary armies to execute regime change. Cuz that’s what lobbyists are for.

Read the whole story
miohtama
138 days ago
reply
Helsinki, Finland
Share this story
Delete

HackTeam sell 7000 BTC with 70% Discount from compromised exchange Binance

1 Share
HackTeam sell 7000 BTC with 70% Discount from compromised exchange Binance submitted by /u/simpleProcess3 to r/BitShares
[link] [comments]
Read the whole story
miohtama
272 days ago
reply
Helsinki, Finland
Share this story
Delete
Next Page of Stories