Informes con Excel

Cuando se desarrolla un sistema, una parte fundamental es la de evaluar los resultados del mismo. Personalmente me gusta aplicar el sistema sobre un histórico muy amplio para ver cómo se desenvuelve en muy distintos escenarios.

Ejecuto el sistema con STMT, usando todos los procesadores del equipo y con la prioridad al máximo. El resultado del sistema (los negocios) lo guardo en un archivo de texto con el formato siguiente:

Excel1En total, desde 2000 a 2013, son 14 archivos, uno por año. Utilizo archivos de texto en lugar de volcarlo todo a Excel directamente porque es más simple el uso de archivos de texto si se quiere hacer algún tratamiento con ellos como, por ejemplo, agrupar los resultados por día en lugar de por negocio.

Hoy vamos a ver una plantilla de Excel que utilizo con algunos sistemas. Es algo muy personal que puede no ser de utilidad general pero iré comentando aspectos interesantes que pueden ser útiles para los archivos Excel que uno quiera usar para sus informes.

El archivo Excel no es más que una plantilla con diversos cálculos, gráficos, resultados… ya configurados de modo que basta con copiar y pegar el archivo de resultados para ver el informe de los mismos.

Aquí una lista de contenido del informe:

  • Se puede indicar la comisión en una celda y se aplicará a todos los negocios. Si se desea, se puede añadir un extra para incluir el deslizamiento.Excel2
  • El valor por punto es configurable para obtener el resultado en moneda. Para saber si el sistema es bueno o malo puede no ser estrictamente necesario pero, por ejemplo, para los impuestos sí que es muy útil y estos se aplican a la moneda, no a los puntos.
  • Incluye un ejemplo muy simple de MM que conforme aumenta el beneficio de la cuenta se opera con más contratos y, si disminuye, con menos.
  • Para cada año:
    • En una columna se puede ver como evoluciona el DrawDown a lo largo del año. Fácilmente puede verse en un gráfico si se desea.
    • Número de días en los que se ha negociado.
    • Duración promedio y percentil 50 de cada negocio.
    • Número de negocios y número de negocios diarios.
    • Número de negocios positivos, negativos y nulos y sus respectivos porcentajes.
    • Máximo Draw Down y percentil 50.
    • Beneficio total, de los negocios positivos y de los negativos, en puntos y moneda y el porcentaje que representa cada uno.
    • El gasto total en comisiones.
    • Beneficio después de comisiones.
    • Beneficio después de impuestos teniendo en cuenta los tramos.
    • Beneficio mensual después de impuestos.
    • Un resumen del resultado aplicando el MM. Es muy simple pero vale como ejemplo.
    • Un gráfico del beneficio con y sin MM.

Excel3Excel4Lo importante cuando se trabaja con Excel es hacer las cosas bien. Así, se ahorra mucho tiempo y problemas. Recomendaciones:

  • Evitar redundancia de datos. En la hoja adjunta, el valor por punto, comisiones… está solo en una celda, en la página de datos. El resto de páginas hacen referencia a estas celdas y basta con cambiar una celda para que todas las hojas se actualicen.
  • Utilizar nombres para celdas y para rangos. En el archivo podemos encontrar fórmulas como “L2*ValorPorPunto”, “N2*M2-N2*Comision”… que son mucho más intuitivas que “L2*Datos!$B$2”.
  • Usar formato para las celdas como la columna Profit o MaxDD. Ponerle colores o una barra hace que los valores resalten y sean más fáciles de identificar. Así se ve dónde están las zonas donde flaquea el sistema y donde genera mejores resultados para estudiar cada caso.
  • Las series del gráfico utilizan rangos nombrados con fórmulas (rangos dinámicos) que permiten que el gráfico muestre los datos que hemos pegado. Si en el año 2000 hay 717 negocios el gráfico muestra 717 valores. Si hacemos un cambio en el sistema y hay más negocios no es necesario configurar de nuevo los valores del gráfico pues coge automáticamente todos los existentes. Basta con pegarlos y listo.

Las fórmulas usadas son relativamente simples pero pueden ser de ayuda si alguien no las conoce.

Con Excel se pueden hacer muchas cosas en lo referente a informes. Es muy útil para ver en un instante los resultados que más interesan.

La hoja adjunta es muy simple y muestra lo comentado anteriormente pero sirve de base para generar informes personalizados con las variables que más nos interesen (sharpe…). Lo importante es la idea: con un conjunto de datos fijo que genere nuestra herramienta (en este caso las fechas, precios… de cada negocio) podemos desarrollar muchos sistemas distintos que se beneficien de la plantilla o plantillas generadas.

Si te ha gustado la entrada, considera hacer una donación Donar. ¿Por qué donar?

Anuncios
Galería | Esta entrada fue publicada en Excel. Guarda el enlace permanente.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s