I have the following series: Series<new { string, DateTime }, double>
. I wanna know when the drawdown starts and ends. Drawdown start date can be found by drawdown.Min().Index
. Unfortunately, I don't find a similar method like .idxmin()
/idxmax()
from pandas. How do I find min and max indices?
var dataSeries = data.Select(e => KeyValue.Create(new { e.Pair, Date = e.CloseDate }, (double)e.ProfitPercentage)).ToSeries();
var cumSum = dataSeries.CumulativeSum();
var cumMax = cumSum.CumulativeMax();
var drawdown = cumSum - cumMax;
Extensions
/// <summary>
/// Utility extension methods for Deedle series/frames.
/// </summary>
public static class DeedleUtils
{
/// <summary>
/// Calculates the cumulative sum for the given series.
/// </summary>
/// <param name="input">Series to calculate cumulative sum for.</param>
/// <returns>Cumulative sum in series form.</returns>
public static Series<T, double> CumulativeSum<T>(this Series<T, double> input)
{
if (input.IsEmpty)
{
return input;
}
var prev = 0.0;
return input.SelectValues(current =>
{
var sum = prev + current;
prev = sum;
return sum;
});
}
/// <summary>
/// Calculates the cumulative product of the series. This is equal to the python pandas method: `df.cumprod()`.
/// </summary>
/// <param name="input">Input series.</param>
/// <returns>Cumulative product.</returns>
public static Series<T, double> CumulativeProduct<T>(this Series<T, double> input)
{
if (input.IsEmpty)
{
return input;
}
var prev = 1.0;
return input.SelectValues(current =>
{
var product = prev * current;
prev = product;
return product;
});
}
/// <summary>
/// Calculates the cumulative max of the series. This is equal to the python pandas method: `df.cummax()`.
/// </summary>
/// <param name="input"></param>
/// <returns></returns>
public static Series<T, double> CumulativeMax<T>(this Series<T, double> input)
{
if (input.IsEmpty)
{
return input;
}
var prevMax = double.NegativeInfinity;
var values = new List<double>();
foreach (var point in input.Values)
{
if (point > prevMax)
{
prevMax = point;
}
values.Add(prevMax);
}
return new Series<T, double>(input.Keys, values);
}
/// <summary>
/// Calculates the percentage change from the previous value to the current.
/// </summary>
/// <param name="input">Series to calculate percentage change for.</param>
/// <returns>Percentage change in series form.</returns>
/// <remarks>Equivalent to `df.pct_change()`.</remarks>
public static Series<T, double> PercentChange<T>(this Series<T, double> input)
{
if (input.IsEmpty)
{
return input;
}
var inputShifted = input.Shift(1);
return (input - inputShifted) / inputShifted;
}
/// <summary>
/// Calculates the cumulative returns series of the given input equity curve.
/// </summary>
/// <param name="input">Equity curve series.</param>
/// <returns>Cumulative returns over time.</returns>
public static Series<T, double> CumulativeReturns<T>(this Series<T, double> input)
{
if (input.IsEmpty)
{
return input;
}
return (input.PercentChange()
.Where(kvp => !double.IsInfinity(kvp.Value)) + 1)
.CumulativeProduct() - 1;
}
/// <summary>
/// Calculates the total returns over a period of time for the given input.
/// </summary>
/// <param name="input">Equity curve series.</param>
/// <returns>Total returns over time.</returns>
public static double TotalReturns<T>(this Series<T, double> input)
{
var returns = input.CumulativeReturns();
if (returns.IsEmpty)
{
return double.NaN;
}
return returns.LastValue();
}
}
Edit
I'm trying to accomplish something like:
def calculate_max_drawdown(trades: pd.DataFrame, *, date_col: str = 'close_date',
value_col: str = 'profit_percent'
) -> Tuple[float, pd.Timestamp, pd.Timestamp]:
"""
Calculate max drawdown and the corresponding close dates
:param trades: DataFrame containing trades (requires columns close_date and profit_percent)
:param date_col: Column in DataFrame to use for dates (defaults to 'close_date')
:param value_col: Column in DataFrame to use for values (defaults to 'profit_percent')
:return: Tuple (float, highdate, lowdate) with absolute max drawdown, high and low time
:raise: ValueError if trade-dataframe was found empty.
"""
if len(trades) == 0:
raise ValueError("Trade dataframe empty.")
profit_results = trades.sort_values(date_col).reset_index(drop=True)
max_drawdown_df = pd.DataFrame()
max_drawdown_df['cumulative'] = profit_results[value_col].cumsum()
max_drawdown_df['high_value'] = max_drawdown_df['cumulative'].cummax()
max_drawdown_df['drawdown'] = max_drawdown_df['cumulative'] - max_drawdown_df['high_value']
idxmin = max_drawdown_df['drawdown'].idxmin()
if idxmin == 0:
raise ValueError("No losing trade, therefore no drawdown.")
high_date = profit_results.loc[max_drawdown_df.iloc[:idxmin]['high_value'].idxmax(), date_col]
low_date = profit_results.loc[idxmin, date_col]
return abs(min(max_drawdown_df['drawdown'])), high_date, low_date
Edit2
using Deedle;
using Microsoft.FSharp.Core;
using Newtonsoft.Json;
using Newtonsoft.Json.Converters;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
namespace Resample
{
class Program
{
public class JsonTimestampConverter : DateTimeConverterBase
{
public override bool CanConvert(Type objectType)
{
return objectType == typeof(long) || objectType == typeof(string);
}
public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
{
long milliseconds;
if (reader.TokenType == JsonToken.Integer)
{
milliseconds = (long)reader.Value!;
}
else if (reader.TokenType == JsonToken.String)
{
if (!long.TryParse((string)reader.Value!, out milliseconds))
{
throw new JsonSerializationException($"Cannot convert invalid value to {objectType}.");
}
}
else
{
throw new JsonSerializationException($"Unexpected token parsing date. Expected Integer or String, got {reader.TokenType}.");
}
return DateTimeOffset.FromUnixTimeMilliseconds(milliseconds).DateTime;
}
public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
{
DateTime utcTime;
if (value is DateTime dateTime)
{
utcTime = DateTime.SpecifyKind(dateTime, DateTimeKind.Utc);
}
else
{
throw new JsonSerializationException("Expected date object value.");
}
writer.WriteRawValue($"{((DateTimeOffset)utcTime).ToUnixTimeMilliseconds()}");
}
}
public class BResult
{
[JsonProperty("pair")]
public string Pair { get; set; }
[JsonProperty("profit_percent")]
public decimal ProfitPercentage { get; set; }
[JsonProperty("profit_abs")]
public decimal ProfitAbs { get; set; }
[JsonProperty("open_rate")]
public decimal OpenRate { get; set; }
[JsonProperty("close_rate")]
public decimal CloseRate { get; set; }
[JsonProperty("open_date")]
[JsonConverter(typeof(JsonTimestampConverter))]
public DateTime OpenDate { get; set; }
[JsonProperty("close_date")]
[JsonConverter(typeof(JsonTimestampConverter))]
public DateTime CloseDate { get; set; }
[JsonProperty("open_fee")]
public decimal OpenFee { get; set; }
[JsonProperty("close_fee")]
public decimal CloseFee { get; set; }
[JsonProperty("amount")]
public decimal Amount { get; set; }
[JsonProperty("trade_duration")]
public decimal TradeDuration { get; set; }
[JsonProperty("open_at_end")]
public bool OpenAtEnd { get; set; }
[JsonProperty("sell_reason")]
public string SellReason { get; set; }
}
static void Main(string[] args)
{
// Take JSON data from pastebin
using var webClient = new WebClient();
var json = webClient.DownloadString("https://pastebin.com/raw/0bASqR47");
// Deserialize the data
var data = JsonConvert.DeserializeObject<List<BResult>>(json);
// Summary
foreach (var result in data.GroupBy(e => e.Pair)
.Select(e => new { Pair = e.Key, Count = e.Count(), Value = e }))
{
var pairsCount = 1;
var key = result.Pair;
var trades = result.Count;
var profitSum = result.Value.Sum(e => e.ProfitPercentage);
var profitSumPercentage = profitSum * 100;
var profitTotal = profitSum / pairsCount;
var profitTotalPercentage = profitTotal * 100;
Console.WriteLine($"Cumulative Profit %: {profitSumPercentage:f2}% | Total Profit %: {profitTotalPercentage:f2}%");
}
// Create series
var series = data.Select(e => KeyValue.Create(e.CloseDate, e)).ToSeries();
// Resample data
// daily_profit = results.resample('1d', on = 'close_date')['profit_percent'].sum()
var dailyProfit = series.ResampleEquivalence(dt => new DateTime(dt.Year, dt.Month, dt.Day, 0, 0, 0, DateTimeKind.Utc), group => group.SelectValues(g => g.Profi