Group Abstract Group Abstract

Message Boards Message Boards

0
|
520 Views
|
11 Replies
|
5 Total Likes
View groups...
Share
Share this post:

Accessing and using TimeSeries data in Mathematica imported from Excel

Mitchell Sandlin
Posted 1 month ago

Hi Community Member,

I am trying to import a simple Excel spreadsheet containing a date stamp column and a data column of closing prices. While the import appears successful, the resulting TimeSeries object seems to list the closing prices as the keys rather than the dates, which is somewhat concerning. I have attached the notebook and Excel spreadsheet for reference.

Additionally, when I attempt to access the data within the TimeSeries object, I get mixed results. While functions like Histogram work correctly, other functions such as Mean, StandardDeviation, and FindDistribution either fail with an error message or simply redisplay the TimeSeries object.

Although I have used Mathematica for calculations for several years, I am new to working with data files. I have reviewed the documentation and examples but have not been able to resolve the issue.

Could you please help me get on the right track?

Thanks so much,
Mitch Sandlin

Attachments:
FGINX_Data20260718.xlsx
ImportFundQuestions.nb
POSTED BY: Mitchell Sandlin
11 Replies
Sort By:

Hi Piotr;

I just tried your suggestion which caused the Association to work, However, trying to calculate the Mean failed on both of the data columns (Close, Delta% - Please see attached. Just between you and me, do you think they could have made Importing an excel spreadsheet any more difficult?

Thanks so much,

Mitch Sandlin

Attachments:
ExcelAssociation...nb
POSTED BY: Mitchell Sandlin

I don't understand what are you trying to achieve. Maybe you should read the data using "Tabular" Import element and then just call TabularSummary. Please study the examples from ref/format/XLSX and ref/TabularSummary.

POSTED BY: Piotr Wendykier

Hi Piotr,

Thank you for your response. To clarify, I am trying to learn how to import financial fund data from an Excel spreadsheet into a Mathematica notebook so that I can analyze the imported data columns of the Time-Series spreadsheet using Mathematica functions such as Mean, StandardDeviation, Histogram, PDF, CDF, NormalDistribution, and FindDistribution.

Based on what I have learned so far, after importing the data using an Mathematica Import function, the imported data requires a specifically formatted Mathematica Association in order to read and reference the imported spreadsheet data. However, after importing and coding the Association function, I am currently unable to reference the columns of the imported spreadsheet to perform any additional Mathematica analytical functions. Please see attached Notebook.

I am currently using the book "Hands-On Start to Wolfram Mathematica," but it does not provide enough detail on how to code the specific Import and Association functions to successfully retrieve and access the data within a Mathematica notebook. Could you please provide the links or locations for the "ref/format/XLSX" and "ref/TabularSummary" resources you mentioned, as well as any additional resources that might help?

Best regards,

Mitchell Sandlin

Attachments:
ExcelAssociation...nb
POSTED BY: Mitchell Sandlin

Hi Piotr;

Additionally, these latest calculations also indicate that something happened to the Timestamp column.

If you review my prior attached Notebook (ExcellAssociationQuestion2) the Import function displays the 1st column of the spreadsheet as dates. However, the output from the Association function show the 1st column as some 10-digit number, which does not look correct.

Thanks Again,

Mitch Sandlin
POSTED BY: Mitchell Sandlin

Hi Piotr,

I wanted to let you know that I figured it out. Thank you very much for your time and help.

Best regards,

Mitchell Sandlin
POSTED BY: Mitchell Sandlin

Hi All,

The information you previously provided worked great. However, I am now trying to add an additional column to the Excel spreadsheet I am importing, which is causing generation errors in my Association function. I have tried several modifications to get the Association function to work with this new column, but without success.

Could you please help me understand what modifications I need to make to both the Import and Association functions to accommodate this additional column?

Additionally, I moved the Excel spreadsheet and the notebook that was performing the Import function to the same directory, hoping to eliminate the need for a full file path in the Import function. However, this did not work either. Any guidance on how to resolve this path issue would also be greatly appreciated.

Thank you again for your help.

Best,

Mitch Sandlin

Attachments:
ExcelAssociation...nb
POSTED BY: Mitchell Sandlin

Replace

dataFINSX=First@Import["C:\\Users\\masandlin\\Documents\\Financial\\FundsWork\\
FGINX_Data20260718.xlsx","TimeSeries"][[1]]

with

dataFINSX=First@Import["C:\\Users\\masandlin\\Documents\\Financial\\FundsWork\\
FGINX_Data20260718.xlsx","TimeSeries"]
POSTED BY: Piotr Wendykier

After doing Piotr's suggestion, we can create your dataset with the Timestamp column as desired (the "Close" column is already in the format we want for Dataset):

dataFINSX = 
  Import[(*path to xlsx file*), 
    "TimeSeries"][[1]];
dataset = 
  Association["TimesStamp" -> #[[1]], #[[2]]] & /@ 
    dataFINSX["DatePath"] // Dataset;

And now we can do some calculations with the closing values:

dataset[Mean, "Close"]
(*15.9247*)
dataset[EstimatedDistribution[#, 
   NormalDistribution[mu, sigma]] &, "Close"]
(*NormalDistribution[15.9247, 1.65351]*)
POSTED BY: David Trimas

Hi Piotr;

I tried your suggestion; however, it didn't seem to change anything throughout the notebook - Please see attached.

Thanks so much, I certainly appreciate the help.

Mitch Sandlin

Attachments:
ImportFundQuestions.nb
POSTED BY: Mitchell Sandlin

Mitch,

In the notebook you should replace

dataFINSX = Import["C:\\Users\\masandlin\\Downloads\\FGINX_Data20260718.xlsx", "TimeSeries"]

with

dataFINSX = First@Import["C:\\Users\\masandlin\\Downloads\\FGINX_Data20260718.xlsx", "TimeSeries"]
POSTED BY: Piotr Wendykier

Everything will work when you call First on the result of Import before passing it to other functions. XLSX Import returns the list of objects.

POSTED BY: Piotr Wendykier
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Tag limit exceeded
Note: Only the first five people you tag will receive an email notification; the other tagged names will appear as links to their profiles.
Reply Preview
Attachments
Remove
or Discard