def setUp(self):
dates = DatetimeIndex(date_range(start='2014-01-01', freq='d', periods=10))
returns = np.arange(0, 1, 0.1)
self.test_series = QFSeries(index=dates, data=returns)
reversed_returns = returns[::-1]
test_series_reversed = QFSeries(index=dates, data=reversed_returns)
self.test_data_frame = concat([self.test_series, test_series_reversed], axis=1, join='inner')
self.xl_importer = ExcelImporter()
def _load_results_data(cls):
excel_importer = ExcelImporter() # type: ExcelImporter
input_data_path = join(dirname(__file__),
'test_portfolio_base_results.xlsx')
cls.rets_for_fully_invested_const_weights = excel_importer.import_container(
input_data_path,
'D5',
'D508',
SimpleReturnsSeries,
sheet_name='returns',
include_index=False)
cls.rets_for_not_fully_invested_const_weights = excel_importer.import_container(
input_data_path,
'J5',
'J508',
SimpleReturnsSeries,
sheet_name='returns',
include_index=False)
cls.rets_for_fully_invested_drift_weights = excel_importer.import_container(
input_data_path,
'E5',
'E508',
SimpleReturnsSeries,
sheet_name='returns',
include_index=False)
cls.rets_for_not_fully_invested_drift_weights = excel_importer.import_container(
input_data_path,
'K5',
'K508',
SimpleReturnsSeries,
sheet_name='returns',
include_index=False)
cls.alloc_for_not_fully_invested_drift_weights = excel_importer.import_container(
input_data_path,
'C4',
'V507',
QFDataFrame,
sheet_name='drifting weights - alloc 75%',
include_index=False)
cls.alloc_for_fully_invested_drift_weights = excel_importer.import_container(
input_data_path,
'C4',
'V507',
QFDataFrame,
sheet_name='drifting weights - alloc 100%',
include_index=False)
# set proper index for all of the loaded results
for returns_tms in [
cls.rets_for_fully_invested_const_weights,
cls.rets_for_not_fully_invested_const_weights,
cls.rets_for_fully_invested_drift_weights,
cls.rets_for_not_fully_invested_drift_weights,
cls.alloc_for_not_fully_invested_drift_weights,
cls.alloc_for_fully_invested_drift_weights
]:
returns_tms.index = assets_df.index.copy()
def import_data(frequency: Frequency, file_path: str):
xlsx = ExcelImporter()
df = xlsx.import_container(file_path,
'A1',
'J1888',
sheet_name="Data",
include_index=True,
include_column_names=True)
weights = xlsx.import_container(file_path,
'M15',
'N23',
sheet_name="Data",
include_index=True,
include_column_names=False)
simple_ret_df = DataFrame()
for column in df:
prices = PricesSeries(df[column])
simple_returns = get_aggregate_returns(prices, frequency)
simple_ret_df[column] = simple_returns
return simple_ret_df, weights
def _get_assets_df():
input_data_path = join(dirname(__file__), 'test_data.xlsx')
excel_importer = ExcelImporter()
return excel_importer.import_container(input_data_path, 'A2', 'U505',
SimpleReturnsDataFrame)
class TestExcelImport(TestCaseWithFileOutput):
_tmp_dir = join(dirname(__file__), 'tmp')
_templates_dir = join(dirname(__file__), 'dummies')
def setUp(self):
dates = DatetimeIndex(start='2014-01-01', freq='d', periods=10)
returns = np.arange(0, 1, 0.1)
self.test_series = QFSeries(index=dates, data=returns)
reversed_returns = returns[::-1]
test_series_reversed = QFSeries(index=dates, data=reversed_returns)
self.test_data_frame = concat([self.test_series, test_series_reversed],
axis=1,
join='inner')
self.xl_importer = ExcelImporter()
def tearDown(self):
self.clear_tmp_dir()
def tmp_dir(self):
return self._tmp_dir
def templates_dir(self):
return self._templates_dir
def test_import_series(self):
template_file_path = self.template_file_path(SINGLE_SHEET_ONE_SERIES)
imported_series = self.xl_importer.import_container(
file_path=template_file_path,
container_type=QFSeries,
starting_cell='A1',
ending_cell='B10')
assert_series_equal(self.test_series, imported_series)
def test_import_dataframe(self):
template_file_path = self.template_file_path(
SINGLE_SHEET_ONE_DATA_FRAME)
imported_dataframe = self.xl_importer.import_container(
file_path=template_file_path,
container_type=DataFrame,
starting_cell='A1',
ending_cell='C10')
assert_dataframes_equal(self.test_data_frame, imported_dataframe)
def test_import_custom_dataframe(self):
template_file_path = self.template_file_path(
SINGLE_SHEET_CUSTOM_INDEX_DATA_FRAME)
df = DataFrame({
"Test": [1, 2, 3, 4, 5],
"Test2": [10, 20, 30, 40, 50]
}, ["A", "B", "C", "D", "E"])
imported_dataframe = self.xl_importer.import_container(
file_path=template_file_path,
container_type=DataFrame,
starting_cell='A10',
ending_cell='C15',
include_index=True,
include_column_names=True)
assert_dataframes_equal(df, imported_dataframe)
def test_import_custom_dataframe_shifted(self):
# This tests issue #79.
template_file_path = self.template_file_path(
SINGLE_SHEET_CUSTOM_INDEX_DATA_FRAME_SHIFTED)
# With index and column names.
df = DataFrame({
"Test": [1, 2, 3, 4, 5],
"Test2": [10, 20, 30, 40, 50]
}, ["A", "B", "C", "D", "E"])
imported_dataframe = self.xl_importer.import_container(
file_path=template_file_path,
container_type=DataFrame,
starting_cell='C10',
ending_cell='E15',
include_index=True,
include_column_names=True)
assert_dataframes_equal(df, imported_dataframe)
# With index and no column names.
df = DataFrame({
0: [1, 2, 3, 4, 5],
1: [10, 20, 30, 40, 50]
}, ["A", "B", "C", "D", "E"])
imported_dataframe = self.xl_importer.import_container(
file_path=template_file_path,
container_type=DataFrame,
starting_cell='C11',
ending_cell='E15',
include_index=True,
include_column_names=False)
assert_dataframes_equal(df, imported_dataframe)
# With column names and no index.
df = DataFrame({
"Test": [1, 2, 3, 4, 5],
"Test2": [10, 20, 30, 40, 50]
})
imported_dataframe = self.xl_importer.import_container(
file_path=template_file_path,
container_type=DataFrame,
starting_cell='D10',
ending_cell='E15',
include_index=False,
include_column_names=True)
assert_dataframes_equal(df, imported_dataframe)
# With no column names and no index.
df = DataFrame({0: [1, 2, 3, 4, 5], 1: [10, 20, 30, 40, 50]})
imported_dataframe = self.xl_importer.import_container(
file_path=template_file_path,
container_type=DataFrame,
starting_cell='D11',
ending_cell='E15',
include_index=False,
include_column_names=False)
assert_dataframes_equal(df, imported_dataframe)