Reporting de ventas con R (Multidivisa)

En empresas internacionales es comun operar con diferentes divisas y consolidar los resultados en una moneda unica.

El objetivo de este proyecto es la elaboración de un reporte de ventas, automatizado, en Euros, de una empresa que se dedica al transporte de pasajaeros urbanos y que opera en diferentes ciudades del mundo: con euros, pesos mexicanos, pesos argentinos, chilenos…

Para ello dispongo de un ejemplo con 52.646 transacciones.

Para obtener los tipos de cambio tenemos que tener acceso a datos economicos y las librerias de R: quantmod y Quandl permiten hacerlo.

library(quantmod)
library(Quandl)

library(tidyverse)
library(dplyr)
library(kableExtra)
library(reshape)
library(lubridate)
library(ggplot2)
library(scales)
library(tidyquant)

Existen distintas fuentes y cada una de ella tiene sus particularidades: Oanda.com por ejemplo, proporciona datos historicos de 180 días, ¡aunque siempre se puede modificar el argumento de la función (from - to)!

https://finance.yahoo.com/?guccounter=1

https://www.alphavantage.co/

https://fred.stlouisfed.org/

https://www.oanda.com/rw-en/

Como ejemplo sencillo hacemos una llamada a la API del Banco Central Europeo (BCE), en este caso a traves del paquete de R quandl(), para extraer la cotización del dollar con respecto al euro:

# Import EURUSD data from BCE
eurusd<-Quandl(code="ECB/EURUSD")

El resultado que proporciona es el siguiente:

Volviendo al ejemplo empresarial con el que estsmos trabajando, las divisas con las que la compañia opera son las siguientes…

## [1] "EUR" "MXN" "BRL" "PEN" "ARS" "CLP" "USD"

…volviendo al dataset, las variables que necesito para medir las ventas son:

Agrupamos las ventas de la compañia por mes…

…pero esto no es lo correcto en estos momentos por que estamos agregando las ventas en diferentes divisas…

Lo importante del proyecto viene a continuación… y lo detallo paso por paso:

1. Preparamos una tabla de fechas

currDF  <- data.frame(
  date = seq.Date(from = as.Date('2017-01-03 13:20:24'), to = as.Date('2017-12-30 09:54:08'), length = 52645),
  variable = datos$currency,
  amount = datos$price_base,stringsAsFactors=FALSE)


currDF = currDF %>% mutate(aniomes = paste(year(date),month(date)))

currDF$divisaaniomes<-paste(currDF$variable,currDF$aniomes)

2. Los datos que tenemos son del 2017, marcamos los limites y el formato de los datos

#tipos de cambio historicos de 2017
startDt = as.Date("2017-01-03 13:20:24")
endDt = as.Date("2017-12-30 09:54:08")
currCombinations = paste(setdiff(unique(currDF$variable),"EUR"),"EUR=X",sep="")

3. Extraemos la cotiación de las divisas desde yahoo con la función getSymbols(). Y alimentamos la tabla.

Asegurarse de tener Internet al ejecutar este codigo.

fxData = do.call(merge.xts,lapply(currCombinations,function(x)
  getSymbols.yahoo(x, src="yahoo", from=startDt,to=endDt,auto.assign=FALSE)[,4])) #make sure you have internet
colnames(fxData) = gsub("EUR.X.Close","",colnames(fxData))
fxData$EUR = 1

4. Calculamos la media de la cotización de cada divisa durante cada mez: tipo de cambio medio de cada mes y de cada divisa. (¡ojo! si el reporting es anual, podemos modificar la formula)

fxData_DF = data.frame(date=index(fxData),coredata(fxData),stringsAsFactors=FALSE)
fxMolten = melt(fxData_DF,id="date",variable.name="currency",value.name="conversionFactor")

#creamos la columna aniomes para poder calcular el tipo de cambio medio de cada mes
fxMolten = fxMolten %>% mutate(aniomes = paste(year(date),month(date)))

fxMolten %>% group_by(aniomes, variable) %>% summarise(amountmedia = mean(value))
## # A tibble: 84 x 3
## # Groups:   aniomes [12]
##    aniomes variable amountmedia
##    <chr>   <fct>          <dbl>
##  1 2017 1  MXN          0.0439 
##  2 2017 1  BRL          0.294  
##  3 2017 1  PEN         NA      
##  4 2017 1  ARS          0.0591 
##  5 2017 1  CLP          0.00144
##  6 2017 1  USD          0.941  
##  7 2017 1  EUR          1      
##  8 2017 10 MXN          0.0452 
##  9 2017 10 BRL          0.266  
## 10 2017 10 PEN          0.266  
## # ... with 74 more rows
#eliminamos NAs
fxMolten<-fxMolten[complete.cases(fxMolten), ] #desaparecen algunas lineas
fxMolten %>% group_by(aniomes, variable) %>% summarise(amountmedia = mean(value))
## # A tibble: 79 x 3
## # Groups:   aniomes [12]
##    aniomes variable amountmedia
##    <chr>   <fct>          <dbl>
##  1 2017 1  MXN          0.0439 
##  2 2017 1  BRL          0.294  
##  3 2017 1  ARS          0.0591 
##  4 2017 1  CLP          0.00144
##  5 2017 1  USD          0.941  
##  6 2017 1  EUR          1      
##  7 2017 10 MXN          0.0452 
##  8 2017 10 BRL          0.266  
##  9 2017 10 PEN          0.266  
## 10 2017 10 ARS          0.0487 
## # ... with 69 more rows
#creamos la columna divisaaniomes para poder calcular el tipo de cambio medio de cada mes
fxMolten$divisaaniomes<-paste(fxMolten$variable,fxMolten$aniomes)
tipocamiomedio= fxMolten %>% group_by(divisaaniomes, variable) %>% summarise(amountmedia = mean(value))
#mergeamos con datoscabify
datos = datos %>% mutate(aniomes = paste(year(created_at),month(created_at)))
datos$divisaaniomes<-paste(datos$currency,datos$aniomes)
datosdivisa = merge(datos,tipocamiomedio, by= "divisaaniomes")
#hacemos la conversion de nuestras variables economicas
datosdivisa$price_base_EUR<-datosdivisa$price_base*datosdivisa$amountmedia
datosdivisa$cost_base_EUR<-datosdivisa$cost_base*datosdivisa$amountmedia

datos1 <- inner_join(datos,datosdivisa, by= "journey_id")

5. Incorporamos las columnas de coste y facturación en euros

Ahora que tenemos los datos en euros, vuelvo agrupar los resultados de las ventas por mes

#str(datosdivisa$created_at)

datos1$price_base_EUR[is.na(datos1$price_base_EUR)] <- 0

datos1$month <- month(datos1$created_at.x)


datos2 <- datos1 %>%
    # group by year and summarizing sales
    group_by(month) %>%
    summarize(sales = sum(price_base_EUR)) %>%
    ungroup()

datos2$sales <- format(round(as.numeric(datos2$sales), 1), nsmall=1, big.mark=",")

datos2%>%
    knitr::kable()%>%
    kable_styling(bootstrap_options = "striped", font_size = 14)

Estan facturando alrededor de 91 millones de euros anuales. Quiero ver las cifras por ciudades…

Parece que Madrid es una ciudad muy importante para la compañia…

Quiero ver como vende la empresa en Madrid semana a semana…