Putting CSV data into a chart & other questions

KX Solutions

Putting CSV data into a chart & other questions

Posted by biggorilla on April 15, 2022 at 12:00 am
Hello!

I would like to put my CSV data into a chart. So far I’ve put it into a table and a canvas financial chart, but don’t know how to edit anything from there. Is there a tutorial on this? For now I’m not fussy on how the data in displayed; I don’t really understand the platforms.

I am using a CSV exported from a TradingView chart, it should be timestamps, OHLC and an indicator output in the columns. I only want to use half of the data, so first I need to edit the CSV. I’d like to do that from Q – how do I access fields in CSV files with Q? What’s the best way to do this, in console/developer/dashboard nodes?

What ways can look for correlations between the data? (for example, Close & indicator output)

https://kx.com/learning/modules/introduction-dashboards/#dashboards-overview – at the end of this video, ‘cars’ comes out of nowhere – how can I make my CSV file accessible there?

How can I edit the data source once the chart or table has a data source applied on Dashboard?

So far I have used
```
("PFFFFF"; enlist ",") 0: `:spy.csv
```
as Q code in a node on Dashboard, which I’m using for the financial chart and table. It’s showing the OHLC (400 ish) and the indicator value (5-10) on the same chart, so it looks very compressed. How can I visualize and compare these two things in Dashboard or Developer?

I tried to use ‘Builder’ but couldn’t find my own files. How can I use my own CSVs with Builder?

Pretty much anything which I can use to learn these platforms using my own data would be appreciated.

Thanks!
biggorilla replied 8 months, 1 week ago 3 Members · 8 Replies
8 Replies

leahs

Member
April 16, 2022 at 12:00 am

Great questions here! Thank you for sharing with the KX Community.

In order to provide appropriate answers, is it correct to say that you are working with Dashboards Direct here?

Kind regards,

Leah
biggorilla

Member
April 16, 2022 at 12:00 am
Hi Leah S!

I’m using Dashboards. I don’t know whether that’s Direct or not.

I have been able to make some progress with:
```
myCSV:("PFFFFF";enlist csv) 0: `$":/q/spy.csv"


c:myCSV[`Line] where myCSV[`Line] <> 0

close:myCSV[`close] where myCSV[`Line] <> 0


dc:deltas c

dclose:deltas close


dc2:dc[1+til 6669]

dclose2:dclose[1+til 6669]
```
This takes the data I want; two columns and only where both contain the data I want. However, I haven’t figured out how to access these data in Dashboards.

I tried to save them to csv files using the ‘set’ function, but the files are corrupt. Using ‘Save’ gave a ‘mismatched type’ error. I was trying to do this because I know how to use them. I have been trying out this code in Developer.

I tried to put the above code into one line in an attempt to use the data in Dashboards, but couldn’t figure it out. I’m not sure if that’s the right next step.

rocuinneagain

Member

April 17, 2022 at 12:00 am

This is likely close to how qsql would be used based on your example

//Read CSV and store in memory table 
myCSV:("PFFFFF";enlist csv) 0: `$":/q/spy.csv" 
//Only retain lines where Line and close do not equal 0 
myCSV:select from myCSV where Line<>0, close<>0 
//Add deltas other extra columns 
myCSV:update dc:deltas Line, dclose:deltas close, dc2:1+til count i, dclose2:1+til count i from myCSV 
//Display the table myCSV

If you run those lines in Developer then you can use myCSVin your Dashboard widget to display it as the data is stored in an in memory table.

set stores data in kdb+ binary files (not human readable).

If you wish to save as CSV either of these should work for you:

`:myCSV.csv 0: csv 0: myCSV save `myCSV.csv

biggorilla

Member
April 17, 2022 at 12:00 am
Hi! Thanks for the reponse.

I put that into developer, how can I access it in dashboards? Trying to use myCSV gives an error like it’s unfound.

I tried to run the first line of this code in a Dashboards widget and it returned this:

Error:
```
access: /q/spy.csv
```
What’s different about Q code in a widget of Dashboards and in Developer? Do I need to setup a connection between the two before I do this? If so, how?

I can’t access any of my local files in Dashboards either. I suspect these are the same issue?

Thanks
biggorilla

Member
April 17, 2022 at 12:00 am

I can access my local files in Dashboards and put them into some charts! But I am having trouble performing any actions on them from this point on.
biggorilla

Member
April 17, 2022 at 12:00 am

I’ve found a nice way to plot the data using a scatter chart. Cool!

Here’s my current situation. I have a technical indicator which I suspect could be predictive of price. I would like to take the output of the indicator and measure the relationship it has with future price. Right now I have the immediate relationship, that is, the values of both at the same point in time. I want to be able to compare the indicator with future price, but test a large number of durations between the indicator output and the price. For example, from 1 period to 500 periods. So, i’ll use notation i’m familiar with where square brackets indicate historic periods:

the relationship (correlations or whatever) between indicator[0] and close[til 500]

I want to determine which close period in the future has the highest correlation with the indicator output from a set time, and then run this test as many times as possible over each series/set. What would be some good ways to collect this data? Is there a way to visualize it? Anything info or suggestions in this area is appreciated. Thanks!

I hope that makes sense!

In keeping with the theme of laying out all of my problems here, my current issue is installing the machine learning stuff. The statistics things in there look like they should address my above problem.

l ml/ml.q
q)'os
[8] python -c "print('.'.join([str(getattr(__import__('sys').version_info,x))for x in ['major','minor']]));"2>nul <nul

Could this be related to this error I got when installing the conda ML stuff?:

(base) C:UsersB>conda install --file requirements.txt
Collecting package metadata (current_repodata.json): done
Solving environment: failed with initial frozen solve. Retrying with flexible solve.
Collecting package metadata (repodata.json): done
Solving environment: failed with initial frozen solve. Retrying with flexible solve.

PackagesNotFoundError: The following packages are not available from current channels:

- sobol-seq

Thanks

biggorilla

Member
April 18, 2022 at 12:00 am
Hello again, I had to manually set python to install stuff using v3.8 instead of 3.9 and manually install sobol-seq because for some reason it didn’t work through anaconda. Here’s my current problem:
```
q)l ml/ml.q
'libpython
[6] C:qp.q:14:
.P.env:not H~P;
.p:(`:./p 2:(`init;3))[L;H;B]]
^
q.p))l p.q
'libpython
[11] C:qp.q:14:
.P.env:not H~P;
.p:(`:./p 2:(`init;3))[L;H;B]]
^
```

KX Community

Putting CSV data into a chart & other questions