def from_entsoe(self, starting, until):
"""
Retrieve day-ahead price from entsoe using their API.
Retrieved dates includes dates from 'starting' upto- but not including- 'until'.
Needs API key.
"""
df = pd.DataFrame(columns=['Day Ahead Price'])
bidding_zones = self.country.get_bidding_zones(starting, until)
for bidding_zone, start, end in bidding_zones:
client = EntsoePandasClient(api_key=self.API_key)
try:
df_to_append = client.query_day_ahead_prices(
bidding_zone, start=start,
end=end).to_frame('Day Ahead Price')
except HTTPError as e:
if e.response.status_code == 429:
if self.verbose:
print(
'Too many requests: waiting for 360 seconds before retrying'
)
time.sleep(360)
# retry once
df_to_append = client.query_day_ahead_prices(
bidding_zone, start=start,
end=end).to_frame('Day Ahead Price')
# raise RequestFailure({'reason': 'Too many requests'})
if e.response.status_code == 401:
raise IncorrectAPIKey('The API key is incorrect')
# append to full dataframe
df = pd.concat([df, df_to_append])
ddf = daily_dataframe.from_tz_aware_df(df, self.country,
self.time_slots)
return ddf
def Day_Ahead_Prices():
from entsoe import EntsoePandasClient
import pandas as pd
import os
import pendulum
today= pendulum.today().strftime('%Y-%m-%d')
tomorrow = pendulum.tomorrow().add(days=1).strftime('%Y-%m-%d')
client = EntsoePandasClient(api_key='bd39354a-6404-40d1-b289-90a2d8135862')
start = pd.Timestamp(today, tz='Europe/Oslo')
end = pd.Timestamp(tomorrow, tz='Europe/Oslo')
country_code = 'NO-3' #Trondheim
ts = client.query_day_ahead_prices(country_code, start=start, end=end)
pris=[]
for x in range(0, 48):
pris.append(ts[x])
return pris
def get_dayahead_prices_fig(zones, start, end):
client = EntsoePandasClient(api_key=ENTSOE_API_KEY)
data = {}
for zone in zones:
data[zone]=client.query_day_ahead_prices(zone, start=start, end=end)
df = pd.DataFrame(data)
fig = px.line(df, line_shape='hv', labels={'index': 'time', 'value': 'Price [EUR / MWh]'})
return fig
def powerPriceFetch(api_key, start, end):
"""
Henter strømpris fra et gitt tidsrom fra ENTSO E sin strømprisdatabase.
I EUR/MWh
"""
client = EntsoePandasClient(api_key)
country_code = 'NO_3' # Norway, Trondelag
response = client.query_day_ahead_prices(country_code,
start=start,
end=end)
return response[0]
def ENTSOE_API():
client = EntsoePandasClient(api_key="2c958a88-3776-4f01-82cd-c957fdc4dc6a")
country_code = 'EE', 'PT', 'ES', 'FR', 'FI', 'HU', 'SI', 'LV', 'NL', 'GR', 'BE'
start = [
pd.Timestamp('2016-12-31T22:00Z'),
pd.Timestamp('2017-01-01T00:00Z'),
pd.Timestamp('2016-12-31T23:00Z'),
pd.Timestamp('2016-12-31T23:00Z'),
pd.Timestamp('2016-12-31T22:00Z'),
pd.Timestamp('2016-12-31T23:00Z'),
pd.Timestamp('2016-12-31T23:00Z'),
pd.Timestamp('2016-12-31T22:00Z'),
pd.Timestamp('2016-12-31T23:00Z'),
pd.Timestamp('2016-12-31T22:00Z'),
pd.Timestamp('2016-12-31T23:00Z')
]
end = [
pd.Timestamp('2019-12-31T21:00Z'),
pd.Timestamp('2020-01-01T00:00Z'),
pd.Timestamp('2019-12-31T22:00Z'),
pd.Timestamp('2019-12-31T22:00Z'),
pd.Timestamp('2019-12-31T21:00Z'),
pd.Timestamp('2019-12-31T23:00Z'),
pd.Timestamp('2019-12-31T23:00Z'),
pd.Timestamp('2019-12-31T21:00Z'),
pd.Timestamp('2019-12-31T23:00Z'),
pd.Timestamp('2019-12-31T21:00Z'),
pd.Timestamp('2019-12-31T22:00Z')
]
df1 = []
iteration2 = 0
ElectricityPrice = []
for iiii in range(len(country_code)):
ElectricityPrice = client.query_day_ahead_prices(
country_code[iteration2],
start=start[iteration2],
end=end[iteration2])
if iiii == 0:
df1 = pd.DataFrame(
{country_code[iteration2]: ElectricityPrice.values})
iteration2 = iteration2 + 1
print(df1)
else:
df1[country_code[iteration2]] = pd.DataFrame(
{country_code[iteration2]: ElectricityPrice.values})
iteration2 = iteration2 + 1
print(df1)
# print(len(ElectricityPrice))
return (df1)
def ENTSOE_API():
client = EntsoePandasClient(api_key="------------------") # Sign up to ENTSO-E and contact ENTSO-E help center for api key.
country_code = 'EE', 'PT', 'ES', 'FR', 'FI', 'HU', 'SI', 'LV', 'NL', 'GR', 'BE'
start = [pd.Timestamp('2016-12-31T22:00Z'), pd.Timestamp('2016-12-31T23:00Z'), pd.Timestamp('2016-12-31T23:00Z'), pd.Timestamp('2016-12-31T23:00Z'), pd.Timestamp('2016-12-31T22:00Z'), pd.Timestamp('2016-12-31T23:00Z'), pd.Timestamp('2016-12-31T23:00Z'), pd.Timestamp('2016-12-31T22:00Z'), pd.Timestamp('2016-12-31T23:00Z'), pd.Timestamp('2016-12-31T22:00Z'), pd.Timestamp('2016-12-31T23:00Z')]
end= [pd.Timestamp('2019-12-31T21:00Z'), pd.Timestamp('2019-12-31T22:00Z'), pd.Timestamp('2019-12-31T22:00Z'), pd.Timestamp('2019-12-31T22:00Z'), pd.Timestamp('2019-12-31T21:00Z'), pd.Timestamp('2019-12-31T23:00Z'), pd.Timestamp('2019-12-31T23:00Z'), pd.Timestamp('2019-12-31T21:00Z'), pd.Timestamp('2019-12-31T23:00Z'), pd.Timestamp('2019-12-31T21:00Z'), pd.Timestamp('2019-12-31T22:00Z')]
df1=[]
iteration2=0
ElectricityPrice=[]
for iiii in range(len(country_code)):
ElectricityPrice=client.query_day_ahead_prices(country_code[iteration2], start=start[iteration2], end=end[iteration2])
if iiii==0:
df1=pd.DataFrame({country_code[iteration2]:ElectricityPrice.values})
iteration2=iteration2+1
print(df1)
else:
df1[country_code[iteration2]]=pd.DataFrame({country_code[iteration2]:ElectricityPrice.values})
iteration2=iteration2+1
print(df1)
return(df1)
from entsoe import EntsoePandasClient
import pandas as pd
import mysql.connector
client = EntsoePandasClient(api_key='c4d117a2-f140-492b-bc2b-d615ec5774f4')
start = pd.Timestamp('20171201', tz='Europe/Brussels')
end = pd.Timestamp('20180101', tz='Europe/Brussels')
country_code = 'BE' # Belgium
# methods that return Pandas Series
client.query_day_ahead_prices(country_code, start=start, end=end)
client.query_load(country_code, start=start, end=end)
client.query_load_forecast(country_code, start=start, end=end)
client.query_generation_forecast(country_code, start=start, end=end)
# methods that return Pandas DataFrames
client.query_wind_and_solar_forecast(country_code,
start=start,
end=end,
psr_type=None)
client.query_generation(country_code, start=start, end=end, psr_type=None)
client.query_installed_generation_capacity(country_code,
start=start,
end=end,
psr_type=None)
client.query_crossborder_flows('DE', 'DK', start=start, end=end)
client.query_imbalance_prices(country_code,
start=start,
end=end,
psr_type=None)
tomorrow = str(tomorrow.strftime("%Y%m%d"))
df = pd.read_csv(
"https://data.norges-bank.no/api/data/EXR/M.EUR.NOK.SP?lastNObservations=1&format=csv",
sep=';')
df = df.filter(items=["BASE_CUR", "QUOTE_CUR", "TIME_PERIOD", "OBS_VALUE"])
eur = df["OBS_VALUE"]
date = df["TIME_PERIOD"]
client = EntsoePandasClient(api_key="YOUR_ENTSOE_API-CODE")
# Fordelen ved å velge timezone er at den automatisk justerer CET til sommertidsudo
start = pd.Timestamp(today, tz="Europe/Brussels")
end = pd.Timestamp(tomorrow, tz="Europe/Brussels")
country_code = "NO_3" # MidtNorge
ts = client.query_day_ahead_prices(country_code, start=start,
end=end).to_frame()
price = ts.multiply(eur)
price = round(price.div(10),
2) # Deler egentlig på 1000, da ender man opp i NOK.
# Men det var mer natulig å dele på 10 for å få det i Øre.
"""
Nettleie er 44,54 øre/kWh = 0,4454 kr/kWh.
"""
price_day = float(sum(price) / 1000) + float(0.4454)
print(round(float(price_day), 5), "er kr/kwh prisen for en dag")
price_week = float(price_day) * float(units)
print(round(price_week, 2), "kr koster det for en uke i kollektivet")
write_to_cot(key_price, price_week)
class Pricing_data:
def __init__(self):
self.__db_name = 'Pricing_data.db'
self.schema_path = "pricing_schema.sql"
self.__cursor = None
self.__connection = None
self.__client = EntsoePandasClient(
api_key='db59dcca-7ec4-4484-956e-86152f39e8be')
self.__country_code = 'BE' # Belgium
self.__timezone = 'Europe/Brussels'
self.today_start = None
self.today_end = None
self.tomorrow_start = None
self.tomorrow_end = None
self.updated = False
self.create_db()
def check_db_existence(self):
db_exists = os.path.exists(self.__db_name)
return db_exists
def create_db_schema(self):
with sqlite3.connect(self.__db_name) as conn:
with open(self.schema_path, 'rt') as file:
schema = file.read()
conn.executescript(schema)
self.update_last_update_time_today_price(True)
self.update_last_update_time_tomorrow_price(True)
def create_db(self):
if (not self.check_db_existence()):
self.create_db_schema()
self.__connection = sqlite3.connect(self.__db_name)
self.__cursor = self.__connection.cursor()
#create empty tables
#self.__cursor.execute('CREATE TABLE today(hour INTEGER PRIMARY KEY, price FLOAT)')
#self.__cursor.execute('CREATE TABLE tomorrow(hour INTEGER PRIMARY KEY, price FLOAT)')
def get_today_data(self):
#get today time
today = datetime.now().date()
self.today_start = today.strftime('%Y%m%d')
self.today_end = (today + timedelta(days=1)).strftime('%Y%m%d')
#set today time
self.today_start = pd.Timestamp(self.today_start, tz=self.__timezone)
self.today_end = pd.Timestamp(self.today_end, tz=self.__timezone)
#get today data
today_data = self.__client.query_day_ahead_prices(
self.__country_code, start=self.today_start, end=self.today_end)
return today_data
def upload_today_data(self):
today_data = self.get_today_data()
#delete the yesterday table
self.__cursor.execute('DROP TABLE today')
self.__cursor.execute(
'CREATE TABLE today(hour INTEGER PRIMARY KEY, price FLOAT)')
for i in range(0, 24):
self.__cursor.execute('INSERT INTO today VALUES(?,?)',
(i, today_data[i]))
self.__connection.commit()
self.update_last_update_time_today_price()
# No need
def check_time(self):
#check if correct date for updating tomorrow data
#should have changed the tomorrow data, first need to check datetime, if in range, continuously update til latest one,
#otherwise use the today data
update = False
now = datetime.now()
update_start = now.replace(hour=10, minute=30, second=0, microsecond=0)
#update_end = now.replace(hour=13, minute=30, second=0, microsecond=0)
if (update_start < now):
update = True
return update
def get_tomorrow_data(self):
#loop = True
#get today time
tomorrow = datetime.now().date() + timedelta(days=1)
self.tomorrow_start = tomorrow.strftime('%Y%m%d')
self.tomorrow_end = (tomorrow + timedelta(days=1)).strftime('%Y%m%d')
#set tomorrow time
self.tomorrow_start = pd.Timestamp(self.tomorrow_start,
tz=self.__timezone)
self.tomorrow_end = pd.Timestamp(self.tomorrow_end, tz=self.__timezone)
#while(self.check_time() & loop):
#get tomorrow data
# try:
# tomorrow_data = self.__client.query_day_ahead_prices(self.__country_code,start = self.tomorrow_start,end = self.tomorrow_end)
# loop = False
# self.updated = True
# except:
# loop = True
is_latest = False
try:
tomorrow_data = self.__client.query_day_ahead_prices(
self.__country_code,
start=self.tomorrow_start,
end=self.tomorrow_end)
is_latest = True
except Exception as e:
# in case the value is not yet available
tomorrow_data = self.get_today_data()
is_latest = False
return {"data": tomorrow_data, "is_latest": is_latest}
def is_data_latest_for_today_price(self):
current_date = datetime.now().date
last_update_time = self.get_last_update_time()
if (not last_update_time is None):
last_update_date = datetime.strptime(last_update_time["today"],
'%b %d, %Y %H:%M:%S').date
if (current_date == last_update_date):
return True
return False
return False
def is_data_latest_for_tomorrow_price(self):
current_date = datetime.now().date
last_update_time = self.get_last_update_time()
if (not last_update_time is None):
last_update_date = datetime.strptime(last_update_time["tomorrow"],
'%b %d, %Y %H:%M:%S').date
if (current_date == last_update_date):
return True
return False
return False
def upload_tomorrow_data(self):
#if(self.updated == False):
# tomorrow_data = self.get_today_data()
#else:
# tomorrow_data = self.get_tomorrow_data()
#delete the today table
raw_data = self.get_tomorrow_data()
self.__cursor.execute('DROP TABLE tomorrow')
self.__cursor.execute(
'CREATE TABLE tomorrow(hour INTEGER PRIMARY KEY, price FLOAT)')
for i in range(0, 24):
self.__cursor.execute('INSERT INTO tomorrow VALUES(?,?)',
(i, raw_data["data"][i]))
self.__connection.commit()
if (raw_data["is_latest"]):
self.update_last_update_time_tomorrow_price()
def get_forecast_pricing(self):
print(self.is_data_latest_for_tomorrow_price())
if (not self.is_data_latest_for_tomorrow_price()):
self.upload_tomorrow_data()
current_hour = datetime.now().hour
con = None
cur = None
data = None
SELECT_QUERY = "SELECT price FROM today WHERE hour>=" + str(
current_hour
) + " UNION ALL SELECT price from tomorrow WHERE hour<=" + str(
current_hour - 1)
try:
con = sqlite3.connect(self.__db_name)
cur = con.cursor()
result = cur.execute(SELECT_QUERY)
forecast_pricing = []
for row in result:
forecast_pricing.append(row[0])
except sqlite3.Error as e:
print("Exception Logged: Get " + str(e))
return None
finally:
if con:
con.close()
return forecast_pricing
def get_current_pricing(self):
print(self.is_data_latest_for_today_price())
if (not self.is_data_latest_for_today_price()):
self.upload_today_data()
current_hour = datetime.now().hour
con = None
cur = None
data = None
SELECT_QUERY = "SELECT price FROM today WHERE hour = " + str(
current_hour)
try:
con = sqlite3.connect(self.__db_name)
cur = con.cursor()
result = cur.execute(SELECT_QUERY)
for row in result:
return row[0]
except sqlite3.Error as e:
print("Exception Logged: Get " + str(e))
return None
finally:
if con:
con.close()
def can_update(self):
current_hour = datetime.now().hour
if (current_hour >= 10 and current_hour <= 13):
return True
return False
def get_last_update_time(self):
con = None
cur = None
data = None
SELECT_QUERY = "SELECT LastUpdateTodayPrice, LastUpdateTomorrowPrice FROM LastUpdate LIMIT 1"
try:
con = sqlite3.connect(self.__db_name)
cur = con.cursor()
result = cur.execute(SELECT_QUERY)
for row in result:
print(row[0])
return {"today": row[0], "tomorrow": row[1]}
except sqlite3.Error as e:
print("Exception Logged: Get " + str(e))
return None
finally:
if con:
con.close()
def update_last_update_time_today_price(self, initial=False):
cur = None
con = None
data = None
try:
if (initial):
yesterday = datetime.now() - timedelta(days=1)
current_time = datetime.strftime(yesterday, '%b %d, %Y %X')
else:
current_time = datetime.strftime(datetime.now(),
'%b %d, %Y %X')
con = sqlite3.connect(self.__db_name)
cur = con.cursor()
INSERT_QUERY = "INSERT INTO LastUpdate (Id,LastUpdateTodayPrice) VALUES (1,'" + current_time + "')"
UPDATE_QUERY = "UPDATE LastUpdate SET LastUpdateTodayPrice='" + current_time + "' WHERE Id=1"
if (self.get_last_update_time()["Today"] is None):
print("None")
result = cur.execute(INSERT_QUERY)
con.commit()
data = 100
else:
print("Time" + self.get_last_update_time()["Today"])
result = cur.execute(UPDATE_QUERY)
con.commit()
data = 101
except sqlite3.Error as e:
print("Exception Logged: Update" + str(e))
data = 112
finally:
if con:
con.close()
return data
def update_last_update_time_tomorrow_price(self, initial=False):
cur = None
con = None
data = None
try:
if (initial):
yesterday = datetime.now() - timedelta(days=1)
current_time = datetime.strftime(yesterday, '%b %d, %Y %X')
else:
current_time = datetime.strftime(datetime.now(),
'%b %d, %Y %X')
con = sqlite3.connect(self.__db_name)
cur = con.cursor()
INSERT_QUERY = "INSERT INTO LastUpdate (Id,LastUpdateTomorrowPrice) VALUES (1,'" + current_time + "')"
UPDATE_QUERY = "UPDATE LastUpdate SET LastUpdateTomorrowPrice='" + current_time + "' WHERE Id=1"
if (self.get_last_update_time()["Tomorrow"] is None):
print("None")
result = cur.execute(INSERT_QUERY)
con.commit()
data = 100
else:
print("Time" + self.get_last_update_time())
result = cur.execute(UPDATE_QUERY)
con.commit()
data = 101
except sqlite3.Error as e:
print("Exception Logged: Update" + str(e))
data = 112
finally:
if con:
con.close()
return data
