CODEWITHSUNDEEP
mysqlrlistwhile-loopdata-manipulation
egariM
egariM

Reputation: 185

r fetch data from mysql db loop

I successfully fetch data from my mysql db using r:

library(RMySQL)
mydb = dbConnect(MySQL(), user='user', password='pass', dbname='fib', host='myhost')
rs = dbSendQuery(mydb, 'SELECT distinct(DATE(date)) as date, open,close FROM stocksng WHERE symbol = "FIB7F";')
data <- fetch(rs, n=-1)
dbHasCompleted(rs)

so now I've an object a list:

> print (typeof(data))
[1] "list"

each elements is a tuple(?) like date(charts),open(long),close(long)

ok well now my problem: I want to get a vector of percentuale difference betwen close (x) and next day open (x+1) until the end BUT I can't access properly to the item!

Example: ((open)/close*100)-100)

I try:

for (item in data){
    print (item[2])
}

and all possible combination like:

for (item in data){
  print (item[][2])
}

but cannot access to right element :! anyone could help?

Upvotes: 1

Views: 452

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520978

You have a bigger problem than this in your MySQL query, because you did not specify an ORDER BY clause. Consider using the following query:

SELECT DISTINCT
    DATE(date) AS date,
    open,
    close
FROM stocksng
WHERE
    symbol = "FIB7F"
ORDER BY
    date

Here we order the result set by date, so that it makes sense to speak of the current and next open or close. Now with a proper query in place if you wanted to get the percentile difference between the current close and the next day open you could try:

require(dplyr)
(lead(open, 1) / close*100) - 100

Or using base R:

(open[2:(length(open)+1)] / close*100) - 100

Upvotes: 1

egariM
egariM

Reputation: 185

naif version:

for (row in 1:nrow(data)){
  date <- unname (data[row,"date"])
  open <- unname (data[row+1,"open"])
  close <- unname (data[row,"close"])
  var <- abs((close/open*100)-100)
  print (var)
}

Upvotes: 0

Related Questions